Floating-Point Columns and AUTO_INCREMENT Changes in MySQL 8

Mydbops

May 12, 2025

Mins to Read

All

AUTO_INCREMENT Changes in MySQL 8

‍

MySQL 8 has introduced several enhancements and deprecated certain features to improve integrity and performance. One of the important changes is deprecating the use of AUTO_INCREMENT with floating-point columns. And MySQL has removed floating point autoincrement completely from the version 8.4. In previous versions of MySQL (5.X), it was possible to define an AUTO_INCREMENT column with floating-point (FLOAT, DOUBLE) or fixed-point (DECIMAL) data types.

This blog will cover why AUTO_INCREMENT with floating-point columns is deprecated and the potential issues it causes, when it comes to migrating from MySQL 5.7 to MySQL 8.

‍

What’s New in AUTO_INCREMENT in MySQL 8?

Floating-Point Columns with auto_increment is no longer allowed

In Previous MySQL versions , we can able to define AUTO_INCREMENT column using floating-point data types (FLOAT, DOUBLE, DECIMAL).

Let’s try to create a table with FLOAT AUTO_INCREMENT on MySQL 5.7

‍

mysql> CREATE TABLE products (
    id FLOAT AUTO_INCREMENT PRIMARY KEY,   
    name VARCHAR(100) NOT NULL,
    price FLOAT(10, 2)             
);
Query OK, 0 rows affected (0.01 sec)
mysql> select version(); 
+---------------+
| version()     |
+---------------+
| 5.7.39-42-log |
+---------------+
1 row in set (0.00 sec)

‍

We were able to create it in 5.7, whereas when we try to create the same table in MySQL 8, it’ll throw an incorrect column specifier error.

mysql> CREATE TABLE products (
    -> id FLOAT AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(255) NOT NULL,
    -> price FLOAT(10, 2)  
    -> )
    -> ;
ERROR 1063 (42000): Incorrect column specifier for column 'id'
mysql> select version(); 
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.00 sec)

‍

AUTO_INCREMENT Now Requires Integer Data Types

MySQL is strictly allows only integer based data types such as tinyint, smallint, mediumint, int, bigint

Improved Data Integrity and Indexing

MySQL enforces integer based columns to prevent from floating point round off error and Index inefficiencies caused by non integer primary keys

‍

Why AUTO_INCREMENT with Floating-Point Deprecated?

Inaccurate Issues - Floating-points don't have exact incrementing values. Eg : if a row has a value like 1.1 , it’ll round it off to nearest value
Equality Issues - In some cases , when we try to query a record with the where condition Eg id = 2 may not be matching to the stored value like 2.001 causing the query to fail.
Skipping Numbers - Auto increment requires exact number to determine the next value. Since floating points are not stored exactly, it may lead to small rounding errors which can affect MySQL to assign the next AUTO_INCREMENT value.

‍

Issues with Floating-Point AUTO_INCREMENT

‍

mysql> show create table test \G 
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `col1` float NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> INSERT INTO test VALUES (1,0.1), (2,0.2), (3,0.3), (4,0.4), (5,0.5), (6,0.4), (7,0.3);
Query OK, 7 rows affected (0.01 sec)
mysql> select * from test ;
+------+------+
| id   | col1 |
+------+------+
|    1 |    2 |
|    2 |    4 |
|    3 |    6 |
|    4 |    8 |
|    5 |   10 |
|    6 |   12 |
|    7 |   14 |
+------+------+
7 rows in set (0.00 sec)

‍

Indexing Lookups - MySQL relies on precise values when it comes to indexing whereas Floating-point values can cause wrong alignments in B-tree indexes.
Primary key Issues - Primary keys of table should be accurate and sequential, skipping numbers or duplicates might leave data gaps and data inconsistency

‍

How to Detect Floating-Point AUTO_INCREMENT Columns

Run the below query to check whether any of your tables been using floating-point auto-increment

‍

SELECT TABLE_NAME, TABLE_SCHEMA
,COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE EXTRA LIKE '%auto_increment%' 
AND DATA_TYPE IN ('float', 'double', 'decimal');

‍

mysql> SELECT TABLE_NAME, TABLE_SCHEMA
    -> ,COLUMN_NAME, DATA_TYPE
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE EXTRA LIKE '%auto_increment%' 
    -> AND DATA_TYPE IN ('float', 'double', 'decimal');
+------------+--------------+-------------+-----------+
| TABLE_NAME | TABLE_SCHEMA | COLUMN_NAME | DATA_TYPE |
+------------+--------------+-------------+-----------+
| EMPLOYEE   | test         | id          | float     |
| employee   | test         | id          | float     |
+------------+--------------+-------------+-----------+
2 rows in set (0.03 sec)

‍

Recommended Alternative for AUTO_INCREMENT with Floating-Point

Since MySQL 8 deprecated AUTO_INCREMENT with floating-point columns , it is significant enough to use alternatives like Integer types. Integer types (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT) are precise and free from rounding off issues.

Use of INTEGER-based AUTO_INCREMENT

Instead of FLOAT or DOUBLE, use an integer-based AUTO_INCREMENT column.

‍

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,   
    name VARCHAR(100) NOT NULL,
    price DECIMEL(10, 2)             
);

‍

Benefits of INT types over FLOAT/DOUBLE

Integers are indexed properly and works efficiently as it is stored in B-tree structures in storage level
Integers grow sequentially which avoids skipping data or duplicates
INTEGER-based keys are faster than UUIDs, composite keys, or string-based keys when performing table joins
If we delete a record, MySQL does not reuse their AUTO_INCREMENT values

‍

‍

Conversion of floating-point autoincrement column to INT

Before we make any changes on the table, make sure we don’t have any Non Integer records of that particular table which may cause issues. In our case, there are no Non Integer values in the table, so we’re performing the conversion with the help of direct alter.

‍

ALTER TABLE test  MODIFY COLUMN col1 INT AUTO_INCREMENT;

‍

We can perform this activity with the help of online-schema-change tools to make sure that there are no lockings on the parallel writes to the table.

mysql> select * from test ;
+------+------+
| id   | col1 |
+------+------+
|    1 |    2 |
|    2 |    4 |
|    3 |    6 |
|    4 |    8 |
|    5 |   10 |
|    6 |   12 |
|    7 |   14 |
+------+------+
7 rows in set (0.00 sec)
mysql> show create table test \G 
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `col1` float NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> 
mysql> ALTER TABLE test  MODIFY COLUMN col1 INT AUTO_INCREMENT;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `col1` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> select * from test ;
+------+------+
| id   | col1 |
+------+------+
|    1 |    2 |
|    2 |    4 |
|    3 |    6 |
|    4 |    8 |
|    5 |   10 |
|    6 |   12 |
|    7 |   14 |
+------+------+
7 rows in set (0.00 sec)

‍

‍ In conclusion ,deprecation of floating-point columns in MySQL 8 is a necessary step to enhance data integrity and indexing performance. I would suggest always using Integer based columns for Auto-increment to maintain the consistent data.

Worried about MySQL 8 changes breaking your setup? Let Mydbops handle it — from performance tuning to secure migrations. Explore our MySQL Managed, Consulting, Audit & Remote DBA Services.

No items found.