记一次 TiDB 数据库 Insert 语句执行报错的处理过程
作者：TiDB 社区干货传送门
- 2022-11-25 北京
本文字数：11471 字
阅读完需：约 38 分钟
作者： tracy0984 原文来源：https://tidb.net/blog/daba2ba6
概述
本文记录了 TiDB 数据库一次 Insert 语句执行报错的问题分析和解决方法。
测试环境
数据库版本信息：tidb 6.1.2mysql 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=0000000000000000000000000000000mysql> 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 setmysql> 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 setmysql> 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: 1mysql> 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=0000000000000000000000000000000mysql> 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 setmysql> 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: 1mysql> 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
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 4
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/a5ec800824a18ff52a5fd8be6】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论