从 MySQL 执行 update 报错 ERROR 1292 说起

JiekeXu之路

发布于 2023-09-06 09:33:24

4880

发布于 2023-09-06 09:33:24

文章被收录于专栏：JiekeXu之路

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 MySQL 执行 update 报错 ERROR 1292 ，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！

事情是这样的，上周五下班前通过自动化工具执行开发人员事先写好的 SQL 时，自动化工具执行失败了，于是手动去生产环境执行，就发生了错误 “ERROR 1292 (22007): Truncated incorrect DOUBLE value”，截断不正确的 DOUBLE 值，难道是数据类型长度不够，接下来我们查看一下表结构。

mysql> update t_busi_cont set busi_contract_file='ba42cfdb-a1d0-4e5a-c'  and busi_contract_file_ct=1 
    -> where id='7823dcaade9145cdb8702d537';
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'ba42cfdb-a1d0-4e5a-c'


mysql> select busi_contract_file,busi_contract_file_ct from t_busi_cont where id='7823dcaade9145cdb8702d537';
+------------------------------------------------+-----------------------+
| busi_contract_file                             | busi_contract_file_ct |
+------------------------------------------------+-----------------------+
| undefined,ba42cfdb-a1d0-4e5a-c                 |                     2 |
+------------------------------------------------+-----------------------+

查看这个表结构，这两个字段类型也没毛病，字符型和整型，varchar(4000) 这也没啥问题，有可能是要下班了，着急下班，一时间竟没看出来是语法问题。。。。。。

mysql> show create table t_busi_cont;
CREATE TABLE `t_busi_cont` (
  `sequence_no` int(18) NOT NULL AUTO_INCREMENT COMMENT '顺序号',
  `id` varchar(36) COLLATE utf8mb4_bin NOT NULL COMMENT '合同明细编号',
  `busi_contract_file` varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '合同文件编号，逗号分隔',
  `busi_contract_file_ct` int(10) NOT NULL DEFAULT '0' COMMENT '合同文件张数'，
  ……省去其他字段……
  PRIMARY KEY (`sequence_no`),
  UNIQUE KEY `u_t_ar_busi_contract_01` (`id`),
  );

然后使用客户端可视化工具、Xshell 命令行执行均出现一样的报错，没办法了，根据 SQL 逻辑先分开更新吧，分开按条件更新算是成功了，到这里还都没想到是语法的问题。。。。。。

mysql> update t_busi_cont set busi_contract_file='ba42cfdb-a1d0-4e5a-c' where id='7823dcaade9145cdb8702d537';
Query OK, 1 row affected (25.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> update t_busi_cont set busi_contract_file_ct=1  where id='7823dcaade9145cdb8702d537';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

验证阶段，一时间没想明白，所以找了套测试环境，模拟了一下更新逻辑，如下所示，居然更新成功了。但是从下面的更新结果来看，只是将 id=1 的值更新成了 0 并没有将 c 列更新成 6 ，不符合预期。要想更新多列的值，则不能使用 AND，可以使用逗号分隔。虽然错误是因为子句语法问题，但 MySQL 错误描述也误导我是认为值有问题，触发此错误的原因之一是在更新表的多列时使用了 AND 子句，而没有使用逗号分隔多列。

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
|  4 |    4 |    4 |
|  5 |    5 |    4 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> update t set id=6 and c=6 where d=1;    ----AND 连接
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
|  4 |    4 |    4 |
|  5 |    5 |    4 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> update t set id=6,c=6 where d=1;  ----使用逗号分隔
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  2 |    2 |    2 |
|  3 |    3 |    3 |
|  4 |    4 |    4 |
|  5 |    5 |    4 |
|  6 |    6 |    1 |
+----+------+------+
5 rows in set (0.00 sec)

如上图的测试中，“update t set id=6 and c=6 where d=1;” 居然成功了，但是有一个问题，大家不知道想了没想，值为何会被更新为 0 了呢？可以先想一想，如果暂时要是想不到，可以接着看下图。

看清楚了吗？上面图中“update t set id=‘6’ and c=‘6’ where d=1;”和“update t set id=‘6’ and c=‘1’ where d=1;” 都执行成功了，但是更新成功的值却不一样，前面为 1 后面为 0 ，现在想到了吗？0、1、0、1 这不是布尔值么，在 MySQL 中，优化器将"set id=" 和 where 之间的子句当做一个值来处理，‘6’ and c=‘6’ 的结果被认为是真，故更新为 id=1，‘6’ and c=‘1’ 的结果被认为是假，故更新成 id=0 了（但是具体为啥会是这个结果呢，还是没搞明白，如果有大神看到可以不吝赐教），但是在生产环境中则出现了我下图中错误数据类型不匹配而报错，只是这报错提示有点让人摸不着头脑。

在我的 Oracle 23c 的测试环境中，我去模拟了上面的操作，直接执行此 SQL 就报错了，ORA-00920 操作无效。

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL> CREATE TABLE JiekeXu.t1 (
  2    id int NOT NULL,
  3    c int DEFAULT NULL,
  4    d int DEFAULT NULL,
  5    PRIMARY KEY (id)
  6  );

Table created.

SQL> insert into JiekeXu.t1 values(1,1,1),(2,2,2),(3,3,3);    ----23c 新特性允许一次性插入多行值

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from JiekeXu.t1;

        ID          C          D
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3

SQL> update JiekeXu.t1 set id=6 and c=6 where d=1;
update JiekeXu.t1 set id=6 and c=6 where d=1
                           *
ERROR at line 1:
ORA-00920: invalid relational operator


SQL> update JiekeXu.t1 set id=6,c=6 where d=1;


1 row updated.

下面我也测试了下在 PostgreSQL 14 版本中也是会直接报错,而且这个报错提示非常明显 ERROR:argument of AND must be type boolean, not type integer，AND 后面必须跟布尔类型而不是整型。

$ psql
psql (14.1)
Type "help" for help.
postgres=# \c jiekexu
You are now connected to database "jiekexu" as user "postgres".
jiekexu=# \dt 
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | t             | table | postgres
 public | t1            | table | postgres
 public | t_analyzeplan | table | postgres
(3 rows)

jiekexu=# CREATE TABLE test (
jiekexu(#       id int NOT NULL,
jiekexu(#       c int DEFAULT NULL,
jiekexu(#       d int DEFAULT NULL,
jiekexu(#       PRIMARY KEY (id)
jiekexu(#     );
CREATE TABLE
jiekexu=# insert into test values(1,1,1),(2,2,2),(3,3,3);
INSERT 0 3
jiekexu=# 
jiekexu=# select * from test;
 id | c | d 
----+---+---
  1 | 1 | 1
  2 | 2 | 2
  3 | 3 | 3
(3 rows)

jiekexu=#  update test set id=6 and c=6 where d=1;
ERROR:  argument of AND must be type boolean, not type integer
LINE 1: update test set id=6 and c=6 where d=1;
                           ^
jiekexu=# show server_version;
 server_version 
----------------
 14.1
(1 row)