Столбец auto_increment MySQL, объявленный как float

Как хорошо сказано в документации:

B.5.4.8 Проблемы с значениями с плавающей запятой

Числа с плавающей запятой иногда вызывают путаницу, поскольку они являются приблизительными и не сохраняются в виде точных значений.

Местный тест представляет пару альтернатив, но без гарантий (особенно второй вариант):

Альтернатива 1:

mysql> DROP TABLE IF EXISTS `tbl_test`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `tbl_test` (
    ->   `id` FLOAT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE `tbl_test`G
*************************** 1. row ***************************
       Table: tbl_test
Create Table: CREATE TABLE `tbl_test` (
  `id` float unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO `tbl_test`
    ->   (`id`)
    -> VALUES
    ->   (2233937),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (16776999),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL);
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> SELECT `id`,
    ->        CONVERT(`id`, UNSIGNED)
    -> FROM `tbl_test`;
+----------+-------------------------+
| id       | CONVERT(`id`, UNSIGNED) |
+----------+-------------------------+
|  2233940 |                 2233937 |
|  2233940 |                 2233938 |
|  2233940 |                 2233939 |
|  2233940 |                 2233940 |
|  2233940 |                 2233941 |
|  2233940 |                 2233942 |
|  2233940 |                 2233943 |
|  2233940 |                 2233944 |
|  2233940 |                 2233945 |
|  2233950 |                 2233946 |
|  2233950 |                 2233947 |
|  2233950 |                 2233948 |
|  2233950 |                 2233949 |
|  2233950 |                 2233950 |
|  2233950 |                 2233951 |
|  2233950 |                 2233952 |
| 16777000 |                16776999 |
| 16777000 |                16777000 |
| 16777000 |                16777001 |
| 16777000 |                16777002 |
| 16777000 |                16777003 |
| 16777000 |                16777004 |
| 16777000 |                16777005 |
| 16777000 |                16777006 |
| 16777000 |                16777007 |
| 16777000 |                16777008 |
| 16777000 |                16777009 |
| 16777000 |                16777010 |
| 16777000 |                16777011 |
| 16777000 |                16777012 |
| 16777000 |                16777013 |
| 16777000 |                16777014 |
+----------+-------------------------+
32 rows in set (0.00 sec)

Пример db-скрипт .

Альтернатива 2: (будьте осторожны)

mysql> DROP TABLE IF EXISTS `tbl_test`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `tbl_test` (
    ->   `id` FLOAT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE `tbl_test`G
*************************** 1. row ***************************
       Table: tbl_test
Create Table: CREATE TABLE `tbl_test` (
  `id` float unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO `tbl_test`
    ->   (`id`)
    -> VALUES
    ->   (2233937),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (16776999),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL),
    ->   (NULL);
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `tbl_test` CHANGE `id`
    ->   `id` FLOAT(8, 0) UNSIGNED AUTO_INCREMENT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE `tbl_test`G
*************************** 1. row ***************************
       Table: tbl_test
Create Table: CREATE TABLE `tbl_test` (
  `id` float(8,0) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16777016 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT `id`
    -> FROM `tbl_test`;
+----------+
| id       |
+----------+
|  2233937 |
|  2233938 |
|  2233939 |
|  2233940 |
|  2233941 |
|  2233942 |
|  2233943 |
|  2233944 |
|  2233945 |
|  2233946 |
|  2233947 |
|  2233948 |
|  2233949 |
|  2233950 |
|  2233951 |
|  2233952 |
| 16776999 |
| 16777000 |
| 16777001 |
| 16777002 |
| 16777003 |
| 16777004 |
| 16777005 |
| 16777006 |
| 16777007 |
| 16777008 |
| 16777009 |
| 16777010 |
| 16777011 |
| 16777012 |
| 16777013 |
| 16777014 |
+----------+
32 rows in set (0.00 sec)

Пример db-скрипт .

Другая информация, представляющая интерес:

• 11.2.3 Типы с плавающей точкой (приблизительное значение) - FLOAT, DOUBLE .
• 11.7 Требования к хранилищу типов данных :: Требования к хранению в цифровом типе .