我知道你可以一次插入多行，是否有一种方法可以一次更新多行(如在，在一个查询)在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;
当前回答
为什么没有人在一个查询中提到多个语句?
在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。
其他回答
您可能还对在更新上使用连接感兴趣，这也是可能的。
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.
编辑:如果要更新的值不是来自数据库中的其他地方，则需要发出多个更新查询。
use
REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);
请注意:
Id必须是一个主唯一键 如果使用外键来 引用表，REPLACE删除然后插入，所以这可能 造成错误
使用临时表
// 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 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 `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);
所以你可以用一个查询更新孔表
