我知道你可以一次插入多行,是否有一种方法可以一次更新多行(如在,在一个查询)在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;

当前回答

你可以给同一个表添加别名来给你想要插入的id(如果你正在逐行更新:

UPDATE table1 tab1, table1 tab2 -- alias references the same table
SET 
col1 = 1
,col2 = 2
. . . 
WHERE 
tab1.id = tab2.id;

此外,显然还可以从其他表进行更新。在本例中,更新将兼任“SELECT”语句,从指定的表中提供数据。您在查询中显式地声明了更新值,因此第二个表不受影响。

其他回答

你可以修改一个名为“多语句”的设置,它会禁用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();

希望这能有所帮助。

不知道为什么没有提到另一个有用的选项:

UPDATE my_table m
JOIN (
    SELECT 1 as id, 10 as _col1, 20 as _col2
    UNION ALL
    SELECT 2, 5, 10
    UNION ALL
    SELECT 3, 15, 30
) vals ON m.id = vals.id
SET col1 = _col1, col2 = _col2;
UPDATE tableName SET col1='000' WHERE id='3' OR id='5'

这应该能达到你想要的效果。只需要添加更多的id。我已经测试过了。

为什么没有人在一个查询中提到多个语句?

在php中,使用mysqli实例的multi_query方法。

来自php手册

MySQL允许在一个语句字符串中包含多条语句。一次发送多个语句可以减少客户端-服务器之间的往返,但需要特殊处理。

下面是更新30,000 raw中与其他3种方法的比较结果。代码可以在这里找到,这是基于@Dakusan的回答

事务:5.5194580554962 插入:0.20669293403625 例:16.474853992462 多:0.0412278175354

如您所见,多语句查询比最高答案更有效。

如果你得到这样的错误信息:

PHP Warning:  Error while sending SET_OPTION packet

你可能需要增加mysql配置文件中的max_allowed_packet,在我的机器是/etc/mysql/my.cnf,然后重新启动mysqld。

我从@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行时一直使用它。