MySQL 중복 레코드 관리 방법 (INSERT 시 중복 키 관리 방법 (INSERT IGNORE, REPLACE INTO, …
페이지 정보
본문
MySQL 중복 레코드 관리 방법 (INSERT 시 중복 키 관리 방법 (INSERT IGNORE, REPLACE INTO, ON DUPLICATE UPDATE))
1. 개요
MySQL에는 아래 3가지 방법을 이용하여 중복 레코드를 관리할 수 있다.
INSERT IGNORE ...
REPLACE INTO ...
INSERT INTO ... ON DUPLICATE UPDATE
| 방법 | 특징 |
|---|---|
INSERT IGNORE ... | 최초 입수된 레코드가 남아 있음 최초 입수된 레코드의 AUTO_INCREMENT 값은 변하지 않음 |
REPLACE INTO ... | 최초 입수된 레코드가 삭제되고, 신규 레코드가 INSERT됨 AUTO_INCREMENT의 값이 변경됨 |
INSERT INTO ... ON DUPLICATE UPDATE | INSERT IGNORE의 장점 포함함 중복 키 오류 발생 시, 사용자가 UPDATE될 값을 지정할 수 있음 |
2. 사전 조건
중복 레코드 관리를 위해선 테이블에 PRIMARY KEY 혹은 UNIQUE INDEX가 필요하다.
본 예제에서는 아래와 같은 person 테이블을 이용하여 설명한다.
CREATE TABLE person
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
address VARCHAR(255),
PRIMARY KEY (id),
UNIQUE INDEX (name) -- 중복 검사용 필드
);
person 테이블에서 PRIMARY KEY로 지정된 id 필드는 AUTO_INCREMENT를 위해 만든 필드이며, 본 문서에는 name 필드를 이용하여 중복을 검사한다.
만약 기존에 만들어진 테이블에 PRIMARY KEY 혹은 UNIQUE INDEX를 추가하려면 아래의 SQL을 이용하면 된다.
-- PRIMARY 추가하는 방법
ALTER TABLE person ADD PRIMARY KEY (name)
-- UNIQUE INDEX를 추가하는 방법
ALTER TABLE person ADD UNIQUE INDEX (name)3. 중복 처리 방법
3-1) INSERT IGNORE
INSERT IGNORE는 중복 키 에러가 발생했을 때 신규로 입력되는 레코드를 무시하는 단순한 방법이다.
다음의 예를 보면 중복 키 에러가 발생했을 때 INSERT 구문 자체는 오류가 발생하지 않고, 대신’0 row affected’가 출력된 것을 볼 수 있다.
mysql> INSERT IGNORE INTO person VALUES (NULL, 'James', 'Seoul');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person VALUES (NULL, 'Cynthia', 'Yongin');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person VALUES (NULL, 'James', 'Seongnam');
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT IGNORE INTO person VALUES (NULL, 'Cynthia', 'Seoul');
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT IGNORE INTO person VALUES (NULL, 'James', 'Incheon');
Query OK, 0 rows affected (0.00 sec)
SELECT의 결과는 2건만 존재한다.
mysql> SELECT * FROM person;
+----+---------+---------+
| id | name | address |
+----+---------+---------+
| 1 | James | Seoul |
| 2 | Cynthia | Yongin |
+----+---------+---------+
2 rows in set (0.00 sec)
James의 주소가 최초 입력한 Seoul이다. 즉, 최초에 입력된 레코드가 남아 있는 걸을 볼 수 있다.
또한 AUTO_INCREMENT 컬럼의 값이 1, 2인 것에 주목하라.
MySQL에서 AUTO_INCREMENT는 식별키 용도로 많이 사용하는데, 중복 발생 여부에 따라 식별 키가 변경되는 경우 여러 가지 불편한 점이 생긴다.
INSERT IGNORE에서는 AUTO_INCREMENT의 값이 변경되지 않는다는 장점이 있다.
3-2) REPLACE INTO
REPLACE INTO는 중복이 발생되었을 때 기존 레코드를 삭제하고 신규 레코드를 INSERT하는 방식이다.
person 테이블을 drop 후 다시 생성한 뒤에 아래의 레코드를 입수해보자.
mysql> REPLACE INTO person VALUES (NULL, 'James', 'Seoul');
Query OK, 1 row affected (0.00 sec)
mysql> REPLACE INTO person VALUES (NULL, 'Cynthia', 'Yongin');
Query OK, 1 row affected (0.00 sec)
mysql> REPLACE INTO person VALUES (NULL, 'James', 'Seongnam');
Query OK, 2 rows affected (0.00 sec)
mysql> REPLACE INTO person VALUES (NULL, 'Cynthia', 'Seoul');
Query OK, 2 rows affected (0.00 sec)
mysql> REPLACE INTO person VALUES (NULL, 'James', 'Incheon');
Query OK, 2 rows affected (0.00 sec)
세번째 레코드를 입수할 때부터는 ‘2 rows affected’가 출력되었다. (INSERT IGNORE에서는 ‘0 rows affected’가 출력되었었다)
mysql> SELECT * FROM person;
+----+---------+---------+
| id | name | address |
+----+---------+---------+
| 4 | Cynthia | Seoul |
| 5 | James | Incheon |
+----+---------+---------+
2 rows in set (0.00 sec)
id가 4, 5로 변하였다. 또한 James의 주소가 “Incheon”으로 변경되었다.
이를 ‘2 rows affected’와 함께 종합적으로 판단한다면 “REPLACE INTO”는 다음과 같이 작동하는 것을 알 수 있다.
- 중복 키 오류 발생 시 기존 레코드를 삭제 -> 첫 번째 레코드가 affected되었음
- 이후 새로운 레코드를 입력 -> 두 번째 레코드가 affected되었음
그래서 ‘2 rows affected’가 출력되었다.
새로운 레코드가 입력되면서 AUTO_INCREMENT 컬럼의 값이 매번 새롭게 발급되었다.
“REPLACE INTO”는 그다지 좋은 방법이 아닌데 앞서 이야기한 것처럼 AUTO_INCREMENT는 레코드를 식별할 수 있는 id로 사용되는데 중복 발생 시 id가 변경되기 때문이다.
3-3) ON DUPLICATE UPDATE
ON DUPLICATE UPDATE의 장점은 중복 키 오류 발생 시 사용자가 원하는 값을 직접 설정할 수 있다는 점이다.
우선 기본적인 사용 방법을 보자.
마찬가지로 테이블을 drop후 새로 생성했다.
mysql> INSERT INTO person VALUES (NULL, 'James', 'Seoul')
ON DUPLICATE KEY UPDATE address = VALUES(address);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO person VALUES (NULL, 'Cynthia', 'Yongin')
ON DUPLICATE KEY UPDATE address = VALUES(address);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO person VALUES (NULL, 'James', 'Seongnam')
ON DUPLICATE KEY UPDATE address = VALUES(address);
Query OK, 2 rows affected, 1 warning (0.01 sec)- 다음글utf8mb4_general_ci utf8mb4_unicode_ci 22.04.22
댓글목록
등록된 댓글이 없습니다.