Mysql 温故知新系列「表记录」「批量更新」

413

「这是我参与11月更文挑战的第 6 天,活动详情查看:2021最后一次更文挑战

表记录管理

测试表

create table test(
    id int PRIMARY key auto_increment,
    uname VARCHAR(20),
    sex TINYINT default 1
)

数据更新

常规操作

根据 id 为条件,精准匹配需要修改单条记录,使用 set column_name = xx 的方式,对指定的字段进行赋值操作,未指定的字段,将会维持旧的值

UPDATE test
SET uname = 'newe',
 sex = 0
WHERE
 id = 5;

image.png

扩展

我们也可以修改条件,用其他条件去匹配 1 条或多条符合要求的记录,统一按照 set 语句块中的设定对值进行覆盖操作

批量修改

这里的批量修改,与上面的 扩展 操作类型,一次更新多条记录。但有一个非常大的区别: 同样是 1 条 sql,但每条记录需要维护的字段值,却不一定相同!! 举个例子,我需要将 id=1 的记录,set name='a'id=2 的记录, set name='b'

最常见的操作,将需要维护的数据,拆为单个 update 语句进行更新。数量级较小时,可以容忍 for 循环对单个记录进行维护,但还是非常不建议这么做

推荐有 3 种批量更新的方案:

① 使用 replace into

对原纪录删除再批量插入,这样会存在一个致命的问题: 如果我们在使用这种方案更新时,遗漏了一些有字段的数据,则这些数据会丢失!!可以理解为,在执行第二步的插入操作时,这些数据是不在 sql 上!!

REPLACE INTO test_tbl (id, dr)
VALUES
    (1, '2'),
    (2, '3'),
    ...
    (x,	'y'
);

② 使用 duplicate key

duplicate key 可以在原地对原纪录进行更新,相比于 REPLACE INTO,他的缺点时性能略差,优点是:我们可以按需更新部分字段

INSERT INTO test_tbl (id, dr)
VALUES
	(1, '2'),
	(2, '3'),
	...
	(x, 'y')
) ON DUPLICATE KEY 
UPDATE dr = VALUES	(dr);

③ 使用 update categories

具体写法可见代码

update categories 
    set orderid = case id 
        when 1 then 3 
        when 2 then 4 
        when 3 then 5 
    end, 
    title = case id 
        when 1 then 'new title 1'
        when 2 then 'new title 2'
        when 3 then 'new title 3'
    end
where id in (1,2,3)

方法,可行,但实践体验不如上两种,语句显得啰嗦,原理是使用了类似 switch...case 对条件匹配的记录进行操。

了解有这么一种批量更新的方法足够了