tl;dr 灵活使用 IN() 函数，或者 INSERT ... ON DUPLICATE KEY UPDATE ... 搭配 UNIQUE KEY 来做到批量处理记录。
数据准备
建立一张测试用的表
CREATE TABLE `testing` (
CREATE TABLE `testing` (
`auto_id` int(11) NOT NULL AUTO_INCREMENT,
`serial` char(36) NOT NULL,
`type` tinyint(3) DEFAULT NULL,
`comment` text,
`status` tinyint(3) DEFAULT '1',
PRIMARY KEY (`auto_id`),
UNIQUE KEY `uniq_key` (`serial`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
准备几条数据
INSERT INTO `testing` VALUES (1,'d0fbd3af-65da-11e9-b766-00163e0bf84f',1,'test 0',1),(2,'d4e21100-65da-11e9-b766-00163e0bf84f',2,'test 002',1),(3,'d80f6f19-65da-11e9-b766-00163e0bf84f',3,'test 003',1),(4,'dafca074-65da-11e9-b766-00163e0bf84f',4,'test 004',1),(5,'de1b15a5-65da-11e9-b766-00163e0bf84f',5,'test 005',1);
select * from testing;
-- 得到结果：
+---------+--------------------------------------+------+----------+--------+
| auto_id | serial | type | comment | status |
+---------+--------------------------------------+------+----------+--------+
| 1 | d0fbd3af-65da-11e9-b766-00163e0bf84f | 1 | test 0 | 1 |
| 2 | d4e21100-65da-11e9-b766-00163e0bf84f | 2 | test 002 | 1 |
| 3 | d80f6f19-65da-11e9-b766-00163e0bf84f | 3 | test 003 | 1 |
| 4 | dafca074-65da-11e9-b766-00163e0bf84f | 4 | test 004 | 1 |
| 5 | de1b15a5-65da-11e9-b766-00163e0bf84f | 5 | test 005 | 1 |
+---------+--------------------------------------+------+----------+--------+
批量查询符合某几列值的记录
以测试表为例，想要查询 serial, type 的值是 [('d4e21100-65da-11e9-b766-00163e0bf84f',2),('d80f6f19-65da-11e9-b766-00163e0bf84f',3),('dafca074-65da-11e9-b766-00163e0bf84f',4)] 的记录。
SELECT * FROM testing WHERE (serial, type) IN (('d4e21100-65da-11e9-b766-00163e0bf84f',2),('d80f6f19-65da-11e9-b766-00163e0bf84f',3),('dafca074-65da-11e9-b766-00163e0bf84f',4));
-- 得到结果：
+---------+--------------------------------------+------+----------+--------+
| auto_id | serial | type | comment | status |
+---------+--------------------------------------+------+----------+--------+
| 2 | d4e21100-65da-11e9-b766-00163e0bf84f | 2 | test 002 | 1 |
| 3 | d80f6f19-65da-11e9-b766-00163e0bf84f | 3 | test 003 | 1 |
| 4 | dafca074-65da-11e9-b766-00163e0bf84f | 4 | test 004 | 1 |
+---------+--------------------------------------+------+----------+--------+
批量将符合某几列值的记录的某个字段更新为某个值
以测试表为例，想要将 serial, type 的值是 [('d4e21100-65da-11e9-b766-00163e0bf84f',2),('d80f6f19-65da-11e9-b766-00163e0bf84f',3),('dafca074-65da-11e9-b766-00163e0bf84f',4)] 的记录的字段 status 更新为 0
UPDATE testing SET status = 0 WHERE (serial, type) IN (('d4e21100-65da-11e9-b766-00163e0bf84f',2),('d80f6f19-65da-11e9-b766-00163e0bf84f',3),('dafca074-65da-11e9-b766-00163e0bf84f',4));
-- 得到结果：
+---------+--------------------------------------+------+----------+--------+
| auto_id | serial | type | comment | status |
+---------+--------------------------------------+------+----------+--------+
| 1 | d0fbd3af-65da-11e9-b766-00163e0bf84f | 1 | test 0 | 1 |
| 2 | d4e21100-65da-11e9-b766-00163e0bf84f | 2 | test 002 | 0 |
| 3 | d80f6f19-65da-11e9-b766-00163e0bf84f | 3 | test 003 | 0 |
| 4 | dafca074-65da-11e9-b766-00163e0bf84f | 4 | test 004 | 0 |
| 5 | de1b15a5-65da-11e9-b766-00163e0bf84f | 5 | test 005 | 1 |
+---------+--------------------------------------+------+----------+--------+
不是将 status 更新为 0，而是更新为 type 的值
UPDATE testing SET status = type WHERE (serial, type) IN (('d4e21100-65da-11e9-b766-00163e0bf84f',2),('d80f6f19-65da-11e9-b766-00163e0bf84f',3),('dafca074-65da-11e9-b766-00163e0bf84f',4));
-- 得到结果：
| auto_id | serial | type | comment | status |
+---------+--------------------------------------+------+----------+--------+
| 1 | d0fbd3af-65da-11e9-b766-00163e0bf84f | 1 | test 0 | 1 |
| 2 | d4e21100-65da-11e9-b766-00163e0bf84f | 2 | test 002 | 2 |
| 3 | d80f6f19-65da-11e9-b766-00163e0bf84f | 3 | test 003 | 3 |
| 4 | dafca074-65da-11e9-b766-00163e0bf84f | 4 | test 004 | 4 |
| 5 | de1b15a5-65da-11e9-b766-00163e0bf84f | 5 | test 005 | 1 |
+---------+--------------------------------------+------+----------+--------+
批量插入新数据的同时更新数据
INSERT INTO testing (serial, type, comment, status) VALUES ('d80f6f19-65da-11e9-b766-00163e0bf84f',3,'test 113',1),('dafca074-65da-11e9-b766-00163e0bf84f',4,'test 114',1),('de1b15a5-65da-11e9-b766-00163e0bf84f',5,'test 115',1),('d80f6f20-65da-11e9-b766-00163e0bf84f',6,'test 116',6),('dafca075-65da-11e9-b766-00163e0bf84f',7,'test 117',7),('de1b16a6-65da-11e9-b766-00163e0bf84f',7,'test 118',7) ON DUPLICATE KEY UPDATE serial=VALUES(serial), type=VALUES(type), comment=VALUES(comment), status=VALUES(status);
-- 得到结果：
+---------+--------------------------------------+------+----------+--------+
| auto_id | serial | type | comment | status |
+---------+--------------------------------------+------+----------+--------+
| 1 | d0fbd3af-65da-11e9-b766-00163e0bf84f | 1 | test 0 | 1 |
| 2 | d4e21100-65da-11e9-b766-00163e0bf84f | 2 | test 002 | 2 |
| 3 | d80f6f19-65da-11e9-b766-00163e0bf84f | 3 | test 113 | 1 |
| 4 | dafca074-65da-11e9-b766-00163e0bf84f | 4 | test 114 | 1 |
| 5 | de1b15a5-65da-11e9-b766-00163e0bf84f | 5 | test 115 | 1 |
| 6 | d80f6f20-65da-11e9-b766-00163e0bf84f | 6 | test 116 | 6 |
| 7 | dafca075-65da-11e9-b766-00163e0bf84f | 7 | test 117 | 7 |
| 8 | de1b16a6-65da-11e9-b766-00163e0bf84f | 7 | test 118 | 7 |
+---------+--------------------------------------+------+----------+--------+
批量删除符合某几列值的记录
以测试表为例，想要删除 serial, type 的值为[('d4e21100-65da-11e9-b766-00163e0bf84f',2),('d80f6f19-65da-11e9-b766-00163e0bf84f',3),('dafca074-65da-11e9-b766-00163e0bf84f',4)] 的记录
DELETE FROM testing WHERE (serial, type) IN (('d4e21100-65da-11e9-b766-00163e0bf84f',2),('d80f6f19-65da-11e9-b766-00163e0bf84f',3),('dafca074-65da-11e9-b766-00163e0bf84f',4));
-- 得到结果：
+---------+--------------------------------------+------+----------+--------+
| auto_id | serial | type | comment | status |
+---------+--------------------------------------+------+----------+--------+
| 1 | d0fbd3af-65da-11e9-b766-00163e0bf84f | 1 | test 0 | 1 |
| 5 | de1b15a5-65da-11e9-b766-00163e0bf84f | 5 | test 115 | 1 |
| 6 | d80f6f20-65da-11e9-b766-00163e0bf84f | 6 | test 116 | 6 |
| 7 | dafca075-65da-11e9-b766-00163e0bf84f | 7 | test 117 | 7 |
| 8 | de1b16a6-65da-11e9-b766-00163e0bf84f | 7 | test 118 | 7 |
+---------+--------------------------------------+------+----------+--------+
相关问题：UNIQUE KEY 的设置，(serial,type) 与 (type,serial) 的性能差别有多大？
理论上而言，(type, serial) 的性能要比 (serial, type) 的好。
