MySQL 中的多次更新

Question

提问by Teifion

I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL?

我知道您可以一次插入多行，有没有办法在 MySQL 中一次更新多行（如在一个查询中）？

Edit: For example I have the following

编辑：例如我有以下内容

Name   id  Col1  Col2
Row1   1    6     1
Row2   2    2     3
Row3   3    9     5
Row4   4    16    8

I want to combine all the following Updates into one query

我想将以下所有更新合并为一个查询

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

回答by Michiel de Mare

Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

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

Using your example:

使用您的示例：

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

回答by Harrison Fisk

Since you have dynamic values, you need to use an IF or CASE for the columns to be updated. It gets kinda ugly, but it should work.

由于您有动态值，您需要对要更新的列使用 IF 或 CASE。它变得有点丑陋，但它应该可以工作。

Using your example, you could do it like:

使用您的示例，您可以这样做：

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

回答by Roman Imankulov

The question is old, yet I'd like to extend the topic with another answer.

这个问题很老，但我想用另一个答案扩展这个话题。

My point is, the easiest way to achieve it is just to wrap multiple queries with a transaction. The accepted answer INSERT ... ON DUPLICATE KEY UPDATEis a nice hack, but one should be aware of its drawbacks and limitations:

我的观点是，实现它的最简单方法就是用一个事务包装多个查询。接受的答案INSERT ... 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.

如上所述，如果您碰巧使用表中不存在主键的行启动查询，则查询会插入新的“半成品”记录。可能这不是你想要的
如果您有一个包含非空字段且没有默认值的表，并且不想在查询中触及此字段，"Field 'fieldname' doesn't have a default value"即使您根本不插入单行，您也会收到MySQL 警告。如果您决定严格并将 mysql 警告转换为应用程序中的运行时异常，这会给您带来麻烦。

I made some performance tests for three of suggested variants, including the INSERT ... ON DUPLICATE KEY UPDATEvariant, 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.

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

Edit:Findings of Dakusanprove that my performance estimations are not quite valid. Please see this answerfor another, more elaborate research.

编辑：Dakusan 的调查结果证明我的性能估计不太有效。请参阅此答案以进行另一项更详细的研究。

Answer 4

回答by newtover

Not sure why another useful option is not yet mentioned:

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

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

回答by Dakusan

All of the following applies to InnoDB.

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

I feel knowing the speeds of the 3 different methods is important.

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

There are 3 methods:

有3种方法：

INSERT: INSERT with ON DUPLICATE KEY UPDATE
TRANSACTION: Where you do an update for each record within a transaction
CASE: In which you a case/when for each different record within an UPDATE

插入：在重复密钥更新时插入
TRANSACTION：您在何处对交易中的每条记录进行更新
案例：在这种情况下，您为 UPDATE 中的每个不同记录设置一个案例/时间

I just tested this, and the INSERT method was 6.7xfaster for me than the TRANSACTION method. I tried on a set of both 3,000 and 30,000 rows.

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

The TRANSACTION method still has to run each individually query, which takes time, though it batches the results in memory, or something, while executing. The TRANSACTION method is also pretty expensive in both replication and query logs.

TRANSACTION 方法仍然必须运行每个单独的查询，这需要时间，尽管它在执行时在内存或其他东西中批处理结果。TRANSACTION 方法在复制和查询日志中也非常昂贵。

Even worse, the CASE method was 41.1xslower than the INSERT method w/ 30,000 records (6.1x slower than TRANSACTION). And 75xslower in MyISAM. INSERT and CASE methods broke even at ~1,000 records. Even at 100 records, the CASE method is BARELY faster.

更糟的是，CASE方法是41.1x比INSERT方法较慢W / 30000条记录（6.1倍比交易更慢）。和75X较慢的MyISAM。INSERT 和 CASE 方法在大约 1,000 条记录时收支平衡。即使有 100 条记录，CASE 方法的速度也几乎没有。

So in general, I feel the INSERT method is both best and easiest to use. The queries are smaller and easier to read and only take up 1 query of action. This applies to both InnoDB and MyISAM.

所以总的来说，我觉得 INSERT 方法是最好的，也是最容易使用的。查询更小且更易于阅读，并且仅占用 1 个操作查询。这适用于 InnoDB 和 MyISAM。

Bonus stuff:

奖金东西：

The solution for the INSERT non-default-field problem is to temporarily turn off the relevant SQL modes: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES",""). Make sure to save the sql_modefirst if you plan on reverting it.

INSERT 非默认字段问题的解决方法是暂时关闭相关的SQL 模式：SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES",""). sql_mode如果您打算恢复它，请确保保存第一个。

As for other comments I've seen that say the auto_increment goes up using the INSERT method, this does seem to be the case in InnoDB, but not MyISAM.

至于我看到的其他评论，说 auto_increment 使用 INSERT 方法上升，这在 InnoDB 中似乎确实如此，但在 MyISAM 中则不然。

Code to run the tests is as follows. It also outputs .SQL files to remove php interpreter overhead

运行测试的代码如下。它还输出 .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).';');
}

Answer 6

回答by Laymain

Use a temporary table

使用临时表

// 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`");
}

Answer 7

回答by UnkwnTech

UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'

This should work for ya.

这应该对你有用。

There is a reference in the MySQL manualfor multiple tables.

有在参考MySQL手册为多个表。

Answer 8

回答by mononoke

Why does no one mention multiple statements in one query?

为什么没有人在一个查询中提到多个语句？

In php, you use multi_querymethod of mysqli instance.

在 php 中，您使用multi_querymysqli 实例的方法。

From the php manual

来自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.

MySQL 允许在一个语句字符串中包含多个语句。一次发送多个语句可以减少客户端-服务器的往返次数，但需要特殊处理。

Here is the result comparing to other 3 methods in update 30,000 raw. Code can be found herewhich is based on answer from @Dakusan

这是与 update 30,000 raw 中其他 3 种方法相比的结果。可以在此处找到代码，该代码基于@Dakusan 的回答

Transaction: 5.5194580554962
Insert: 0.20669293403625
Case: 16.474853992462
Multi: 0.0412278175354

交易：5.5194580554962
插入：0.20669293403625
案例：16.474853992462
多：0.0412278175354

As you can see, multiple statements query is more efficient than the highest answer.

如您所见，多语句查询比最高答案更有效。

If you get error message like this:

如果您收到这样的错误消息：

PHP Warning:  Error while sending SET_OPTION packet

You may need to increase the max_allowed_packetin mysql config file which in my machine is /etc/mysql/my.cnfand then restart mysqld.

您可能需要增加max_allowed_packet我机器中的 mysql 配置文件，/etc/mysql/my.cnf然后重新启动 mysqld。

Answer 9

回答by eggmatters

You can alias the same table to give you the id's you want to insert by (if you are doing a row-by-row update:

您可以为同一个表设置别名，以便为您提供要插入的 ID（如果您正在进行逐行更新：

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

Additionally, It should seem obvious that you can also update from other tables as well. In this case, the update doubles as a "SELECT" statement, giving you the data from the table you are specifying. You are explicitly stating in your query the update values so, the second table is unaffected.

此外，很明显您也可以从其他表进行更新。在这种情况下，更新兼作“SELECT”语句，为您提供指定表中的数据。您在查询中明确说明了更新值，因此第二个表不受影响。

Answer 10

回答by Brooks

There is a setting you can alter called 'multi statement' that disables MySQL's 'safety mechanism' implemented to prevent (more than one) injection command. Typical to MySQL's 'brilliant' implementation, it also prevents user from doing efficient queries.

您可以更改一个名为“multi statement”的设置，该设置禁用 MySQL 的“安全机制”以防止（多个）注入命令。典型的 MySQL '辉煌' 实现，它也阻止用户进行有效的查询。

Here (http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html) is some info on the C implementation of the setting.

这里（http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html）是有关设置的 C 实现的一些信息。

If you're using PHP, you can use mysqli to do multi statements (I think php has shipped with mysqli for a while now)

如果您使用的是 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();

Hope that helps.

希望有帮助。

MySQL 中的多次更新

提问by Teifion

回答by Michiel de Mare

回答by Harrison Fisk

回答by Roman Imankulov

回答by newtover

回答by Dakusan

回答by Laymain

回答by UnkwnTech

回答by mononoke

回答by eggmatters

回答by Brooks

相关推荐

最近更新

标签

MySQL 中的多次更新

提问by Teifion

回答by Michiel de Mare

回答by Harrison Fisk

回答by Roman Imankulov

回答by newtover

回答by Dakusan

回答by Laymain

回答by UnkwnTech

回答by mononoke

回答by eggmatters

回答by Brooks

相关推荐

ios Swift 3 更改日期格式

ios 如何解决“缺少 Info.plist 键...... NSPhotoLibraryUsageDescription”

ios 如何在 Core Data 中使用带有 fetchRequest 的谓词

ios 未找到 `React/RCTBridgeModule.h` 文件

相关推荐

最近更新

标签