1
0
0
0
专栏/.../

记一次TiDB数据库Insert语句执行报错的处理过程

 tracy0984  发表于  2022-11-21
原创

概述

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

1
0
0
0

版权声明:本文为 TiDB 社区用户原创文章,遵循 CC BY-NC-SA 4.0 版权协议,转载请附上原文出处链接和本声明。

评论
暂无评论