MySQL中的多个更新

是的,这是可能的-您可以使用insert…在重复的密钥更新时。

使用您的示例:

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

这个问题由来已久,但我想用另一个答案来扩展这个话题。

我的观点是,实现这一点的最简单方法就是用一个事务包装多个查询。公认的答案 INSERT ... ON DUPLICATE KEY UPDATE 是一个不错的黑客,但应该注意其缺点和局限性:

• 如前所述,如果您碰巧使用表中不存在主键的行启动查询,那么查询将插入新的“半烘焙”记录。可能这不是你想要的
• 如果有一个表有一个不带默认值的非空字段,并且不想在查询中触摸该字段,那么您将得到 "Field 'fieldname' doesn't have a default value" MySQL警告,即使您根本不插入一行。如果您决定严格执行,并将MySQL警告转换为应用程序中的运行时异常,这会给您带来麻烦。

我为三个建议的变体做了一些性能测试,包括 插入…重复密钥更新时 variant,一个带有“case/when/then”子句的变量,以及一种简单的事务处理方法。您可以得到python代码和结果 here . 总体的结论是,有case语句的变体的速度是其他两个变体的两倍,但是很难为其编写正确的和注入安全的代码,因此我个人坚持最简单的方法:使用事务。

编辑: 调查结果 Dakusan 证明我的绩效评估不是很有效。请看 this answer 另一个更为详尽的研究。

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

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。

我觉得知道三种不同方法的速度很重要。

有三种方法:

1. 插入:在重复密钥更新时插入
2. 事务:对事务中的每个记录进行更新的位置
3. 案例:更新中每个不同记录的案例/时间

我刚测试过这个,插入方法是 6.7X 比事务处理方法更快。我试了一套3000行和30000行。

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

更糟糕的是,案例方法是 41.1X 低于插入方法w/30000条记录(比事务慢6.1X)。和 75倍 在Myisam中更慢。插入和事例方法甚至在大约1000条记录中打破了平衡。即使是在100条记录下,case方法也快不了多少。

所以总的来说,我觉得insert方法是最好的,也是最容易使用的。查询更小,更容易阅读,只需要1个动作查询。这适用于InnoDB和MyISAM。

奖金:

解决插入非默认字段问题的方法是暂时关闭相关的SQL模式: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES","") . 确保保存 sql_mode 首先,如果你打算恢复它。

至于其他的评论,我已经看到说使用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).';');
}

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

这应该对你有用。

中有一个引用 the MySQL manual 对于多个表。

使用临时表

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

您可以对同一个表进行别名,以提供要插入的ID(如果正在逐行更新:

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

此外,显然您也可以从其他表中进行更新。在这种情况下,更新将加倍为“select”语句,为您提供指定表中的数据。您在查询中显式地声明了更新值,因此第二个表不受影响。

您也可能对在更新时使用联接感兴趣,这也是可能的。

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来执行多语句(我认为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();

希望有帮助。

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

在PHP中,您使用 multi_query mysqli实例的方法。

从 php manual

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

这是与更新30000 raw中其他3种方法的比较结果。可以找到代码 here 基于@dakusan的回答

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

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

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

PHP Warning:  Error while sending SET_OPTION packet

您可能需要增加 max_allowed_packet 在mysql配置文件中,我的计算机中 /etc/mysql/my.cnf 然后重启mysqld。

使用

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

请注意:

• ID必须是主唯一键
• 如果您使用外键 引用表,替换删除,然后插入,因此 引起错误

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

Update Table Set
Column1 = 'New Value'

下一个将更新列2的值大于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'

是..可以在重复的key update sql语句上使用insert.. 语法: 插入到表\名称(A,B,C)值(1,2,3),(4,5,6) 在重复键更新时,A=值(A),B=值(B),C=值(C)

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

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

这应该达到你想要的。只需添加更多的ID。我已经测试过了。

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

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