我知道你可以一次插入多行,是否有一种方法可以一次更新多行(如在,在一个查询)在MySQL?
编辑:
例如,我有以下内容
Name id Col1 Col2
Row1 1 6 1
Row2 2 2 3
Row3 3 9 5
Row4 4 16 8
我想将以下所有更新组合成一个查询
UPDATE table SET Col1 = 1 WHERE id = 1;
UPDATE table SET Col1 = 2 WHERE id = 2;
UPDATE table SET Col2 = 3 WHERE id = 3;
UPDATE table SET Col1 = 10 WHERE id = 4;
UPDATE table SET Col2 = 12 WHERE id = 4;
我从@newtover中得到答案,并使用MySql 8中的新json_table函数扩展它。这允许你创建一个存储过程来处理工作负载,而不是在代码中构建自己的SQL文本:
drop table if exists `test`;
create table `test` (
`Id` int,
`Number` int,
PRIMARY KEY (`Id`)
);
insert into test (Id, Number) values (1, 1), (2, 2);
DROP procedure IF EXISTS `Test`;
DELIMITER $$
CREATE PROCEDURE `Test`(
p_json json
)
BEGIN
update test s
join json_table(p_json, '$[*]' columns(`id` int path '$.id', `number` int path '$.number')) v
on s.Id=v.id set s.Number=v.number;
END$$
DELIMITER ;
call `Test`('[{"id": 1, "number": 10}, {"id": 2, "number": 20}]');
select * from test;
drop table if exists `test`;
它比纯SQL慢了几毫秒,但我很高兴接受打击,而不是在代码中生成SQL文本。不确定它对巨大记录集的性能如何(JSON对象的最大大小为1Gb),但我在一次更新10k行时一直使用它。
你可以修改一个名为“多语句”的设置,它会禁用MySQL为防止(多个)注入命令而实现的“安全机制”。典型的MySQL的“辉煌”实现,它也阻止用户进行有效的查询。
这里(http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html)有一些关于设置的C实现的信息。
如果你正在使用PHP,你可以使用mysqli来做多个语句(我认为PHP已经发布了mysqli一段时间了)
$con = new mysqli('localhost','user1','password','my_database');
$query = "Update MyTable SET col1='some value' WHERE id=1 LIMIT 1;";
$query .= "UPDATE MyTable SET col1='other value' WHERE id=2 LIMIT 1;";
//etc
$con->multi_query($query);
$con->close();
希望这能有所帮助。
use
REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);
请注意:
Id必须是一个主唯一键
如果使用外键来
引用表,REPLACE删除然后插入,所以这可能
造成错误