MySQL中插入0x7FFFFFFFFFFFFFFD为何仅在DOUBLE AUTO_INCREMENT列触发截断警告？

问题描述 #

场景1：带AUTO_INCREMENT的DOUBLE列 #

创建表t1，将c1设为DOUBLE NOT NULL AUTO_INCREMENT并执行插入操作后，出现了Truncated incorrect INTEGER value: '9.223372036854776e18'警告：

mysql> CREATE TABLE t1 (
  ->  c1 DOUBLE NOT NULL AUTO_INCREMENT,
  ->  c2 INT,
  ->  c3 DECIMAL(2) UNSIGNED,
  ->  c4 DECIMAL,
  ->  PRIMARY KEY (c1)
  -> );
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, 1, -500, "aaa");
Query OK, 1 row affected, 5 warnings (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+-----------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'c3' at row 1               |
| Warning | 1366 | Incorrect decimal value: 'aaa' for column 'c4' at row 1   |
| Warning | 1292 | Truncated incorrect INTEGER value: '9.223372036854776e18' |
| Warning | 1292 | Truncated incorrect INTEGER value: '9.223372036854776e18' |
| Warning | 1292 | Truncated incorrect INTEGER value: '9.223372036854776e18' |
+---------+------+-----------------------------------------------------------+
5 rows in set (0.00 sec)

场景2：普通DOUBLE列 #

将c1改为仅DOUBLE NOT NULL后，执行相同插入操作，不会出现上述整数截断警告：

mysql> CREATE TABLE t2 (
  ->  c1 DOUBLE NOT NULL,
  ->  c2 INT,
  ->  c3 DECIMAL(2) UNSIGNED,
  ->  c4 DECIMAL,
  ->  PRIMARY KEY (c1)
  -> );
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> INSERT INTO t2 VALUES (0x7FFFFFFFFFFFFFFD, 1, -500, "aaa");
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+---------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'c3' at row 1             |
| Warning | 1366 | Incorrect decimal value: 'aaa' for column 'c4' at row 1 |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)

环境信息：

mysql> SHOW VARIABLES LIKE "%SQL_MODE%";
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.01 sec) 

差异原因 #

1. AUTO_INCREMENT的本质约束 #

MySQL的AUTO_INCREMENT属性是为整数类型列设计的，虽然语法上允许给DOUBLE列添加该属性（属于历史兼容行为），但后台会强制对这类列执行整数类型的校验与转换逻辑——因为自增序列的维护依赖整数运算。

2. 场景1的警告触发逻辑 #

• 插入值0x7FFFFFFFFFFFFFFD是十六进制数，转换为十进制为9223372036854775805，这个数值远超DOUBLE类型能精确存储的最大整数（2^53 ≈ 9.007e15），存入DOUBLE后会被近似为科学计数法格式的9.223372036854776e18。
• 由于c1带有AUTO_INCREMENT属性，MySQL需要将该列值当作整数来维护自增序列，会尝试把这个近似后的DOUBLE值转换为整数类型。但9.223372036854776e18无法被精确转换为整数（超出了整数类型的精确表示范围），因此触发了Truncated incorrect INTEGER value警告；且因为自增逻辑的多次校验，出现了3次相同警告。

3. 场景2无警告的原因 #

当c1仅为DOUBLE NOT NULL时，MySQL只需要将插入值直接转换为DOUBLE类型存储，不需要执行整数转换或自增序列的相关校验，因此仅出现c3（超出无符号DECIMAL范围）、c4（字符串转DECIMAL失败）的预期警告，不会触发整数截断的警告。

内容的提问来源于stack exchange，提问作者tarang ranpara