[MySQL] error 1292 数据类型错误

Mysql [Err] 1292 - Truncated incorrect DOUBLE value: a

A-01. 问题:

[SQL] update `book` set `times` = 1 where `type` = 1 and `times` = 0;
[Err] 1292 - Truncated incorrect DOUBLE value: a

A-02. 分析

book表中type为vachar, times为int. 该错误为类型错误, 查询条件 `times`=0 的结果集中含有 `type`=a 的结果, 所有类型转换错误.

A-03. 解决

[SQL] update `book` set `times` = 1 where `type` = 1 and `times` = 0;

注意: 一定要严格写sql, vachar类型的一定用单引号, 否则索引都使用不上.

B-00. sql_mode 的问题, 在 WorkBench 中 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

B-01. 查看 sql_mode

[SQL] show session variables like %sql_mode%;
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

B-02. 问题

[SQL] update `user` set `name` = CONCAT("用户", `yy_id`) where `name` = `yy_id`;
[ERR] 1292 - Truncated incorrect DOUBLE value: 蔡徐坤

B-03. 解决

设置 sql_mode 为非严格模式, 问题解决

[SQL] set sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;
Query OK, 0 rows affected (0.00 sec)


[SQL] show session variables like %sql_mode%;
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)


[SQL] update `user` set `name` = CONCAT("用户", `yy_id`) where `name` = `yy_id`;
Query OK, 101622 rows affected, 14594 warnings (2.65 sec)
Rows matched: 101622  Changed: 101622  Warnings: 14594