MySQL中的多个更新-Java 学习之路

335

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

17 回答

-7
您可能也有兴趣在更新时使用连接，这也是可能的 .
```
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.
```
编辑：如果要更新的值不是来自数据库中的其他位置，则需要发出多个更新查询 .
回复于 2024-04-26T00:27:31+08:00

109

是的，这是可能的 - 您可以使用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);

回复于 2024-04-26T00:27:31+08:00

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

回复于 2024-04-26T00:27:31+08:00

-3
问题是陈旧的，但我想用另一个答案来扩展这个话题 .

我的观点是，实现它的最简单方法就是用事务包装多个查询 . 接受的答案 INSERT ... ON DUPLICATE KEY UPDATE 是一个很好的黑客，但应该意识到它的缺点和局限性：
- 如上所述，如果您碰巧使用主键不是't exist in the table, the query inserts new 879494 records. Probably it'的行启动查询
- 如果你有一个没有默认值的非空字段的表，并且即使你根本没有插入一行，也不会得到 "Field 'fieldname' doesn't have a default value" MySQL警告 . 如果您决定严格并在应用程序中将mysql警告转换为运行时异常，它会让您遇到麻烦 .
我对三个建议的变体进行了一些性能测试，包括 INSERT ... ON DUPLICATE KEY UPDATE 变体，带有"case / when / then"子句的变体和带事务的简单方法 . 你可能会得到python代码和结果here . 总的结论是带有case语句的变体的速度是其他两个变体的两倍，但是很难为它编写正确且注入安全的代码，所以我个人坚持使用最简单的方法：使用事务 .

Edit: Dakusan的调查结果证明我的绩效估算不是很有效 . 请参阅this answer进行另一项更精细的研究 .
回复于 2024-04-26T00:27:31+08:00

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

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;

回复于 2024-04-26T00:27:31+08:00

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

我知道3种不同方法的速度很重要 .

有3种方法：

INSERT：使用ON DUPLICATE KEY UPDATE INSERT
TRANSACTION：您在事务中为每条记录执行更新的位置
CASE：在UPDATE中为每个不同记录的情况/时间

我刚刚测试了这个，并且INSERT方法对我来说比TRANSACTION方法快了 6.7x . 我尝试了一组3,000和30,000行 .

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

更糟糕的是，CASE方法比使用30,000条记录的INSERT方法慢了 41.1x （比TRANSACTION慢6.1倍） . 并且在MyISAM中慢了 75x . INSERT和CASE方法甚至达到了约1,000条记录 . 即使在100条记录中，CASE方法也更快 .

所以一般来说，我觉得INSERT方法最好也最容易使用 . 查询更小，更易于阅读，只需要执行1次操作查询 . 这适用于InnoDB和MyISAM .

Bonus stuff:

INSERT非默认字段问题的解决方案是暂时关闭相关的SQL模式： SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES","") . 如果您打算将其恢复，请务必先保存 sql_mode .

至于我看过的其他评论说auto_increment使用INSERT方法上升了，我也测试了它，但似乎并非如此 .

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

回复于 2024-04-26T00:27:31+08:00

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

这应该适用于你 .

the MySQL manual中有一个对多个表的引用 .

回复于 2024-04-26T00:27:31+08:00

-5

使用临时表

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

回复于 2024-04-26T00:27:31+08:00

572
您可以使用同一个表的别名来为您提供要插入的ID（如果您正在进行逐行更新：
```
UPDATE table1 tab1, table1 tab2 -- alias references the same table
SET 
col1 = 1
,col2 = 2
. . . 
WHERE 
tab1.id = tab2.id;
```
此外，显而易见的是，您也可以从其他表更新 . 在这种情况下，更新会兼作“SELECT”语句，为您提供您指定的表中的数据 . 您在查询中明确说明了更新值，因此第二个表不受影响 .
回复于 2024-04-26T00:27:31+08:00
-1
有一个你可以改变的设置称为“多语句”，它禁用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();
```
希望有所帮助 .
回复于 2024-04-26T00:27:31+08:00
-3
为什么没有人提到 multiple statements in one query ？

在php中，你使用mysqli实例的 multi_query 方法 .

来自php manual

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

以下是与更新30,000 raw中的其他3种方法进行比较的结果 . 代码可以找到here，这是基于@Dakusan的回答

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

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

如果您收到如下错误消息：
```
PHP Warning:  Error while sending SET_OPTION packet
```
您可能需要增加mysql配置文件中的 max_allowed_packet ，该文件在我的机器中是 /etc/mysql/my.cnf 然后重新启动mysqld .
回复于 2024-04-26T00:27:31+08:00
2
使用
```
REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);
```
请注意：
- id必须是主要唯一键
- 如果使用外键引用表，则REPLACE删除然后插入，因此这可能会导致错误
回复于 2024-04-26T00:27:31+08:00

-4

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

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'

回复于 2024-04-26T00:27:31+08:00

50

是..可以使用INSERT ON DUPLICATE KEY UPDATE sql语句..语法：INSERT INTO table_name（a，b，c）VALUES（1,2,3），（4,5,6）ON DUPLICATE KEY UPDATE a = VALUES（A），b = VALUES（b），C = VALUES（c）中

回复于 2024-04-26T00:27:31+08:00

用PHP我做到了这一点 . 使用分号，将其拆分为数组，然后通过循环提交 .

$con = new mysqli('localhost','user1','password','my_database');
$batchUpdate = true; /*You can choose between batch and single query */
$queryIn_arr = explode(";", $queryIn);

if($batchUpdate)    /* My SQL prevents multiple insert*/
{
    foreach($queryIn_arr as $qr)
    {
        if(strlen($qr)>3)
        {
            //echo '<br>Sending data to SQL1:<br>'.$qr.'</br>';
            $result = $conn->query($qr);
        }

    }
}
else
{
    $result = $conn->query($queryIn);
}
$con->close();

回复于 2024-04-26T00:27:31+08:00

8
```
UPDATE tableName SET col1='000' WHERE id='3' OR id='5'
```
这应该达到你想要的 . 只需添加更多ID . 我测试过了 .
回复于 2024-04-26T00:27:31+08:00

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

因此，您可以使用一个查询更新孔表

回复于 2024-04-26T00:27:31+08:00

MySQL中的多个更新

17 回答

相关问题