MySQL 中的多次更新

发布于 2024-07-04 01:54:23 字数 464 浏览 6 评论 0原文

我知道你可以一次插入多行，有没有一种方法可以在 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;

原文

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?

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

╰沐子 2024-07-11 01:54:24

UPDATE tableName SET col1='000' WHERE id='3' OR id='5'

这应该可以达到您想要的效果。只需添加更多 id 即可。我已经测试过了。

UPDATE tableName SET col1='000' WHERE id='3' OR id='5'

This should achieve what you'r looking for. Just add more id's. I have tested it.

回复收藏 0 原文

辞慾 2024-07-11 01:54:24

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);

你就可以用一个查询更新孔表

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`)

// You just building it in php like

$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);

So you can update hole table with one query

回复收藏 0 原文

谜泪 2024-07-11 01:54:24

我从 @newtover 获取了答案，并使用 MySql 8 中的新 json_table 函数对其进行了扩展。这允许您创建一个存储过程来处理工作负载，而不是在代码中构建自己的 SQL 文本：

drop table if exists `test`;
create table `test` (
  `Id` int,
  `Number` int,
  PRIMARY KEY (`Id`)
);
insert into test (Id, Number) values (1, 1), (2, 2);

DROP procedure IF EXISTS `Test`;
DELIMITER $
CREATE PROCEDURE `Test`(
    p_json json
)
BEGIN
    update test s
        join json_table(p_json, '$[*]' columns(`id` int path '$.id', `number` int path '$.number')) v 
        on s.Id=v.id set s.Number=v.number;
END$
DELIMITER ;

call `Test`('[{"id": 1, "number": 10}, {"id": 2, "number": 20}]');
select * from test;

drop table if exists `test`;

它比纯 SQL 慢几毫秒，但是我很乐意接受打击，而不是在代码中生成 sql 文本。不确定它对于巨大的记录集（JSON 对象的最大大小为 1Gb）的性能如何，但我在一次更新 10k 行时一直使用它。

I took the answer from @newtover and extended it using the new json_table function in MySql 8. This allows you to create a stored procedure to handle the workload rather than building your own SQL text in code:

drop table if exists `test`;
create table `test` (
  `Id` int,
  `Number` int,
  PRIMARY KEY (`Id`)
);
insert into test (Id, Number) values (1, 1), (2, 2);

DROP procedure IF EXISTS `Test`;
DELIMITER $
CREATE PROCEDURE `Test`(
    p_json json
)
BEGIN
    update test s
        join json_table(p_json, '$[*]' columns(`id` int path '$.id', `number` int path '$.number')) v 
        on s.Id=v.id set s.Number=v.number;
END$
DELIMITER ;

call `Test`('[{"id": 1, "number": 10}, {"id": 2, "number": 20}]');
select * from test;

drop table if exists `test`;

It's a few ms slower than pure SQL but I'm happy to take the hit rather than generate the sql text in code. Not sure how performant it is with huge recordsets (the JSON object has a max size of 1Gb) but I use it all the time when updating 10k rows at a time.

回复收藏 0 原文

蓝眼泪 2024-07-11 01:54:24

最简单的方法

UPDATE companies
SET vat_number = 
  (CASE id WHEN 1 THEN '123'
           WHEN 2 THEN '345'
           WHEN 3 THEN '456'
   END)
WHERE id IN (1,2,3);

Simplest Way

UPDATE companies
SET vat_number = 
  (CASE id WHEN 1 THEN '123'
           WHEN 2 THEN '345'
           WHEN 3 THEN '456'
   END)
WHERE id IN (1,2,3);

回复收藏 0 原文

诗笺 2024-07-11 01:54:24

以下将更新一个表中的所有行

Update Table Set
Column1 = 'New Value'

下一个将更新 Column2 的值大于 5 的所有行

Update Table Set
Column1 = 'New Value'
Where
Column2 > 5

全部都是 Unkwntech 更新多个表的示例

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

The following will update all rows in one table

Update Table Set
Column1 = 'New Value'

The next one will update all rows where the value of Column2 is more than 5

Update Table Set
Column1 = 'New Value'
Where
Column2 > 5

There is all Unkwntech's example of updating more than one table

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

回复收藏 0 原文

許願樹丅啲祈禱 2024-07-11 01:54:24

使用

REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);

请注意：

id 必须是主唯一键
如果您使用外键，则
引用表，REPLACE 删除然后插入，所以这可能
导致错误

use

REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);

Please note:

id has to be a primary unique key
if you use foreign keys to
reference the table, REPLACE deletes then inserts, so this might
cause an error

回复收藏 0 原文

相思碎 2024-07-11 01:54:24

是的..可以使用 INSERT ON DUPLICATE KEY UPDATE sql 语句..
句法：
INSERT INTO 表名 (a,b,c) 值 (1,2,3),(4,5,6)
重复密钥更新 a=VALUES(a),b=VALUES(b),c=VALUES(c)

回复收藏 0 原文

旧伤慢歌 2024-07-11 01:54:23

您可能还对在更新上使用联接感兴趣，这也是可能的。

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.

编辑：如果您要更新的值不是来自数据库中的其他位置，您将需要发出多个更新查询。

You may also be interested in using joins on updates, which is possible as well.

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.

Edit: If the values you are updating aren't coming from somewhere else in the database, you'll need to issue multiple update queries.

回复收藏 0 原文

爱的十字路口 2024-07-11 01:54:23

还没有人提到对我来说更简单的方法来做到这一点 - 使用允许您执行多个单独查询的 SQL 编辑器。此屏幕截图来自 Sequel Ace，我假设 Sequel Pro 和可能其他编辑器具有类似的功能。（当然，这是假设您只需要将其作为一次性的事情来运行，而不是作为应用程序/网站的集成部分）。

回复收藏 0 原文

清旖 2024-07-11 01:54:23

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

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

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

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:

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.

回复收藏 0 原文

前事休说 2024-07-11 01:54:23

您可以更改一个名为“多语句”的设置，它会禁用 MySQL 的“安全机制”，以防止（多个）注入命令。这是 MySQL“出色”实现的典型特征，它还会阻止用户进行高效查询。

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

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

希望有所帮助。

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.

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.

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

$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.

回复收藏 0 原文

秋风の叶未落 2024-07-11 01:54:23

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

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

来自 PHP 手册

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

以下是更新 30,000 个原始数据时与其他 3 种方法的结果比较。代码可以在此处找到，该代码基于以下答案@Dakusan

交易：5.5194580554962
插入：0.20669293403625
案例：16.474853992462
Multi: 0.0412278175354

可以看到，多语句查询比最高答案效率更高。

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

PHP Warning:  Error while sending SET_OPTION packet

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

Why does no one mention multiple statements in one query?

In php, you use multi_query method of mysqli instance.

From the php manual

MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

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

Transaction: 5.5194580554962
Insert: 0.20669293403625
Case: 16.474853992462
Multi: 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_packet in mysql config file which in my machine is /etc/mysql/my.cnf and then restart mysqld.

回复收藏 0 原文

·深蓝 2024-07-11 01:54:23

使用临时表

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

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

回复收藏 0 原文

坦然微笑 2024-07-11 01:54:23

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

这应该对你有用。

MySQL 手册中有关于多个表的参考。

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 manual for multiple tables.

回复收藏 0 原文

顾忌 2024-07-11 01:54:23

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

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

有 3 种方法：

INSERT：使用 ON DUPLICATE KEY UPDATE
TRANSACTION 进行插入：您对事务中的每条记录进行更新案例
：其中您对 UPDATE 中的每个不同记录进行案例/何时

我刚刚测试了这一点，并且 INSERT对我来说，方法比 TRANSACTION 方法快 6.7 倍 [使用准备好的事务语句将其时间缩短了约 20%]。我尝试了一组 3,000 行和 30,000 行。

TRANSACTION 方法仍然必须运行每个单独的查询，这需要时间，尽管它在执行时将结果批量存储在内存或其他内容中。 TRANSACTION 方法在复制和查询日志中也相当昂贵。

更糟糕的是，CASE 方法比包含 30,000 条记录的 INSERT 方法慢 41.1 倍（比 TRANSACTION 慢 6.1 倍）。 MyISAM 中的速度75 倍慢。 INSERT 和 CASE 方法在约 1,000 条记录时实现收支平衡。即使有 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，请务必先保存它。

至于我看到的其他评论说使用 INSERT 方法 auto_increment 上升，这似乎是 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).';');
}

All of the following applies to InnoDB.

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

There are 3 methods:

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

I just tested this, and the INSERT method was 6.7x faster for me than the TRANSACTION method [using prepared statements with the transactions lowered its time by ~20%]. I tried on a set of both 3,000 and 30,000 rows.

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.

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

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.

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_mode first if you plan on reverting it.

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.

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

<?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).';');
}

回复收藏 0 原文

万劫不复 2024-07-11 01:54:23

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

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;

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;

回复收藏 0 原文

孤独患者 2024-07-11 01:54:23

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

我的观点是，实现它的最简单方法就是用一个事务包装多个查询。接受的答案 INSERT ... ON DUPLICATE KEY UPDATE 是一个很好的技巧，但应该意识到它的缺点和限制：

正如前面所说，如果您碰巧使用主键的行启动查询表中不存在，查询会插入新的“半成品”记录。可能这不是您想要的
如果您有一个没有默认值的非空字段的表，并且不想在查询中触及此字段，您将得到 "Field 'fieldname' does not have a default value" MySQL 警告，即使你根本不插入任何一行。如果您决定严格并将 mysql 警告转变为应用程序中的运行时异常，那么它会给您带来麻烦。

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

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

回复收藏 0 原文

昔日梦未散 2024-07-11 01:54:23

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

使用你的例子：

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);

Yes, that's possible - you can use 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);

回复收藏 0 原文

べ繥欢鉨o。 2024-07-11 01:54:23

由于您有动态值，因此需要对要更新的列使用 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);

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.

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);

回复收藏 0 原文

挽梦忆笙歌 2024-07-11 01:54:23

现在简单的方法

update my_table m, -- let create a temp table with populated values
    (select 1 as id, 20 as value union -- this part will be generated
     select 2 as id, 30 as value union -- using a backend code
     -- for loop 
     select N as id, X as value
        ) t
set m.value = t.value where t.id=m.id -- now update by join - quick

And now the easy way

update my_table m, -- let create a temp table with populated values
    (select 1 as id, 20 as value union -- this part will be generated
     select 2 as id, 30 as value union -- using a backend code
     -- for loop 
     select N as id, X as value
        ) t
set m.value = t.value where t.id=m.id -- now update by join - quick

回复收藏 0 原文

~没有更多了~