MySQL中的多次更新

mysql sql sql-update


我知道可以一次插入多行,那么在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;



Answer 1 Michiel de Mare


是的,这是可能的--你可以使用INSERT ...ON DUPLICATE KEY UPDATE。

用你的例子。

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);



Answer 2 Harrison Fisk


由于你有动态值,你需要使用IF或CASE来更新列。它变得有点丑陋,但应该能用。

用你的例子,你可以这样做。

UPDATE table SET Col1 = CASE id 
                          WHEN 1 THEN 1 
                          WHEN 2 THEN 2 
                          WHEN 4 THEN 10 
                          ELSE Col1 
                        END, 
                 Col2 = CASE id 
                          WHEN 3 THEN 3 
                          WHEN 4 THEN 12 
                          ELSE Col2 
                        END
             WHERE id IN (1, 2, 3, 4);



Answer 3 Roman Imankulov


这个问题是老问题了,然而我想用另一个答案来延伸这个话题。

我的观点是,最简单的方法是用事务包装多个查询。可接受的答案 INSERT ... ON DUPLICATE KEY UPDATE 是一个不错的技巧,但您应该意识到它的缺点和局限性:

  • 正如所说的那样,如果你恰好用表中主键不存在的行启动查询,查询就会插入新的 "半成品 "记录。可能这不是你想要的
  • 如果您的表具有不带默认值的非空字段,并且不想在查询中触摸此字段,则即使您不这样做,也会收到 "Field 'fieldname' doesn't have a default value" MySQL警告完全不插入一行。如果您决定严格执行,并将mysql警告转换为应用程序中的运行时异常,它将给您带来麻烦。

我对三个建议的变体进行了性能测试,包括 INSERT ... ON DUPLICATE KEY UPDATE 变体,带有“ case / when / then”子句的变体和带有事务处理的幼稚方法。您可以在此处获取python代码和结果。总的结论是,带有case语句的变体的速度是其他两个变体的两倍,但是很难为其编写正确且注入安全的代码,因此我个人坚持最简单的方法:使用事务。

编辑:Dakusan的发现证明了我的绩效评估不是很有效。请参阅此答案以进行其他更详细的研究。




Answer 4 newtover


不知道为什么还没有提到另一个有用的选择。

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;



Answer 5 Dakusan


以下所有内容都适用于InnoDB。

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

有3种方法。

  1. INSERT:INSERT with ON DUPLICATE KEY UPDATE(插入)。
  2. TRANSACTION:在一个事务中对每条记录进行更新。
  3. CASE:在其中你为每条不同的记录进行一个UPDATE内的case/when。

我刚刚对此进行了测试,而INSERT方法对我来说比TRANSACTION方法快6.7倍。我尝试了3,000行和30,000行的组合。

TRANSACTION方法还是要单独运行每一个查询,这需要时间,虽然它在执行的时候会在内存中批处理结果什么的。TRANSACTION方法在复制和查询日志上也是相当昂贵的。

更糟糕的是,CASE方法的速度比INSERT方法慢了41.1倍,具有30,000条记录(比TRANSACTION慢6.1倍)。而MyISAM的速度要慢75倍。INSERT和CASE方法收支平衡,达到约1,000条记录。即使有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

至于我看到的其他评论说使用INSERT方法自动增加,在InnoDB中确实是这样,但MyISAM中不是这样。

运行测试的代码如下。它还输出.SQL文件,以消除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).';');
}