MySQL INSERT ON DUPLICATE KEY UPDATE

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

행이 있는지 없는지 모를 때가 있습니다. INSERT ... ON DUPLICATE KEY UPDATE는 행이 없으면 쓰고 있으면 바꾸는데, 1개의 문으로, 먼저 확인하지 않고 그렇게 합니다. 모든 “이 설정 저장하기”와, 올리기 전에 먼저 만들어져야 하는 모든 카운터 뒤에 있는 문입니다. 이 예제들은 INSERT의 sakila_practice 데이터베이스를 사용합니다. 시작하기 전에 거기 있는 설정 블록을 다시 실행한 다음, mysql -u root -p sakila_practice로 클라이언트를 여세요.

동작 방식

INSERT INTO TABLE_NAME (COLUMN_LIST) VALUES (VALUE_LIST) AS ROW_ALIAS
ON DUPLICATE KEY UPDATE COLUMN_NAME = ROW_ALIAS.COLUMN_NAME;

TABLE_NAME을 테이블로, COLUMN_LIST와 VALUE_LIST를 일반 삽입에서처럼 열과 값으로, ROW_ALIAS를 제시하는 행의 이름으로, COLUMN_NAME을 행이 이미 있을 때 바꿀 열로 바꾸세요. ON DUPLICATE KEY UPDATE 절은 삽입이 PRIMARY KEY나 UNIQUE 인덱스를 깨뜨릴 때만 실행됩니다. 별칭은 삽입하려 했던 행의 이름이므로, ROW_ALIAS.COLUMN_NAME은 제시한 값이고 별칭 없는 열 이름은 이미 저장된 값입니다.

예제

이것이 피하는 충돌

rating_count는 rating을 기본 키로 가집니다. 행을 하나 쓰세요.

INSERT INTO rating_count (rating, films) VALUES ('G', 178);

Query OK, 1 row affected (0.00 sec)

새 개수로 다시 쓰면 삽입이 실패합니다.

INSERT INTO rating_count (rating, films) VALUES ('G', 999);

ERROR 1062 (23000): Duplicate entry 'G' for key 'rating_count.PRIMARY'

삽입하거나, 가로막는 행을 업데이트하기

INSERT INTO rating_count (rating, films) VALUES ('G', 179) AS new
ON DUPLICATE KEY UPDATE films = new.films;

Query OK, 2 rows affected (0.00 sec)

SELECT * FROM rating_count;

+--------+-------+
| rating | films |
+--------+-------+
| G      |   179 |
+--------+-------+
1 row in set (0.00 sec)

1개의 행이 저장되어 있는데 문은 2 rows affected라고 말합니다. 그 수는 문이 어느 분기를 탔는지 말하는 방식입니다: 삽입된 행이면 1, 업데이트된 행이면 2, 업데이트가 이미 올바른 값을 발견했으면 0입니다.

INSERT INTO rating_count (rating, films) VALUES ('G', 179) AS new
ON DUPLICATE KEY UPDATE films = new.films;

Query OK, 0 rows affected (0.00 sec)

행을 찾았고 이미 179를 가지고 있었으므로 쓸 것이 없었습니다. 영향받은 행 수를 테이블의 행 수로 읽지 마세요.

없는 행에 같은 문 실행하기

INSERT INTO rating_count (rating, films) VALUES ('R', 195) AS new
ON DUPLICATE KEY UPDATE films = new.films;

Query OK, 1 row affected (0.00 sec)

SELECT * FROM rating_count ORDER BY rating;

+--------+-------+
| rating | films |
+--------+-------+
| G      |   179 |
| R      |   195 |
+--------+-------+
2 rows in set (0.00 sec)

1 row affected는 삽입 분기입니다. 값 말고는 문장에서 아무것도 바뀌지 않았고, 그것이 이 문장의 핵심입니다: 호출하는 쪽은 행이 있는지 알 필요가 없습니다.

요약 테이블 전체 새로 고치기

행은 쿼리에서 올 수도 있고, 이것이 요약 테이블을 1개의 문으로 최신 상태로 만드는 방법입니다. 거기서는 별칭 형태가 허용되지 않습니다.

INSERT INTO rating_count (rating, films)
SELECT rating, COUNT(*) FROM sakila.film GROUP BY rating
AS new ON DUPLICATE KEY UPDATE films = new.films;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS new ON DUPLICATE KEY UPDATE films = new.films' at line 3

대신 쿼리를 파생 테이블로 감싸서 자체 별칭을 주고, 제시하는 값을 거기서 읽으세요.

INSERT INTO rating_count (rating, films)
SELECT r, c FROM (SELECT rating AS r, COUNT(*) AS c FROM sakila.film GROUP BY rating) AS new
ON DUPLICATE KEY UPDATE films = new.c;

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 1  Warnings: 0

SELECT * FROM rating_count ORDER BY rating;

+--------+-------+
| rating | films |
+--------+-------+
| G      |   178 |
| NC-17  |   210 |
| PG     |   194 |
| PG-13  |   223 |
| R      |   195 |
+--------+-------+
5 rows in set (0.00 sec)

Duplicates: 1은 G 행으로, 179로 저장되어 있다가 지금은 178입니다. 3개의 새 등급이 영향받은 5개의 행 중 3개를 차지하고, 그 1건의 업데이트가 나머지 2개를 차지합니다. R은 이미 195를 가지고 있었으므로 전혀 세어지지 않았습니다.

오래된 코드는 별칭 대신 VALUES()를 씁니다

오래된 문은 별칭 대신 VALUES(films)로 제시된 값을 읽습니다. 그 형태는 여전히 동작하고, 경고를 냅니다.

INSERT INTO rating_count (rating, films) VALUES ('G', 178)
ON DUPLICATE KEY UPDATE films = VALUES(films);
SHOW WARNINGS\G

Query OK, 0 rows affected, 1 warning (0.00 sec)

*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
1 row in set (0.00 sec)

\G는 세미콜론처럼 문장을 끝내고 한 줄에 1개의 열을 출력하는데, 표에 담기에는 너무 넓은 메시지를 읽는 방법이 이것입니다. 새 문장은 별칭으로 쓰고, 이전에 작성된 코드에서는 VALUES()를 만날 것이라고 예상하세요.

요약

행이 있을 수도 없을 수도 있을 때 INSERT ... ON DUPLICATE KEY UPDATE를 쓰세요.
이 절은 PRIMARY KEY나 UNIQUE 충돌에서만 실행된다고 예상하세요.
영향받은 행 수를 삽입이면 1, 업데이트면 2, 변경 없음이면 0으로 읽으세요.
제시하는 행에 AS alias로 이름을 붙이고 그 값을 alias.column으로 읽으세요.
행이 SELECT에서 올 때는 쿼리를 파생 테이블로 감싸세요.
VALUES(col)을 별칭 형태로 바꾸세요. 경고 1287이 요청하는 것이 그것입니다.

함께 보기

INSERT — 기본 형태와 ERROR 1062
INSERT SELECT — 쿼리에서 오는 행
UPDATE — 있다는 것을 아는 행 바꾸기
MySQL의 UPSERT — REPLACE, INSERT IGNORE, 그리고 무엇을 골라야 하는지

MySQL Tutorial

가이드

데이터 저장

쿼리 작성

데이터 보안

성능 최적화

AI로 구축

MySQL 운영

애플리케이션 연결

확장 작성