MySQLでの複数の更新

mysql sql sql-update


一度に複数の行を挿入できることは知っていますが、MySQLで複数の行を一度に(1つのクエリで)更新する方法はありますか?

編集:例えば、私は次のようにしています。

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

以下のすべてのアップデートを1つのクエリにまとめたい

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 Michiel de Mare


はい、それは可能です-あなたは、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);



Answer 2 Harrison Fisk


動的な値を持っているので、更新される列には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);



Answer 3 Roman Imankulov


質問は古いですが、別の回答で話題を広げたいと思います。

私のポイントは、それを達成する最も簡単な方法は、トランザクションで複数のクエリをラップすることです。受け入れられた答え INSERT ... ON DUPLICATE KEY UPDATE は素晴らしいハックですが、その欠点と制限に注意する必要があります。

  • 言われているように、主キーがテーブルに存在しない行でクエリを起動した場合、クエリは新たな「半ベイクド」レコードを挿入します。おそらく、これはあなたが望んでいるものではないでしょう
  • デフォルト値のないnull以外のフィールドを持つテーブルがあり、クエリでこのフィールドを変更したくない場合、 "Field 'fieldname' doesn't have a default value" MySQL警告が表示されます。 t単一の行を挿入します。厳格でmysql警告をアプリの実行時例外に変換する場合、問題が発生します。

INSERT ... ON DUPLICATE KEY UPDATE バリアント、 "case / when / then"句を含むバリアント、トランザクションによる単純なアプローチなど、3つの推奨されるバリアントについていくつかのパフォーマンステストを行いました。あなたはここで Pythonコードと結果を得るかもしれません。全体的な結論として、caseステートメントを含むバリアントは他の2つのバリアントの2倍の速さであることがわかりますが、そのための正確で注入安全なコードを書くのは非常に難しいので、私は個人的にトランザクションを使用するという最も単純なアプローチに固執します。

編集:Dakusanの調査結果は、私のパフォーマンス推定があまり有効でないことを証明しています。より詳細な別の調査については、この回答を参照してください。




Answer 4 newtover


別の有用なオプションがまだ言及されていない理由がよくわからない。

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 Dakusan


以下のすべてがInnoDBに該当します。

3つの方法の速度を知ることは大切なことだと感じています。

3つの方法があります。

  1. INSERT:ON DUPLICATE KEY UPDATEでINSERTします。
  2. トランザクション:トランザクション内の各レコードの更新を行う場所
  3. CASE:UPDATE内の各異なるレコードの場合/場合を指定します。

これをテストしたところ、INSERTメソッドはTRANSACTIONメソッドより6.7倍高速でした。3,000行と30,000行のセットを試しました。

TRANSACTIONメソッドは、実行中にメモリか何かで結果をバッチ処理しますが、それでも個別にクエリを実行しなければならず、時間がかかります。また、TRANSACTIONメソッドはレプリケーションとクエリログの両方でかなりコストがかかります。

さらに悪いことに、CASEメソッドは、30,000レコードのINSERTメソッドより41.1倍遅くなりました(TRANSACTIONより6.1倍遅くなりました)。そして、75X MyISAMテーブルで遅くなります。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の場合はそうではありません。

テストを実行するコードは以下の通りです。また、PHPインタプリタのオーバーヘッドを取り除くために.SQLファイルを出力します。

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