MySQL 批量处理

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) 的好。