感谢 @tracy0984 的贡献
概述
本文记录了TiDB数据库一次Insert语句执行报错的问题分析和解决方法。
测试环境
数据库版本信息： tidb 6.1.2 mysql 8.0.28 测试用表：
test.t_test
CREATE TABLE test.t_test ( xq decimal(65,30) DEFAULT NULL);
测试相关数据库参数：
mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.16 sec)
问题描述
执行SQL语句，确认执行后的结果没问题后，将Select语句改写为insert … select …语句进行插入操作时，SQL报错。但是将查询结果直接插入到表中并不会报错。
报错信息
1292 - Truncated incorrect DECIMAL value: ......
问题复现
mysql> select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
+-------------------------------------------------------------------------------+
| -269944300556.700000000000000000000000000000/0.000000001138326077000000000000 |
+-------------------------------------------------------------------------------+
| -237141453587819371373.322233045883214006350133012019 |
+-------------------------------------------------------------------------------+
1 row in set (0.12 sec)
mysql> insert into test.t_test select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
1292 - Truncated incorrect DECIMAL value: '{%!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000021) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000054) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030) %!s(bool=true) [%!s(int32=0000000000000000000000000000000
mysql> insert into test.t_test values(-237141453587819371373.322233045883214006350133012019);
Query OK, 1 row affected (0.02 sec)
mysql> show warnings;
Empty set
问题分析
对于TiDB或MySQL数据库，设置了sql_mode为严格模式（sql_mode=‘STRICT_TRANS_TABLES’）时，在SQL中存在比较的数据类型不一致且无法强制转换的情况下，select语句执行过程会产生warning信息，但是insert,update和delete语句会报错。
TiDB测试结果
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.1.2 |
+--------------------+
1 row in set (0.12 sec)
mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.16 sec)
mysql> select 1 where 'a'=2;
Empty set
mysql> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.10 sec)
mysql> insert into test.t_test select 1 where 'a'=2;
1292 - Truncated incorrect DOUBLE value: 'a'
mysql> show errors;
+-------+------+---------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: 'a' |
+-------+------+---------------------------------------+
1 row in set (0.13 sec)
mysql> delete from test.t_test where 'a'=2;
1292 - Truncated incorrect INTEGER value: 'a'
mysql> update test.t_test set xq =1 where 'a'=2;
1292 - Truncated incorrect INTEGER value: 'a'
MySQL测试结果
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.11 sec)
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)
mysql> select 1 where 'a'=2;
Empty set
mysql> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.14 sec)
mysql> insert into test.t_test select 1 where 'a'=2;
1292 - Truncated incorrect DOUBLE value: 'a'
mysql> show errors;
+-------+------+---------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: 'a' |
+-------+------+---------------------------------------+
1 row in set (0.09 sec)
mysql> delete from test.t_test where 'a'=2;
1292 - Truncated incorrect DOUBLE value: 'a'
mysql> update test.t_test set xq =1 where 'a'=2;
1292 - Truncated incorrect DOUBLE value: 'a'
解决方法
方法一，调整SQL
mysql> insert into test.t_test select 1 where 'a'='2';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> delete from test.t_test where 'a'='2';
Query OK, 0 rows affected (0.01 sec)
mysql> update test.t_test set xq =1 where 'a'='2';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0
方法二，修改sql_mode
mysql> set @@session.sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test.t_test select 1 where 'a'=2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.10 sec)
mysql> delete from test.t_test where 'a'=2;
Query OK, 0 rows affected (0.03 sec)
mysql> update test.t_test set xq =1 where 'a'=2;
Query OK, 0 rows affected (0.05 sec)
Rows matched: 0 Changed: 0 Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.16 sec)
总结
当出现报错1292时，首先详细分析一下报错SQL，是哪一部分计算出现的隐式转换报错。然后考虑进行SQL调整，消除不必要的类型转换比如（‘a’ = 2 可以改为’a’=‘2’），或者在SQL语句中使用cast()等函数显示进行强制类型转换。在计算结果精度要求不高的场合可以考虑临时在会话级别取消sql_mode的严格模式。
注意事项
注意：涉及decimal数据类型的隐式转换时，可能会发生insert…select语句在mysql中执行可以成功，但是在TiDB中执行报错的问题。
TiDB执行记录：
mysql> select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
+-------------------------------------------------------------------------------+
| -269944300556.700000000000000000000000000000/0.000000001138326077000000000000 |
+-------------------------------------------------------------------------------+
| -237141453587819371373.322233045883214006350133012019 |
+-------------------------------------------------------------------------------+
1 row in set (0.10 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133012019' |
+---------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql> insert into test.t_test select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
1292 - Truncated incorrect DECIMAL value: '{%!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000021) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000054) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030) %!s(bool=true) [%!s(int32=0000000000000000000000000000000
mysql> show errors;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '{%!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000021) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000054) %!s(int8=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030) %!s(bool=true) [%!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000237) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000141453587) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000819371373) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000322233045) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000883214006) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000350133012) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019191404) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000327285739) %!s(int32=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000585143493)]}' |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.13 sec)
mysql> insert into test.t_test values(-269944300556.700000000000000000000000000000/0.000000001138326077000000000000);
1292 - Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133012019'
mysql> show errors;
+-------+------+--------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133012019' |
+-------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)
-- 解决方法：强制类型转换或者修改SQL_mode为非严格模式
mysql> insert into test.t_test select cast(-269944300556.700000000000000000000000000000 as decimal(60,20))/cast(0.000000001138326077000000000000 as decimal(60,20));
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133' |
+---------+------+--------------------------------------------------------------------------------------+
1 row in set (0.14 sec)
mysql执行记录：
mysql> select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
+-------------------------------------------------------------------------------+
| -269944300556.700000000000000000000000000000/0.000000001138326077000000000000 |
+-------------------------------------------------------------------------------+
| -237141453587819371373.322233045883214006350133012019 |
+-------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> show warnings;
Empty set
mysql> insert into test.t_test values(-269944300556.700000000000000000000000000000/0.000000001138326077000000000000);
Query OK, 1 row affected (0.01 sec)
mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------+
| Note | 1265 | Data truncated for column 'xq' at row 1 |
+-------+------+-----------------------------------------+
1 row in set (0.04 sec)
mysql> insert into test.t_test select -269944300556.700000000000000000000000000000/0.000000001138326077000000000000;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------+
| Note | 1265 | Data truncated for column 'xq' at row 1 |
+-------+------+-----------------------------------------+
1 row in set (0.03 sec)
mysql> insert into test.t_test select cast(-269944300556.700000000000000000000000000000/0.000000001138326077000000000000 as decimal(60,30));
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0