[MySQL] 중복 키 관리 방법

▶MySQL 중복 키 관리 방법

---

▶설명

---

MySQL에서 중복 키 관리 방법은 총 3가지가 있습니다.

1. INSERT IGNORE
2. REPLACE INTO ...
3. INSERT INTO ... ON DUPLICATE

자주 까먹는 부분이라서 한 번 정리해보기로 했습니다.

출처 : http://jason-heo.github.io/mysql/2014/03/05/manage-dup-key2.html

▶예제 테이블 (heroes)

---

idx	name
1	이순신
2	안중근

CREATE TABLE `heroes` (
  `idx` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`idx`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT  INTO `heroes`(`idx`,`name`) VALUES (1,'이순신'), (2,'안중근');

▶INSERT IGNORE

---

INSERT IGNORE는 키 중복이 발생하는 경우 추가하지 않고, 키 중복이 발생하지 않은 경우에만 추가합니다.

사용법

INSERT IGNORE INTO 테이블명 [컬럼1, 컬럼2, ...] VALUES (값1, 값2, ...);

예제 : 중복 데이터가 있는 경우

쿼리

INSERT IGNORE INTO heroes (name) VALUES ('이순신'),('윤봉길');

결과

idx	name
1	이순신
2	안중근
3	윤봉길

총 1건이 적용된 것을 알 수 있습니다.

• idx 3번 추가 (윤봉길)

▶REPLACE INTO

---

REPLACE INTO는 키 중복이 발생하는 경우 중복이 발생한 열을 삭제하고 새로 입력합니다.

사용법

REPLACE INTO 테이블명 [컬럼1, 컬럼2, ...] VALUES (값1, 값2, ...);

예제 : 중복 데이터가 있는 경우

쿼리

REPLACE INTO heroes (name) VALUES ('이순신'),('윤봉길');

결과

idx	name
2	안중근
3	이순신
4	윤봉길

총 3건이 적용된 것을 알 수 있습니다.

• idx 1번 삭제 (이순신)
• idx 3번 추가 (이순신)
• idx 4번 추가 (윤봉길)

▶INSERT INTO ... ON DUPLICATE UPDATE

---

INSERT INTO ... ON DUPLICATE UPDATE는 키 중복이 발생하는 경우 추가 대신에 수정할 값을 지정할 수 있습니다.

INSERT INTO ... ON DUPLICATE UPDATE는 중복이 발생하지 않는 경우 1건 적용, 중복이 발생한 경우 2건 적용, 값이 변화가 없으면 0건으로 반환됩니다.

중복이 발생한 경우에는 INSERT를 시도하고 UPDATE를 진행하기 때문에 AUTO_INCREMENT 값은 1이 증가하게 됩니다.

INSERT INTO ... ON DUPLICATE UPDATE는 UPDATE하고자 하는 값을 항상 VALUES(컬럼)으로 작성해줘야 합니다. VALUES() 없이 사용하면 기존에 존재하는 레코드의 컬럼 값을 UPDATE하게 됩니다.

사용법

INSERT INTO 테이블명
    [컬럼1, 컬럼2, ...]
VALUES
    (값1, 값2, ...)
ON DUPLICATE KEY UPDATE
    [컬럼1] = VALUES(값1), ...;

예제 : 중복 데이터가 있는 경우

쿼리

INSERT INTO heroes
    (name)
VALUES
    ('이순신'),('윤봉길')
ON DUPLICATE KEY UPDATE
    name = CONCAT(VALUES(name), idx);

결과

idx	name
1	이순신1
2	안중근
3	윤봉길

총 3건이 적용된 것을 알 수 있습니다.

• idx 1번 INSERT 시도 후 이미 값이 존재하는 것을 확인하여 UPDATE / 2건
• idx 3번 추가 / 1건

▶요약

---

분류	특징
INSERT IGNORE	기존 레코드가 남아 있음 기존 레코드의 AUTO_INCREMENT 값은 변하지 않음
REPLACE INTO	기존 레코드가 삭제되고, 신규 레코드가 INSERT됨 따라서 AUTO_INCREMENT의 값이 변경됨
INSERT INTO ... ON DUPLICATE UPDATE	INSERT IGNORE의 장점 포함함 중복 키 오류 발생 시, 사용자가 UPDATE 할 값을 지정할 수 있음