UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'

这应该对你有用。

MySQL手册中有关于多个表的参考。

这些都适用于InnoDB。

我觉得了解这3种不同方法的速度很重要。

有3种方法:

插入:插入时没有重复的密钥更新 TRANSACTION:对事务中的每个记录进行更新 CASE:在这种情况下，您可以在UPDATE中为每个不同的记录设置CASE /时间

我刚刚测试了一下，对我来说，INSERT方法比TRANSACTION方法快6.7倍。我同时尝试了3000行和30000行。

TRANSACTION方法仍然必须单独运行每个查询，这需要时间，尽管它在执行时将结果批处理在内存或其他地方。TRANSACTION方法在复制和查询日志中也非常昂贵。

更糟糕的是，CASE方法比有30000条记录的INSERT方法慢41.1倍(比TRANSACTION慢6.1倍)。MyISAM慢75倍。INSERT和CASE方法在大约1000条记录中保持平衡。即使是在100条记录中，CASE方法也仅仅快一点。

所以一般来说，我觉得INSERT方法是最好的，也是最容易使用的。查询更小，更容易阅读，并且只占用1个查询操作。InnoDB和MyISAM都适用。

附加材料:

解决INSERT非默认字段问题的方法是临时关闭相关的SQL模式:SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode，"STRICT_TRANS_TABLES"，"")，"STRICT_ALL_TABLES"，"")。如果计划恢复sql_mode，请确保首先保存它。

至于我看到的其他评论说auto_increment是使用INSERT方法增加的，这似乎在InnoDB中是这样的，但在MyISAM中不是。

运行测试的代码如下所示。它还输出.SQL文件以消除php解释器开销

<?php
//Variables
$NumRows=30000;

//These 2 functions need to be filled in
function InitSQL()
{

}
function RunSQLQuery($Q)
{

}

//Run the 3 tests
InitSQL();
for($i=0;$i<3;$i++)
    RunTest($i, $NumRows);

function RunTest($TestNum, $NumRows)
{
    $TheQueries=Array();
    $DoQuery=function($Query) use (&$TheQueries)
    {
        RunSQLQuery($Query);
        $TheQueries[]=$Query;
    };

    $TableName='Test';
    $DoQuery('DROP TABLE IF EXISTS '.$TableName);
    $DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB');
    $DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')');

    if($TestNum==0)
    {
        $TestName='Transaction';
        $Start=microtime(true);
        $DoQuery('START TRANSACTION');
        for($i=1;$i<=$NumRows;$i++)
            $DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i);
        $DoQuery('COMMIT');
    }
    
    if($TestNum==1)
    {
        $TestName='Insert';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' ON DUPLICATE KEY UPDATE i2=VALUES(i2)');
    }
    
    if($TestNum==2)
    {
        $TestName='Case';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');
    }
    
    print "$TestName: ".(microtime(true)-$Start)."<br>\n";

    file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
}

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

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

使用临时表

// Reorder items
function update_items_tempdb(&$items)
{
    shuffle($items);
    $table_name = uniqid('tmp_test_');
    $sql = "CREATE TEMPORARY TABLE `$table_name` ("
        ."  `id` int(10) unsigned NOT NULL AUTO_INCREMENT"
        .", `position` int(10) unsigned NOT NULL"
        .", PRIMARY KEY (`id`)"
        .") ENGINE = MEMORY";
    query($sql);
    $i = 0;
    $sql = '';
    foreach ($items as &$item)
    {
        $item->position = $i++;
        $sql .= ($sql ? ', ' : '')."({$item->id}, {$item->position})";
    }
    if ($sql)
    {
        query("INSERT INTO `$table_name` (id, position) VALUES $sql");
        $sql = "UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position"
            ." WHERE `$table_name`.id = `test`.id";
        query($sql);
    }
    query("DROP TABLE `$table_name`");
}

UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'

这应该对你有用。

MySQL手册中有关于多个表的参考。