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

发表于

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

表记录管理

测试表

1
2
3
4
5
sql复制代码create table test(
    id int PRIMARY key auto_increment,
    uname VARCHAR(20),
    sex TINYINT default 1
)

数据更新

常规操作

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

1
2
3
4
5
sql复制代码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 上！！

1
2
3
4
5
6
7
sql复制代码REPLACE INTO test_tbl (id, dr)
VALUES
    (1, '2'),
    (2, '3'),
    ...
    (x,	'y'
);

② 使用 duplicate key

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

1
2
3
4
5
6
7
8
sql复制代码INSERT INTO test_tbl (id, dr)
VALUES
	(1, '2'),
	(2, '3'),
	...
	(x, 'y')
) ON DUPLICATE KEY 
UPDATE dr = VALUES	(dr);

③ 使用 update categories

具体写法可见代码

1
2
3
4
5
6
7
8
9
10
11
12
sql复制代码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 对条件匹配的记录进行操。

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

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%