Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.9Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb


13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文

ON DUPLICATE KEY UPDATE を指定したとき、UNIQUE インデックスまたは PRIMARY KEY に重複した値を発生させる行が挿入された場合は、MySQL によって古い行の UPDATE が実行されます。たとえば、カラム aUNIQUE として宣言され、値 1 を含んでいる場合、次の 2 つのステートメントには同様の効果があります。

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

(これらの効果は、a が自動インクリメントカラムである InnoDB テーブルに対して同じではありません。自動インクリメントカラムを使用した場合、INSERT ステートメントは自動インクリメント値を増やしますが、UPDATE は増やしません。)

ON DUPLICATE KEY UPDATE 句には、カンマで区切られた、複数のカラム割り当てを含めることができます。

ON DUPLICATE KEY UPDATE を使用した場合、行ごとの影響を受けた行の値は、その行が新しい行として挿入された場合は 1、既存の行が更新された場合は 2、既存の行がその現在の値に設定された場合は 0 です。mysqld への接続時に CLIENT_FOUND_ROWS フラグを mysql_real_connect() に指定すると、既存の行がその現在の値に設定された場合の影響を受けた行の値は (0 ではなく) 1 になります。

カラム b も一意である場合、INSERT は、代わりに次の UPDATE ステートメントと同等です。

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

a=1 OR b=2 が複数の行に一致する場合は、1 つの行だけが更新されます。一般に、一意のインデックスが複数含まれているテーブルに対して ON DUPLICATE KEY UPDATE 句を使用することは避けるようにしてください。

UPDATE 句で VALUES(col_name) 関数を使用して、INSERT ... ON DUPLICATE KEY UPDATE ステートメントの INSERT 部分からカラム値を参照できます。つまり、ON DUPLICATE KEY UPDATE 句にある VALUES(col_name) は、重複キーの競合が発生していない場合に挿入される col_name の値を参照します。この関数は、複数の行を挿入する際に特に役立ちます。VALUES() 関数は、INSERT ... UPDATE ステートメントの中でだけ意味を持ち、そうでなければ NULL を返します。例:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

そのステートメントは、次の 2 つのステートメントと同一です。

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

テーブルに AUTO_INCREMENT カラムが含まれているときに、INSERT ... ON DUPLICATE KEY UPDATE で行を挿入または更新した場合、LAST_INSERT_ID() 関数は AUTO_INCREMENT 値を返します。

ON DUPLICATE KEY UPDATE を使用している場合、DELAYED オプションは無視されます。

INSERT ... SELECT ステートメントの結果は SELECT からの行の順序に依存し、またこの順序を常に保証することはできないため、ロギング時に、INSERT ... SELECT ON DUPLICATE KEY UPDATE ステートメントがマスターとスレーブで異なる可能性があります。そのため、MySQL 5.6.4 以降では、INSERT ... SELECT ON DUPLICATE KEY UPDATE ステートメントには、ステートメントベースのレプリケーションには安全でないというフラグが付けられます。この変更により、このようなステートメントは、ステートメントベースモードを使用しているときはログ内に警告を生成し、MIXED モードを使用しているときは行ベース形式を使用してログに記録されます。さらに、MySQL 5.6.6 からは、一意のキーまたは主キーが複数含まれているテーブルに対する INSERT ... ON DUPLICATE KEY UPDATE ステートメントも安全ではないとしてマークされます。(Bug #11765650、Bug #58637) セクション17.1.2.1「ステートメントベースおよび行ベースレプリケーションのメリットとデメリット」も参照してください。

MySQL 5.6.6 より前は、テーブルレベルのロックを採用した MyISAM などのストレージエンジンを使用しているパーティション化されたテーブルに対する INSERT ... ON DUPLICATE KEY UPDATE によって、そのテーブルのすべてのパーティションがロックされました。(これは、行レベルロックを採用した InnoDB などのストレージエンジンを使用しているテーブルでは発生しておらず、現在も発生しません。)MySQL 5.6.6 以降では、このようなステートメントでは、パーティション化キーカラムが更新されたパーティションのみがロックされます。詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Nikolay Pelov on May 18, 2011
This won't work:

INSERT INTO xxx(id,val) SELECT a,b FROM yyyy ON DUPLICATE KEY UPDATE val = VALUES(val)

Taking value that will be inserted with VALUES() only works with INSERT INTO .... VALUES (...), ...
  Posted by HungNghiep Tran on October 16, 2011
Here is a nice tip for INSERT INTO SELECT ON DUPLICATE KEY UPDATE. Better than Jon Webb's example mentioned above.
The trick is to use user-defined variable to store computed data, so that it is not need to be computed again.
This will also solve Nikolay Pelov's problem in the previous post.

insert
into _Rank_Author(idAuthor, publicationCount, citationCount, coAuthorCount)
select ap.idAuthor, @publicationCount := count(distinct ap.idPaper), 0, 0
from author_paper ap
group by ap.idAuthor
on duplicate key update publicationCount = @publicationCount;
  Posted by Lane Snider on February 22, 2012
I don't see it documented, but it looks like NULL values do not trip the "duplicate key" feature.

Consider a unique key definition like this:
UNIQUE KEY `friend_id` (`friend_id`,`type`,`status`)
Putting in two rows with identical values in those three fields should obviously not be allowed, but it can happen if the field value happens to be NULL in both rows.

A bug? A case of me missing the documentation? Whatever. There it is.
  Posted by Rami Jamleh on May 2, 2012
well it's cant be safe and OR is not AND
when we say A=1 OR B=5 limit 1
and we have the following table
A | B
1 | 6
1 | 5

then it well update the first row only where b = 6

  Posted by Proxy . on August 31, 2012
Note that ON DUPLICATE KEY UPDATE checks every unique fields in table, not just PRIMARY key (page_id for example). So care when you have other unique fields (page_seo_url for example).
  Posted by Geoff Kendall on October 21, 2012
Probably obvious to more experienced users, but the number of rows affected can be 0 as well as the 2 or 1 values mentioned above, if the ON DUPLICATE KEY UPDATE does not change the existing column values.
  Posted by Ivan Levashew on June 9, 2013
Don't forget that UPDATE in MySQL is not standard-conformant. Old values being referenced in UPDATE are getting replaced one by one from left to right to their new values. This is counter-intuitive given that VALUES are being applied simultaneously and UPDATE expressions are not.
  Posted by Jonas Reinhardt on March 19, 2014
When using ON DUPLICATE KEY in combination with a BEFORE INSERT trigger note that if you update a NEW.col_name value in the BEFORE INSERT trigger this will effect the value och values(col_name) in then ON DUPLiCATE KEY UPDATE statement!
Updates to other NEW.col_name in the BEFORE INSERT trigger that is not used in the values(col_name) statement are discarded.
A testcase is available at http://sqlfiddle.com/#!2/b324a/1
  Posted by Sam Daams on September 19, 2014
If you have an autoincrement pk, and a unique key on say an email address, and the 'on duplicate update' triggers based on the email address, note that the last_insert_id' will NOT be the autoincrement value of the updated row. It appears to be the most recently inserted autoincrement value. This makes a huge difference. To work around this, use the workaround from 5.1 and earlier: id=LAST_INSERT_ID(id) in the updating query.
Sign Up Login You must be logged in to post a comment.