Mysql 批量更新

Mysql批量更新数据主要可以通过以下4种方法：
以表t_order为例，建表语句如下：
CREATE TABLE t_order (
order_id bigint(20) NOT NULL,
order_no varchar(512) DEFAULT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1. 使用mysql 自带的语句批量更新
   update t_order
   set order_no=
   case order_id
   WHEN 1 THEN 'v1'
   WHEN 2 THEN 'v2'
   WHEN 3 THEN 'v3'
   end
   WHERE order_id IN (1,2,3)

2. insert into table_name ... on duplicate key update ...
   insert into t_order(order_id,order_no)
   values (1,'111'),(2,'222'),(3,'333'),(4,'444'),(5,'555'),(6,'666'),(7,'777')
   on DUPLICATE key update order_no='2222'

3. replace into table_name
   replace into t_order (order_id,order_no)
   values (1,'1111'),(2,'2222'),(3,'3333'),(4,'4444'),(5,'5555'),(6,'6666'),(7,'7777') ,(8,'8888')

4. 临时表
   create temporary table tmp(oid bigint primary key,ono varchar(50));
   insert into tmp values (1,'N1'),(2,'N2'),(3,'N3'),(4,'N4'),(5,'N5'),(6,'N6'),(7,'N7') ,(8,'N8'),(9,'N8');
   update t_order,tmp set order_id=oid,order_no=ono where order_id=oid;

备注：replace into 和 insert into on duplicate key update的不同在于：

replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值
insert into 则是只update重复记录，不会改变其它字段。