一条 update 语句引起的事故,这回让开发长长记性!!
码农突围
共 4482字,需浏览 9分钟
·
2021-05-29 12:24
点击上方“码农突围”,马上关注 这里是码农充电第一站,回复“666”,获取一份专属大礼包 真爱,请设置“星标”或点个“在看
一、前言
二、过程
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;
三、小结
- END - 最近热文
• “我辞退了一位学位学历造假的程序员” • 再见!杭州!再见!阿里巴巴! • 面试官扎心一问: 为什么 ConcurrentHashMap 的读操作不需要加锁? • Java这个高级特性,很多人还没用过!
评论