解决 TiDB 数据库 Insert 语句执行报错的实践案例

概述

本文记录了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