MySQL 중복 레코드 관리 방법

1. 개요

MySQL에는 아래 3가지 방법을 이용하여 중복 레코드를 관리할 수 있다.

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

각 방법의 특징을 요약하면 다음과 같다.

INSERT IGNORE ...	최초 등록된 레코드가 남아 있음 최초 등록된 레코드의 AUTO_INCREMENT 값은 변하지 않음
REPLACE INTO ...	최초 등록된 레코드가 삭제되고, 신규 레코드가 INSERT됨 AUTO_INCREMENT의 값이 변경됨
INSERT INTO ... ON DUPLICATE UPDATE	INSERT IGNORE의 장점 포함함 중복 키 오류 발생 시, 사용자가 UPDATE될 값을 지정할 수 있음

방법 특징

2. 사전 조건 및 중복 처리 방법

중복 레코드 관리를 위해선 테이블에 UNIQUE INDEX가 필요하다.

자동으로 증가하는 Primary Key 로 중복관리를 하는 것은 쉽지 않다.

중복 레코드 관리를 위한 KEY가 2개의 칼럼 조합일 수도 있다.

 

create database androidsample default character set utf8 COLLATE utf8_general_ci;
 
CREATE TABLE IF NOT EXISTS person (
  id int(11) NOT NULL,
  name varchar(20) DEFAULT NULL,
  address varchar(150) DEFAULT NULL,
  cnt int(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE INDEX (name) -- 중복 검사용 필드
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
 
-- INSERT IGNORE INTO
-- INSERT IGNORE는 중복 키 에러가 발생했을 때 신규로 입력되는 레코드를 무시
INSERT IGNORE INTO person VALUES (NULL, '홍길동', '서울',0);
INSERT IGNORE INTO person VALUES (NULL, '신시아', '용인',0);
INSERT IGNORE INTO person VALUES (NULL, '홍길동', '평택',1);
INSERT IGNORE INTO person VALUES (NULL, '신시아', '서울',1);
INSERT IGNORE INTO person VALUES (NULL, '홍길동', '인천',2);
 
 
-- INSERT INTO ON DUPLICATE KEY UPDATE
INSERT INTO person VALUES (NULL, '강감찬', '대전', 3)
           ON DUPLICATE KEY UPDATE address = VALUES(address), cnt=VALUES(cnt);
 
INSERT INTO person VALUES (NULL, '홍길동', '인천', 5)
           ON DUPLICATE KEY UPDATE address = VALUES(address), cnt=VALUES(cnt);
 
INSERT INTO person(name,cnt) VALUES ('신시아', 8)
           ON DUPLICATE KEY UPDATE cnt=VALUES(cnt);
 
INSERT INTO person VALUES (NULL, '이순신', '충청', 9)
           ON DUPLICATE KEY UPDATE address = VALUES(address), cnt=VALUES(cnt);
 
-- 테이블 비우기
TRUNCATE person;
 
INSERT IGNORE INTO person VALUES (NULL, '홍길동', '서울',0);
INSERT IGNORE INTO person VALUES (NULL, '신시아', '용인',0);
INSERT IGNORE INTO person VALUES (NULL, '홍길동', '평택',1);
INSERT IGNORE INTO person VALUES (NULL, '신시아', '서울',1);
INSERT IGNORE INTO person VALUES (NULL, '홍길동', '인천',2);
 
-- REPLACE INTO
-- REPLACE INTO는 중복이 발생되었을 때 기존 레코드를 삭제하고 신규 레코드를 INSERT하는 방식
REPLACE INTO person VALUES (NULL, '홍길동', '서울',0);
REPLACE INTO person VALUES (NULL, '신시아', '용인',0);
REPLACE INTO person VALUES (NULL, '홍길동', '평택',1);
REPLACE INTO person VALUES (NULL, '신시아', '서울',1);
REPLACE INTO person VALUES (NULL, '홍길동', '인천',2);
 

 

UPDATE하고자 할 때는 항상 column=VALUES(column)와 같이 적어줘야 한다는 점이다.

 

두가지 케이스로 테스트를 해보면서, id 값이 어떻게 변경되는지 확인해 보시라.

 

Primary Key 값의 변동없이 Insert INTO, Update를 처리하려면 PHP 등과 같은 별도의 백엔드 언어를 통해서 처리하는 코드를 구현해야 한다.