我知道你可以一次插入多行，是否有一种方法可以一次更新多行(如在，在一个查询)在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;
当前回答
UPDATE `your_table` SET
`something` = IF(`id`="1","new_value1",`something`), `smth2` = IF(`id`="1", "nv1",`smth2`),
`something` = IF(`id`="2","new_value2",`something`), `smth2` = IF(`id`="2", "nv2",`smth2`),
`something` = IF(`id`="4","new_value3",`something`), `smth2` = IF(`id`="4", "nv3",`smth2`),
`something` = IF(`id`="6","new_value4",`something`), `smth2` = IF(`id`="6", "nv4",`smth2`),
`something` = IF(`id`="3","new_value5",`something`), `smth2` = IF(`id`="3", "nv5",`smth2`),
`something` = IF(`id`="5","new_value6",`something`), `smth2` = IF(`id`="5", "nv6",`smth2`)
//你只是在php中构建它
$q = 'UPDATE `your_table` SET ';
foreach($data as $dat){
$q .= '
`something` = IF(`id`="'.$dat->id.'","'.$dat->value.'",`something`),
`smth2` = IF(`id`="'.$dat->id.'", "'.$dat->value2.'",`smth2`),';
}
$q = substr($q,0,-1);
所以你可以用一个查询更新孔表
其他回答
由于您有动态值，因此需要为要更新的列使用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);
使用临时表
// 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`");
}
你可以给同一个表添加别名来给你想要插入的id(如果你正在逐行更新:
UPDATE table1 tab1, table1 tab2 -- alias references the same table
SET
col1 = 1
,col2 = 2
. . .
WHERE
tab1.id = tab2.id;
此外，显然还可以从其他表进行更新。在本例中，更新将兼任“SELECT”语句，从指定的表中提供数据。您在查询中显式地声明了更新值，因此第二个表不受影响。
这个问题很老了，但我想用另一个答案来扩展这个话题。
我的观点是，实现它的最简单的方法是用一个事务包装多个查询。这句话的意思是:ON DUPLICATE KEY UPDATE是一个很好的黑客，但人们应该意识到它的缺点和限制:
As being said, if you happen to launch the query with rows whose primary keys don't exist in the table, the query inserts new "half-baked" records. Probably it's not what you want If you have a table with a not null field without default value and don't want to touch this field in the query, you'll get "Field 'fieldname' doesn't have a default value" MySQL warning even if you don't insert a single row at all. It will get you into trouble, if you decide to be strict and turn mysql warnings into runtime exceptions in your app.
I made some performance tests for three of suggested variants, including the INSERT ... ON DUPLICATE KEY UPDATE variant, a variant with "case / when / then" clause and a naive approach with transaction. You may get the python code and results here. The overall conclusion is that the variant with case statement turns out to be twice as fast as two other variants, but it's quite hard to write correct and injection-safe code for it, so I personally stick to the simplest approach: using transactions.
编辑:Dakusan的发现证明我的性能估计不太有效。请参阅另一个更详细的研究的答案。
这些都适用于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).';');
}
