1. 为什么批量更新?

  • 一条记录update一次，性能很差，也很容易造成阻塞。

2.方法一: 通过语句构建

2.1 批量更新单个字段

语法

UPDATE table_name 
    SET field_name = CASE where_field_name 
        WHEN 1 THEN 'value1'
        WHEN 2 THEN 'value2'
        WHEN 3 THEN 'value3'
    END
WHERE where_field_name IN (1,2,3)

说明: 根据where_field_name判断更新field_name的值，即当 where_field_name=1 更新 field_name ='value1',当 where_field_name=2 更新 field_name ='value2'....,

示例

-- 更新用户表，当id=1设置address='北京',当id=2设置address='上海',当id=3设置address='南京'
UPDATE user 
    SET address = CASE id 
        WHEN 1 THEN '北京'
        WHEN 2 THEN '上海'
        WHEN 3 THEN '南京'
    END
WHERE id IN (1,2,3)

2.2 批量更新多个字段

语法

UPDATE table_name 
    SET field_name1 = CASE where_field_name 
        WHEN 1 THEN 'value1'
        WHEN 2 THEN 'value2'
        WHEN 3 THEN 'value3'
    END,
    field_name2 = CASE where_field_name
        WHEN 1 THEN 'value-1'
        WHEN 2 THEN 'value-2'
        WHEN 3 THEN 'value-3'
    END
WHERE where_field_name IN (1,2,3)

示例

-- 更新用户表，当id=1设置address='北京'phone=17600113410....
UPDATE user 
    SET address = CASE id 
        WHEN 1 THEN '北京'
        WHEN 2 THEN '上海'
        WHEN 3 THEN '南京'
    END,
    phone = CASE id
        WHEN 1 THEN '17600113410'
        WHEN 2 THEN '17600113411'
        WHEN 3 THEN '17600113412'
    END
WHERE id IN (1,2,3)

3. 方法二: 使用 replace into

示例

REPLACE INTO user (`id`,`phone`,`address`) 
VALUES 
(1,'17600113410','北京'),
(2,'17600113411','上海'),
(3,'17600113412','南京')

使用注意 :

  • replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值。如上面的sql会把表中的其他字段设置成默认值。

  • 使用replace into 前提: 表存在主键或唯一键的时候。

4. 方法三: 使用 insert into …on duplicate key update

在INSERT INTO…..后面指定ON DUPLICATE KEY UPDATE，插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。

4.1 批量更新单个字段

示例:

-- 批量
INSERT INTO `user`(`id`, `address`) VALUES 
(1, '北京'),
(2, '南京'),
(3,'上海') 
ON DUPLICATE KEY UPDATE address=VALUES(address);

4.2 批量更新多个字段

示例:

-- 批量
INSERT INTO `user`(`id`, `address`,`phone`) VALUES 
(1, '北京','17600113410'),
(2, '南京','17600113411'),
(3,'上海','17600113412') 
ON DUPLICATE KEY UPDATE address=VALUES(address),phone=VALUES(phone);