MySQL多个字段多条记录批量upsert,update

Upsert

INSERT INTO demo_table
(uid,type,thingid,lasttime)
VALUES
(1,0,3,4.5),(2,0,3,4)
ON DUPLICATE KEY UPDATE
type = CASE uid
WHEN 1 THEN 0
WHEN 2 THEN 0
END,
thingid = CASE uid
WHEN 1 THEN 4
WHEN 2 THEN 3
END,
lasttime = CASE uid
WHEN 1 THEN 4.5
WHEN 2 THEN 4
END

需要注意的是,必须设置主键,并且每个字段都得有值

demo_table表结构如上

Update

UPDATE demo_table
SET thingid=CASE uid
WHEN 1 THEN 1
WHEN 2 THEN 2
END
WHERE
uid IN (1,2)

1 条思考于 “MySQL多个字段多条记录批量upsert,update

  1. Pingback: (转)MySQL多个字段多条记录批量upsert,update – f2的博客

发表评论

电子邮件地址不会被公开。 必填项已用*标注