MySQL中的多个更新怎么弄?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (10)
  • 关注 (0)
  • 查看 (111)

我知道你可以一次插入多个行,是否有一种方法可以同时更新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;
提问于
用户回答回答于

例子:

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);
用户回答回答于

你可以使用相同的表别名:

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

在这种情况下,更新加倍为“SELECT”语句。

用户回答回答于

你也可能对在更新中使用联接感兴趣,这也是可能的。

Update someTable Set someValue = 4 From someTable s Inner Join anotherTable a on s.id = a.id Where a.id = 4
-- Only updates someValue in someTable who has a foreign key on anotherTable with a value of 4.
用户回答回答于

有一个你可以修改的设置叫做'multi statement',它禁止MySQL的'safety mechanism'被实现来防止(多于一个)注入命令。 对于MySQL的“brilliant”实现来说,它也阻止用户进行有效的查询。

如果使用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();
用户回答回答于

使用临时表

// 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'

可以尝试下这个

用户回答回答于

以下全部适用于InnoDB

我觉得三种不同方法的速度是重要的

有三种方法:

  1. INSERT:使用ON DUPLICATE KEY UPDATE进行插入
  2. TRANSACTION:在每个记录中进行更新
  3. CASE:在一种情况下UPDATE其中的每个不同的记录

INSERT方法比TRANSACTION方法快了6.7倍

TRANSACTION方法仍然需要运行每个单独的查询

CASE方法是比INSERT方法慢30000条记录。即使在100条记录中,CASE方法也比较快。

所以一般来说,我觉得INSERT方法是最好的,最容易使用,查询更小,更容易阅读

INSERT非默认字段问题的解决方案是暂时关闭相关的SQL模式:

SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES","")sql_mode

运行测试的代码如下,将输出.SQL文件

<?
//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).';');
}
用户回答回答于

另一个有用的选择:

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;
用户回答回答于

我们应该意识到它的缺点和局限性:

  • 如前所述,如果你碰巧使用表中不存在主键的行启动查询,则该查询将插入新的“half-baked”记录。
  • 如果表中的NOTNULL字段没有默认值,并且不想在查询中触摸该字段,则将得到"Field 'fieldname' doesn't have a default value"MySQL警告
用户回答回答于

由于你有动态值,所以需要使用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);

扫码关注云+社区

领取腾讯云代金券