2021-12-14 14:22 작성

My SQL basic 메모

Table of contents

MySQL INSERT ON DUPLICATE KEY UPDATE 구문 소개

만약 새로운 row가 UNIQUE index 혹은 PRIMARY KEY에서 중복을 일으킬 경우 MySQL은 에러를 발생시킨다.

그러나 만약 INSERT 구문에서 ON DUPLICATE KEY UPDTATE option을 명시할 경우, MySQL은 이미 존재하는 row에 새로운 values를 update할 것이다.

INSERT ON DUPLICATE KEY UPDATE 구문의 문법은 다음과 같다:

INSERT INTO table (column_list)
VALUES (value_list)
ON DUPLICATE KEY UPDATE
    c1 = v1,
    c2 = v2.
    ...;

INSERT 구문에 추가된 것은 ON DUPLICATE KEY UPDATE 뿐이며 이곳에 중복되는 경우를 대비해 column-value-pair(column-value 짝)를 명시한다.

기본적으로 이 구문은 새로운 row를 table에 삽입하려 시도한다. 만약 중복 에러가 발생하면, 이미 존재하는 row에 ON DUPLICATE KEY UPDATE에서 명시한 방법대로 update 한다.

MySQL은 수행하는 활동에 기반해 영향을 받는 rows의 수를 반환한다:

DUPLICATE KEY UPDATE 절에서 INSERT 절의 values를 사용하기 위해서 VALUES() function을 사용한다:

INSERT INTO table_name(c1)
VALUES(c1)
ON DUPLICATE KEY UPDATE c1 = VALUES(c1) | 1;

상기의 구문은 UNIQUE index 혹은 PRIMARY KEY에서 중복이 발생한 경우 VALUES(c1)에 1을 더해 기존의 c1을 현재의 value로 설정한다.

INSERT ON DUPLICATE KEY UPDATE가 어떻게 작동하는지 이해하기 위해 예시를 들어 확인해보자.

첫째, network devices를 저장하는 새로운 devices table을 생성한다:

CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

다음은 devices table에 rows를 삽입한다.

INSERT INTO devices(name)
VALUES('Router F1'),('Switch 1'),('Switch 2');

그리고 devices table에 삽입된 데이터를 확인하기 위해 데이터를 쿼리한다.

SELECT 
    id, 
    name
FROM	
    devices;

output:

이제 devices table에 3개의 rows가 있다.

여기에 하나의 row를 더 삽입해보자.

INSERT INTO
    devices(name)
VALUES
    ('Printer')
ON DUPLICATE KEY UPDATE name = 'Printer';

output:

중복이 없기 때문에 MySQL은 devices table에 새로운 row를 삽입한다. 위 구문은 다음의 구문과 같은 효과를 가진다.

INSERT INTO devices(name)
VALUES ('Printer');

마지막으로, id column에 중복된 value와 함께 새로운 row를 삽입해보자.

INSERT INTO devices(id,name) 
VALUES 
   (4,'Printer') 
ON DUPLICATE KEY UPDATE name = 'Central Printer';

MySQL은 다음의 메시지를 출력한다:

Query OK, 2 rows affected (0.02 sec)

output:

다음은 참고할 수 있는 추가 예시다.

INSERT INTO devices(id, name)
VALUES
    (4, 'Printer')
ON DUPLICATE KEY UPDATE name = CONCAT('MIDDLE ',VALUES(name));

MySQL은 다음의 메시지를 출력한다:

Query OK, 2 rows affected, 1 warning (0.02 sec)

쿼리가 성공적으로 실행되었고 이 부분에서 발생하는 warning은 크게 신경쓰지 않아도 된다.

output: