mysql一次批量更新多条记录

Question

我有以下数据：

id  table   column add edit delete view
1   vendors city    0   0   0      1
1   vendors state   0   0   0      1
1   vendors zip     0   0   0      1

我正在尝试使用类似的查询执行批量更新：

UPDATE user_perms SET add = ?, edit = ?, delete = ?, view = ?
WHERE id = ? AND table_name = ? AND column_name = ?

在不必遍历列表并运行此查询X次的情况下，MySQL是否支持一次性批量更新？

创建表STMT:

CREATE TABLE `user_perms` (
`up_id` int unsigned NOT NULL AUTO_INCREMENT,
`id` int unsigned DEFAULT NULL,
`table_name` varchar(255) DEFAULT NULL,
`column_name` varchar(255) DEFAULT NULL,
`add` int unsigned DEFAULT NULL,
`edit` int unsigned DEFAULT NULL,
`delete` int unsigned DEFAULT NULL,
`view` int unsigned DEFAULT NULL,
PRIMARY KEY (`up_id`),
UNIQUE KEY `up_id_UNIQUE` (`up_id`),
KEY `fk_idx` (`id`),
CONSTRAINT `fk_id` FOREIGN KEY (`id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1659 DEFAULT CHARSET=utf8

Answer 1

在您的情况下，更新多行最接近的方法是使用case表达式设置值：

UPDATE user_perms 
SET add    = CASE column_name WHEN 'city' THEN ? WHEN 'state' THEN ? WHEN 'zip' THEN ? END,
edit   = CASE column_name WHEN 'city' THEN ? WHEN 'state' THEN ? WHEN 'zip' THEN ? END,
delete = CASE column_name WHEN 'city' THEN ? WHEN 'state' THEN ? WHEN 'zip' THEN ? END,
view   = CASE column_name WHEN 'city' THEN ? WHEN 'state' THEN ? WHEN 'zip' THEN ? END
WHERE id = ? AND table_name = ?;

如果要将table_name和column_name都放入CASE表达式中，则会更加复杂。

坦率地说，只写循环并一次只写一行更容易。

例如，如果您有一个由列(id, table_name, column_name)组成的唯一键，这样SQL就可以在插入重复行时进行检测，那么您可以执行以下操作：

INSERT INTO user_perms (id, table_name, column_name, add, edit, delete, view)
VALUES (1, 'vendors', 'city',  0, 0, 0, 1),
(1, 'vendors', 'state', 0, 0, 0, 1),
(1, 'vendors', 'zip',   0, 0, 0, 1) 
-- any number of additional tuples follow
ON DUPLICATE KEY UPDATE add = VALUES(add), edit = VALUES(edit), 
delete = VALUES(delete), view = VALUES(view);

如果这样做有效，就不需要先删除这些行。插入。。根据UPDATE子句，ON DUPLICATE KEY UPDATE用于在插入的数据与现有行不冲突的情况下插入新行，但如果插入的值与表上的唯一键冲突，则只更新您设置的列。有关更多详细信息，请阅读文档。