tl;dr 靈活使用 IN() 函數(shù)漱牵，或者 INSERT ... ON DUPLICATE KEY UPDATE ... 搭配 UNIQUE KEY 來(lái)做到批量處理記錄。
數(shù)據(jù)準(zhǔn)備
建立一張測(cè)試用的表
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;
準(zhǔn)備幾條數(shù)據(jù)
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;
-- 得到結(jié)果：
+---------+--------------------------------------+------+----------+--------+
| 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 |
+---------+--------------------------------------+------+----------+--------+
批量查詢(xún)符合某幾列值的記錄
以測(cè)試表為例，想要查詢(xún) 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));
-- 得到結(jié)果：
+---------+--------------------------------------+------+----------+--------+
| 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 |
+---------+--------------------------------------+------+----------+--------+
批量將符合某幾列值的記錄的某個(gè)字段更新為某個(gè)值
以測(cè)試表為例，想要將 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));
-- 得到結(jié)果：
+---------+--------------------------------------+------+----------+--------+
| 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));
-- 得到結(jié)果：
| 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 |
+---------+--------------------------------------+------+----------+--------+
批量插入新數(shù)據(jù)的同時(shí)更新數(shù)據(jù)
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);
-- 得到結(jié)果：
+---------+--------------------------------------+------+----------+--------+
| 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 |
+---------+--------------------------------------+------+----------+--------+
批量刪除符合某幾列值的記錄
以測(cè)試表為例，想要?jiǎng)h除 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));
-- 得到結(jié)果：
+---------+--------------------------------------+------+----------+--------+
| 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 |
+---------+--------------------------------------+------+----------+--------+
相關(guān)問(wèn)題：UNIQUE KEY 的設(shè)置赞咙，(serial,type) 與 (type,serial) 的性能差別有多大责循？
理論上而言，(type, serial) 的性能要比 (serial, type) 的好攀操。
