Multiple Updates in MySQL
我知道，你可以同时插入多行，有没有办法一次更新多行(如，在一个查询中)在MySQL？
编辑：
例如我有以下
1
2
3
4
5
| Name id Col1 Col2
Row1 1 6 1
Row2 2 2 3
Row3 3 9 5
Row4 4 16 8 |
我想将以下所有更新合并到一个查询中
1
2
3
4
5
| 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 ...对重复密钥更新。
使用您的示例：
1
2
| 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); |
既然你有动态值，您需要使用IF或案例要更新的列。它有点难看，但应该可以。
使用你的榜样，你能做到这一点，如：
1
2
3
4
5
6
7
8
9
10
11
12
| 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); |
现在的问题是旧的，但我想用另一种答案扩展话题。
我的观点是，要实现它的最简单的方法就是换行多个查询与交易。接受的答案INSERT ... ON DUPLICATE KEY UPDATE是一个很好的黑客，但应该意识到其弊端和局限性：
-
正如是说，如果你碰巧有行，其主键不存在于表中，查询插入新的"半生不熟"的记录发起查询。可能不是您想要的
-
如果您的表具有不带默认值的非空字段，并且不想在查询中触摸此字段，那么即使您根本不插入任何行，也会收到"Field 'fieldname' doesn't have a default value" MySQL警告。如果您决定严格执行，并将mysql警告转换为应用程序中的运行时异常，它将惹上麻烦。
我提出了一些性能测试三种建议的变体，其中包括INSERT ... ON DUPLICATE KEY UPDATE变型中，用"的情况下/时/然后"条款的变体和带交易幼稚的方法。您可以在此处获取python代码和结果。总体结论是，带有case语句的变体的速度是其他两个变体的两倍，但是很难为其编写正确且注入安全的代码，因此我个人坚持最简单的方法：使用事务。
编辑：Dakusan的调查结果证明我的表现估计都不太有效。请看到这个答案另一个更精细的研究。
不知道为什么没有提到另一个有用的选项：
1
2
3
4
5
6
7
8
9
| 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; |
以下所有内容均适用于InnoDB。
我觉得了解3种不同方法的速度非常重要。
有3种方法：
INSERT：INSERT与对重复密钥更新
交易：如果你做一个更新每个记录的事务中
案例：在您的情况下/时的UPDATE中的每个不同的记录
我只是测试这一点，和INSERT方法是快6.7倍对我来说比交易方法。我尝试了一套既3000个30000行。
TRANSACTION方法仍然必须运行每个单独的查询，这会花费一些时间，尽管它会在执行时将结果批处理到内存中。交易方法也都在复制和查询日志相当昂贵。
更糟糕的是，CASE方法的速度比INSERT方法慢了41.1倍，具有30,000条记录(比TRANSACTION慢6.1倍)。而MyISAM的速度要慢75倍。 INSERT和CASE方法打破，即使在1000?记录。即使在100条记录，案例教学法稍微快。
因此，在一般情况下，我觉得INSERT方法既最好的和最容易使用。查询更小，更易于阅读和只采取了行动1个查询。这适用于InnoDB和MyISAM数据。
奖励的东西：
INSERT非默认字段问题的解决方案是暂时关闭相关的SQL模式：SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA??BLES",""),"STRICT_AL??L_TABLES","")。请务必保存sql_mode第一，如果你打算恢复它。
至于其他评论，我已经看到说使用INSERT方法提高了auto_increment，我也进行了测试，但事实并非如此。
运行测试的代码如下。它还输出.SQL文件以消除php解释器的开销
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
| <?
//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
".implode("
", $Query)."
END
WHERE i1 IN (".implode(',', range(1, $NumRows)).')');
}
print"$TestName:".(microtime(true)-$Start)."
";
file_put_contents("./$TestName.sql", implode(";
", $TheQueries).';');
} |
使用临时表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| // 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`");
} |
1
| UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567' |
这应该适用于你。
MySQL手册中有多个表的参考。
为什么没有人提到在一个查询多个语句？
在PHP中，您使用的mysqli实例multi_query方法。
从PHP手册
MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.
这是在更新30,000 raw中与其他3种方法比较的结果。代码可以在这里找到，其基于回答@Dakusan
交易：5.5194580554962
插入：0.20669293403625
案例：16.474853992462
多：0.0412278175354
正如你所看到的，多条语句的查询比最高的答案更有效。
如果您收到这样的错误消息：
1
| PHP Warning: Error while sending SET_OPTION packet |
您可能需要在我的机器中为/etc/mysql/my.cnf的mysql配置文件中增加max_allowed_packet，然后重新启动mysqld。
您可以为同一张表加上别名，以为您要插入的ID(如果要进行逐行更新：
1
2
3
4
5
6
7
| UPDATE table1 tab1, table1 tab2 -- alias references the same table
SET
col1 = 1
,col2 = 2
. . .
WHERE
tab1.id = tab2.id; |
此外，它应该是显而易见的，你也可以从其他表更新为好。在这种情况下，更新将兼作" SELECT"语句，从而为您提供来自指定表的数据。你在你的查询中显式声明的更新值，因此，第二表未受影响。
您还可能有兴趣在使用上加入更新，这是可能的。
1
2
| 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的"安全机制"，以防止(多个)注入命令。典型的MySQL的"辉煌"的实施，也阻止用户这样做高效的查询。
这里(http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html)是有关设置的C实现的一些信息。
如果您使用的是PHP，则可以使用mysqli来执行多条语句(我认为phpi已随mysqli一起发布了一段时间)
1
2
3
4
5
6
| $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(); |
希望能有所帮助。
使用
1
2
| REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8); |
请注意：
-
id必须是主唯一键
-
如果你使用外键
引用该表，REPLACE删除然后插入，因此这可能
导致错误
是的..它是可以使用INSERT ON DUPLICATE KEY UPDATE SQL语句..
句法：
INSERT INTO TABLE_NAME(A，B，C)VALUES(1,2,3)，(4,5,6)
ON DUPLICATE密钥更新一个= VALUES(A)，B = VALUES(B)，C = VALUES(c)中
以下将更新一个表中的所有行
1
2
| Update Table Set
Column1 = 'New Value' |
下一个会更新所有行，其中列2的值大于5
1
2
3
4
| Update Table Set
Column1 = 'New Value'
Where
Column2 > 5 |
有更新多个表的所有Unkwntech的榜样
1
2
3
4
5
6
| UPDATE table1, table2 SET
table1.col1 = 'value',
table2.col1 = 'value'
WHERE
table1.col3 = '567'
AND table2.col6='567' |
1
| UPDATE tableName SET col1='000' WHERE id='3' OR id='5' |
这应该达到什么样的找你找。只需添加更多的ID。我已经测试过了
1
2
3
4
5
6
7
8
| 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像
1
2
3
4
5
6
7
8
9
10
11
12
| $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); |
因此您可以使用一个查询更新孔表
|
