同事写了一个 update,误用一个双引号,生产数据全变 0 了!
逆锋起笔
共 3014字,需浏览 7分钟
·
2021-11-05 14:59
一、前言
二、过程
update tablename set source_name = "bj1062-北京市朝阳区常营北辰福第" where source_name = "-北京市朝阳区常营北辰福第"
bj1062
,是真的没有错误么?是的没有错误。开发执行完成后,结果的确是符合预期。Harvey,我执行了update,where条件都是对的,set的值也是对的,但是set后的字段全部都变成了0,你赶紧帮我看看,看看能不能恢复数据。
update tbl_name set str_col="xxx" = "yyy"
update tbl_name set (str_col="xxx" )= "yyy"
update tbl_name set str_col=("xxx" = "yyy")
select "xxx" = "yyy"
update tbl_name set str_col="xxx" = "yyy"
update tbl_name set str_col=0
mysql [localhost] {msandbox} (test) > select id,str_col from tbl_name where str_col="xxx" = "yyy";
+----+---------+
| id | str_col |
+----+---------+
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
| 4 | aaa |
+----+---------+
mysql [localhost] {msandbox} (test) > warnings
Show warnings enabled.
mysql [localhost] {msandbox} (test) > explain extended select id,str_col from tbl_name where str_col="xxx" = "yyy"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_name
type: index
possible_keys: NULL
key: idx_str
key_len: 33
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
Note (Code 1003): /* select#1 */ select `test`.`tbl_name`.`id` AS `id`,`test`.`tbl_name`.`str_col` AS `str_col` from `test`.`tbl_name` where ((`test`.`tbl_name`.`str_col` = 'xxx') = 'yyy')
((`test`.`tbl_name`.`str_col` = 'xxx') = 'yyy')
mysql [localhost] {msandbox} (test) > select 'yyy'+0.0;
+-----------+
| 'yyy'+0.0 |
+-----------+
| 0 |
+-----------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > select 0=0;
+-----+
| 0=0 |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
select id,str_col from tbl_name where 1=1;
三、小结
逆锋起笔
是一个专注于程序员圈子的技术平台,你可以收获最新技术动态
、最新内测资格
、BAT等大厂的经验
、精品学习资料
、职业路线
、副业思维
,微信搜索逆锋起笔
关注!
点赞是最大的支持
评论