2021-12-14 15:22 작성

My SQL basic 메모

Table of contents

MySQL INSERT IGNORE 구문 소개

임의의 table에 복수의 rows를 추가하는 INSERT 구문을 사용할 때 그리고 처리 중 에러가 발생하면 MySQL은 구문을 종료하고 에러를 반환한다. 결과적으로 어떤 rows도 table에 삽입되지 않는다.

그러나 만약 INSERT IGNORE 구문을 사용하면 에러를 일으킬 수 있는 무효한 데이터를 가지고 있는 rows는 무시되고(ignored) 유효한 데이터를 가진 rows는 table에 삽입된다.

INSERT IGNORE 구문의 기본 문법은 다음과 같다:

INSERT IGNORE INTO table(column_list)
VALUES
    (value_list),
    (value_list),
    ...

증명을 위해 subscribers라는 명칭의 table을 생성한다.

CREATE TABLE subscribers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(50) NOT NULL UNIQUE
);

UNIQUE constraint(제한)는 email column에서 중복되지 않은 email만 존재하도록 보장한다.

다음의 구문은 subscribers table에 하나의 새로운 row를 삽입한다:

INSERT INTO subscribers(email)
VALUES('john.doe@gmail.com');

subscribers table에 두 개의 rows를 삽입하는 다른 구문을 실행해보자:

INSERT INTO subscribers(email)
VALUES('john.doe@gmail.com'),
    ('jane.smith@ibm.com');

다음과 같은 에러가 발생한다.

ERROR 1062 (23000): Duplicate entry 'john.doe@gmail.com' for key 'subscribers.email'

에러 메시지에서 안내했듯이 email john.doe@gmail.com이 UNIQUE 제한을 침해한다.

그러나 만약 INSERT IGNORE 구문을 대신 사용한다면:

INSERT IGNORE INTO subscribers(email)
VALUES
    ('john.doe@gmail.com'),
    ('jane.smith@ibm.com');

다음과 같은 메시지를 출력한다.

Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 2  Duplicates: 1  Warnings: 1

구체적인 warning의 원인을 찾기 위해 SHOW WARNINGS 커맨드를 사용할 수 있다:

SHOW WARNINGS;

output:

Level	Code	Message
Warning	1062	Duplicate entry ‘john.doe@gmail.com’ for key ‘subscribers.email’

결론적으로 INSERT IGNORE 구문을 사용할 때 에러가 발생되어야 할 상황에서 에러가 아닌 wanring이 발생한다.

만약 subscribers table의 데이터를 쿼리하면, 에러를 발생시키는 row를 제외한 row만 삽입된 것을 알 수 있다.

output:

id	email
4	jane.smith@ibm.com
1	john.doe@gmail.com

strict mode가 켜져 있을 때, 유효하지 않은 values를 임의의 table에 삽입하려고 하면 MySQL은 에러를 반환하고 INSERT 구문을 중단시킨다.

그러나 만약 INSERT IGNORE 구문을 사용한다면, MySQL은 에러 대신 warning을 발생시킬 것이다. 뿐만 아니라, value를 table에 더하기 전에 values가 유효한 상태가 되도록 조정할 것이다.

다음의 예시를 고려해보자.

첫째, tokens라는 명칭의 새로운 table을 생성한다:

CREATE TABLE tokens (
    s VARCHAR(6)
);

이 table에서 s column은 길이가 6 이하인 string만 수용한다.

둘째, tokens table에 길이가 7인 string을 삽입한다.

INSERT INTO tokens VALUES('abcdefg');

strict mode가 켜져 있으므로 MySQL은 다음의 에러를 출력한다.

ERROR 1406 (22001): Data too long for column 's' at row 1

셋째, INSERT IGNORE 구문과 함께 똑같은 string을 삽입한다.

INSERT IGNORE INTO tokens VALUES('abcdefg');

MySQL은 tokens table에 데이터를 삽입하기 전에 데이터의 끝을 자른다. 그리고 warning을 발생시킨다.

Level	Code	Message
Warning	1265	Data truncated for column ‘s’ at row 1

output:

s
abcdef