[MariaDB/Mysql] 중복되지 않은 데이터 입력하기

개요

특정 테이블에 레코드 입력시, 중복되지 않은 레코드만 입력하고자 하는 경우가 종종 있습니다.
본 문서에서는 MariaDB/Mysql 에서 테이블 내의 데이터와 중복되지 않은 레코드를 입력하는 두 가지 방법에 대하여 살펴 보도록 하겠습니다.

중복되지 않은 데이터 입력하기

특정 테이블에 중복되지 않도록 데이터를 입력하는 방법은 유일키(UNIQUE KEY or PK)가 존재할 경우와 그렇지 않은 경우에 대하여 처리하는 방법이 달라집니다.

UNIQUE KEY가 존재하지 않는 경우

중복을 허용하지 않는 PRIMARY KEY 또는 UNIQUE KEY가 없는 경우에는 다음과 같이 "INSERT ... SELECT ..." 구문을 이용하여 중복 방지된 데이터 입력을 구현할 수 있습니다.

CREATE TABLE IF NOT EXISTS `NO_UNIQUE_KEY`
(
    `seq` INT NOT NULL AUTO_INCREMENT
    , `name` VARCHAR(150) NOT NULL
    , `address` VARCHAR(255) NOT NULL
    , `phone` VARCHAR(30) NOT NULL
    , `reg_date` DATETIME NOT NULL
    , PRIMARY KEY `PK_SEQ` (`seq`)
) ENGINE=INNODB;


-- no duplicate `name` column ; name 컬럼에 색인 생성 권고
INSERT INTO NO_UNIQUE_KEY (`name`, address, phone, reg_date)
SELECT
    *
FROM ( SELECT 'hanwh', 'my address', '0113334444', NOW() ) AS TMP
WHERE 1
    AND NOT EXISTS
        (
            SELECT `name` FROM NO_UNIQUE_KEY WHERE `name` = 'hanwh'
        )
LIMIT 1;

UNIQUE KEY가 존재하는 경우

PRIMARY KEY 또는 UNIQUE KEY가 있는 경우에는 다음과 같이 "INSERT ... ON DUPLICATE KEY UPDATE ..." 구문을 이용하여 중복 방지된 데이터 입력을 구현할 수 있습니다.

CREATE TABLE IF NOT EXISTS `HAS_UNIQUE_KEY`
(
    `site_no`    INT NOT NULL
    , `add_service_type`    SMALLINT UNSIGNED NOT NULL
    , `is_used`    BIT NOT NULL DEFAULT 1
    , `reg_date`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP()
    , `end_date`    DATETIME NULL
    , `mod_date`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP()

    , PRIMARY KEY `PK_SITE_NO_N_TYPE` ( `site_no`, `add_service_type` )
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;


INSERT INTO `HAS_UNIQUE_KEY` ( `site_no`, `add_service_type`, `is_used`, `end_date` )
VALUES ( 2332, 10003, 1, IFNULL('2020-06-03', DATE_ADD(NOW(), INTERVAL 3 MONTH)) )
    ON DUPLICATE KEY UPDATE `end_date` = '2020-06-03', mod_date = NOW();

결론

MariaDB/Mysql 에서 테이블 내의 데이터와 중복되지 않도록 레코드를 입력하는 두 가지 방법에 대하여 살펴 보았습니다.

유일키(UNIQUE KEY / PRIMARY KEY)가 존재하는 경우에는 INSERT ... ON DUPLICATE KEY UPDATE ..." 구문을 이용하고, 없는 경우에는 "INSERT ... SELECT ..." 구문을 이용하여 테이블 내의 데이터와 중복되지 않는 레코드 입력을 처리하여 보았습니다.