慎用mysql的批量更新语句
经常使用的是Mysql的单条更新id=id,或者id in(1,2, 3),使用mysql的连表更新使用的比较少。然而使用一次,掉一次坑,也不长记性。在此记录一下,也是自己的第一条技术文章。
背景
同一张关联表中记录的数据重复了,需要删除(软删除)掉一下重复数据,只剩下一条:
// sql code
UPDATE mark_info,
(SELECT relation_id, mark_info.id
from mark_info
WHERE mark_info.relation_type=9
AND mark_info.info_key='marked_order'
AND info_val='10'
AND is_delete=FALSE
) temp_mid
SET mark_info.is_delete=True
WHERE mark_info.relation_id=temp_mid.relation_id;
这里可以实现批量联表更新,但是会遇到几个问题,需要注意:
- 条件指定不全 ,由于是更新部分重复数据,但是where条件并没有指定出来,所以直接执行会全部更新,然后后悔莫及;
- 所以需要指定清楚条件temp_mid中使用group分组,然后使用having语句找出指定的数据,才可以;
- 为了让自己有后悔药吃,在更新的语句set中应该加入指定的更新时间,然后使操作的数据有迹可循;
Mysql批量联表更新的两种写法
// 第一种如上 update中指定两张(**多张**未使用)表,然后通过where条件指定关系 语法中有convert方法,是数据类型的原因
UPDATE user,
(SELECT
remark.relation_id as user_id,
max(remark.id) as max_remark_id
FROM remark
where length(remark.relation_id)<=12 and convert(remark.relation_id, SIGNED) in (SELECT id from user) AND remark.relation_type=6 AND remark.remark_type in (1, 2)
GROUP BY remark.relation_id) mid_table
set user.last_remark_id=convert(mid_table.max_remark_id, SIGNED)
WHERE user.id=convert(mid_table.user_id, SIGNED);
这种方法我感觉更容易理解一点儿,使用时要慎重,如上
// 第二种
UPDATE
user
set user.last_remark_id=
(SELECT max_remark_id
from (
SELECT
remark.relation_id as user_id,
max(remark.id) as max_remark_id
FROM remark
where remark.relation_id in (SELECT id from user) AND remark.relation_type=6 AND remark.remark_type in (1, 2)
GROUP BY remark.relation_id) mid_table
WHERE mid_table.user_id=user.id
);
这种方法有点不好理解,多看几遍,也能接受。
在这里记录一下。顺便学习着使用markdown语法
2019-01-04 14:46