ON DUPLICATE KEY UPDATE
を指定したとき、UNIQUE
インデックスまたは PRIMARY KEY
に重複した値を発生させる行が挿入された場合は、MySQL によって古い行の UPDATE
が実行されます。たとえば、カラム a
が UNIQUE
として宣言され、値 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「パーティショニングとロック」を参照してください。
So if there is a UNIQUE(a,b) constraint on the table in the example, then the INSERT is equivalent to this UPDATE statement:
UPDATE table SET c=c+1 WHERE a=1 AND b=2;
(and not "a=1 OR b=2")
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
This does not work if nothing changes, presumably because MySQL doesn't run the dummy update at all then. I.e. if there is an existing record with 3 in column c then LAST_INSERT_ID() still won't return the AUTO_INCREMENT id afterwards.
I'm not sure whether this should be regarded as a bug or not, but it does make the construct less useful.
so if you have a table with auto_increment column ID and unique key a,b and a smallint dummy column for instance, the query might look like this:
INSERT INTO test (a,b) VALUES ('1','2') ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID),Dummy = NOT dummy;
Now, SELECT LAST_INSERT_ID(); will return the correct ID.
I have a table of totals 'v8totals' with PrimaryKey=(tid,tyy,tmm) and a table of records 'bbprepay'.
Here's how i keep my totals uptodate when the prepays change...
INSERT INTO v8totals (tid,tyy,tmm,finances)
SELECT '3218',YEAR(ppdate),MONTH(ppdate),SUM(ppamount) FROM bbprepay
WHERE fkuserid='3218' GROUP BY YEAR(ppdate),MONTH(ppdate)
ON DUPLICATE KEY UPDATE finances=(SELECT SUM(ppamount) FROM bbprepay
WHERE fkuserid='3218' AND tyy=YEAR(ppdate) AND tmm=MONTH(ppdate) GROUP BY YEAR(ppdate),MONTH(ppdate))
It might not be the best way to do an "Insert otherwise Update" but its working for me. Hope it helps. :)
INSERT INTO tbl_a (a,b,c)
SELECT a,b,c FROM tbl_b
ON DUPLICATE KEY UPDATE c = tbl_b.c
SET @previous_note := NULL;
INSERT INTO rencontre_note_moi_last_votes (id, note) VALUES (1, 2) ON DUPLICATE KEY UPDATE note = IF((@previous_note := note) <> NULL IS NULL, VALUES(note), NULL);
SELECT @previous_note;
Two tricks are actually used here :
(anything) <> NULL is always NULL even if (anything) is NULL. So (anything) <> NULL IS NULL is always TRUE.
@previous_note is set according to the value of a field, and that value is obviously the previous value, not the one being currently computed.
That way, a new "note" is inserted of the "note" is changed, and the previous value is returned.
Best regards,
-Frank.
create table b (a1 integer,a2 integer, primary key (a1));
insert into b values (1,2),(2,2);
select * from b;
insert into b (a1, a2) values(1,2) on duplicate key
update b.a2 = IF(VALUES(a2) < b.a2,b.a2,VALUES(a2));
Translated:
IF new value is less than old value use old value else use new value;
mysql> select * from b;
insert into b (a1, a2) values(1,3) on duplicate key
update b.a2 = IF(VALUES(a2) < b.a2,b.a2,VALUES(a2));
select * from b;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ....
My ultimate approach is to not use the DUPLICATE KEY UPDATE for this purpose. Rather, I explicitly test for existence using a SELECT, and only perform the INSERT if the SELECT fails.
You will still need to check whether your INSERT works, and if it doesn't then you need to do an update.
Rob Smeets
I'd suggest something here with a code example.
I'm told it's not a bug:
http://bugs.mysql.com/bug.php?id=45081
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 (...), ...
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;
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.
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
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