MySQL update诡异,MySQL之UPDATE中的坑

之前在工作中碰到了update多表联合更新的问题，总结了这篇文章；这两天又碰到了update中的另一个坑，就放在一起吧。

第一部分：update与join的联合使用

1、inner join

现有两张表instr_test_left和instr_test_right，表数据分别如下：

instr_test_left

image.png

instr_test_right

image.png

UPDATE instr_test_right a,instr_test_left b

SET a.`equ_catr`='2222',b.`equ_catr`='2222'

WHERE a.`id`=b.`id`

或者

UPDATE instr_test_right a

INNER JOIN instr_test_left b

ON a.`id`=b.`id`

SET a.`equ_catr`='2222',b.`equ_catr`='2222';

结果都是

left表

image.png

right表

image.png

left表和right表能匹配上的id为001,002,003，两个表中id等于这三个值的记录被更新。

如果加上where条件，比如

left表现被修改为

image.png

现在进行如下更新：

UPDATE instr_test_right a

INNER JOIN instr_test_left b

ON a.`id`=b.`id`

SET a.`equ_catr`='9999',b.`equ_catr`='9999'

WHERE b.`equ_catr`='5555';

或者

UPDATE instr_test_right a,instr_test_left b

SET a.`equ_catr`='9999',b.`equ_catr`='9999'

WHERE a.`id`=b.`id`

AND b.`equ_catr`='5555';

结果 ：

left表

image.png

right表

image.png

对于right表来说，能够与left表匹配上id且equ_catr字段等于5555的只有id为003 的那条记录；对于left表来说，能够与right表匹配上且equ_catr字段等于5555的有三条id等于003，equ_catr等于5555的记录。

2、left join

现在right表如下

image.png

left表如下

image.png

不加where，做如下更新

UPDATE instr_test_right a

LEFT JOIN

instr_test_left b

ON a.`id`=b.`id`

SET a.`equ_catr`='7777',b.`equ_catr`='7777'

结果

left表

image.png

right表

image.png

在不加where条件的情况下，作为左连接的主表right表，无论是否能够通过id与left表关联上，该表所有记录的equ_catr字段都被更新成7777，因此在查询时，left join的主表需要显示该表所有的记录，在update时，left join的主表需要更新所有的记录，而作为被关联的表left，只有能够匹配上的记录(id等于001和002)被做了更新。

同理，right join也是类似的结果。

当加上了where条件，

UPDATE instr_test_right a

LEFT JOIN

instr_test_left b

ON a.`id`=b.`id`

SET a.`equ_catr`='7777',b.`equ_catr`='7777'

WHERE b.`equ_catr`='3333'

结果

left表

image.png

right表

image.png

当加上where条件之后，作为主表的right表，只对能够与left表匹配上且left表的equ_catr等于3333的记录做了更新，left表的结果与之前一样。

第二部分：update中set和and联合使用

之前做多字段更新的时候，查了下update的语法

UPDATE table_name

SET field1=new-value1, field2=new-value2

[WHERE Clause]

当时就按照这个格式写了，然后前两天在修改以前的SQL，看到update同时更新多个字段时，第一反应的语法是多字段中间用and连接，还以为之前的写错了，但是修改之后运行发现结果不对。

假设现在有这样一个表

image.png

现在要把sat='好评'的score修改为95，goods_id修改为'g10'，

update atest

set score=95,goods_id='g10'

where sat='好评'

结果:

image.png

显示运行成功，表中数据如下

image.png

修改成功，再尝试and的写法

update atest

set score=95

and goods_id='g10'

where sat='好评'

结果

image.png

同样运行成功，但是此时表中数据为

image.png

sat='好评'的记录中，goods_id的值未被修改，score被修改为0而不是95。可以再看两个例子，如果要修改的是user_name和score字段是什么结果

update atest

set user_name='kk' AND score=95

where sat='好评'

结果

image.png

此时报错'kk'不是数字类型

另一个例子

update atest

set user_name=234 AND goods_id='g10'

where sat='好评'

结果

image.png

产生上述结果的原因是

set和and联合使用的时候

正确的执行顺序是先and再set

update atest

set score=(95 and goods_id='g10')

where sat='好评'

95 and goods_id='g10'这条语句的执行结果是false，即0，而set goods_id='g10' 这条语句根本没有执行，所以score被修改为0，而goods_id未被修改，此外，根据上面的运行结果 set 字段=[value1 and 字段=vaule2]，如果value1的类型不是数字型会报类型错误，当然set and的语法本身就是个坑，不用纠结它的数据类型问题。

综上，在写SQL的时候，有些不熟悉的语句还是查询一下官方语法，想当然有时候会出错。