执行update语句mysql5.6报错ERROR 1292 (22007): Truncated incorrect DOUBLE value: '糖糖的坤大叔'...

最新推荐文章于 2024-02-01 15:52:51 发布

原创最新推荐文章于 2024-02-01 15:52:51 发布 · 868 阅读

CC 4.0 BY-SA版权

本文记录了一次解决MySQL更新语句时出现的ERROR 1292问题的过程，通过调整sql_mode设置从严格模式切换到非严格模式，成功执行了包含特殊字符的CONCAT操作。

执行修改语句update tbl_user_details set nickname=CONCAT("用户",yunva_id) where nickname = yunva_id;

报错：
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '糖糖的坤大叔'

查看sql模式
mysql> show session variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> update tbl_user_details set nickname=CONCAT("用户",yunva_id) where nickname = yunva_id;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '糖糖的坤大叔'

解决办法：设置sql_mode为非严格模式，问题解决

mysql> set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> 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)

mysql> update tbl_user_details set nickname=CONCAT("用户",yunva_id) where nickname = yunva_id;
Query OK, 101622 rows affected, 14594 warnings (2.65 sec)
Rows matched: 101622 Changed: 101622 Warnings: 14594