insert into on duplicate key MySQL merge SQL 독학 강의#20편 -sTricky

insert into on duplicate key MySQL merge SQL 독학 강의#20편 -sTricky

 

 

콘텐츠 index 0. insert into on duplicate key 문이란? 1. 테스트용 데이터 입력 2. insert into on duplicate key 문 사용 예제 3. insert into on duplicate key 문 사용 예제 2

## 전편 강의 보러 가기 ##

https://stricky.tistory.com/282

mysql delete sql 독학 강의#19편 -sTricky

안녕하세요.

 

어느덧 SQL 독학 강의도 20편이네요..ㅎㅎ

 

오늘 포스팅할 내용은 merge문입니다.

 

orcle에서는 merge문이라고 부르지만, MySQL에서는 해당 구문을 다른 방식으로 실행해야 합니다.

 

천천히 하나씩 예제를 통해서 해당 구문에 대해서 포스팅을 해드리도록 하겠습니다.

 

0. insert into on duplicate key 문이란?

insert into on duplicate key란, 위에서도 말씀드렸다시피 oracle에서의 merge문과 같은 기능을 가지고 있습니다.

 

어떤 데이터를 입력을 하는데, 대상 테이블에 해당 키에 해당하는 데이터가 없으면 insert문을 실행하여 입력을 하고, 해당 키가 이미 대상 테이블에 있는 경우에는 다른 칼럼들을 update 하여 값을 경신하겠다는 의미입니다.

위 그림을 보면 <입력대상 테이블>이 있고, case 1, case 2에서 각각 "대한민국"이라는 데이터를 넣는데, case 1 에서는 해당 데이터의 key값이 "6"이고, case 2에서는 해당 데이터의 key값이 "3"입니다.

 

이런 경우에 insert into on duplicate key로 입력을 하게 되면 case 1의 경우에서는 <"6", "대한민국"> 데이터가 insert가 되고, case 2의 경우에는 이미 입력대상 테이블에 key값인 3이 있기 때문에 3에 "대한민국" 데이터가 update 되게 됩니다.

 

1. 테스트용 데이터 입력

이해되시나요? 자, 그럼 아래와 같이 테이블을 생성하고, 데이터를 입력한 다음 실제로 insert into on duplicate key 구문을 이용해서 어떻게 데이터가 insert, update 되는지 확인해보도록 하겠습니다.

create table class.insert_test
(
	seq int(10) not null
		primary key,
	cont text null,
	name varchar(15) null,
	tel_num int null,
	input_date datetime null
);

INSERT INTO class.insert_test (seq, cont, name, tel_num, input_date) VALUES (1, '대한민국은 코로나를 잘 극복 하고 있습니다.', '홍길동', 1012345678, '2020-05-15 14:35:10');
INSERT INTO class.insert_test (seq, cont, name, tel_num, input_date) VALUES (2, '대한민국은 코로나를 잘 극복 하고 있습니다.', '홍길동', 1012345678, '2020-05-15 14:35:10');
INSERT INTO class.insert_test (seq, cont, name, tel_num, input_date) VALUES (3, '대한민국은 코로나를 잘 극복 하고 있습니다.', '홍길동', 1012345678, '2020-05-15 14:35:10');
INSERT INTO class.insert_test (seq, cont, name, tel_num, input_date) VALUES (4, '대한민국은 코로나를 잘 극복 하고 있습니다.', '홍길동', 1012345678, '2020-05-15 14:35:10');
INSERT INTO class.insert_test (seq, cont, name, tel_num, input_date) VALUES (5, '대한민국은 코로나를 잘 극복 하고 있습니다.', '홍길동', 1012345678, '2020-05-15 14:35:10');


create table class.insert_test2
(
	seq int(10) not null,
	cont text null,
	name varchar(15) null,
	tel_num int null,
	input_date datetime null
);


INSERT INTO class.insert_test2 (seq, cont, name, tel_num, input_date) VALUES (4, '사회적 거리두기를 잘 실천 합시다!', '손흥민', 1012345678, '2020-02-01 12:32:22');
INSERT INTO class.insert_test2 (seq, cont, name, tel_num, input_date) VALUES (5, '사회적 거리두기를 잘 실천 합시다!', '손흥민', 1012345678, '2020-02-01 12:32:22');
INSERT INTO class.insert_test2 (seq, cont, name, tel_num, input_date) VALUES (6, '사회적 거리두기를 잘 실천 합시다!', '손흥민', 1012345678, '2020-02-01 12:32:22');
INSERT INTO class.insert_test2 (seq, cont, name, tel_num, input_date) VALUES (7, '사회적 거리두기를 잘 실천 합시다!', '손흥민', 1012345678, '2020-02-01 12:32:22');

자, 그럼 위 테이블을 select 해서 데이터가 잘 입력되었는지 확인해 보겠습니다.

<insert_test>

<insert_test2>

이렇게 확인이 되었으면, 이젠 insert into on duplicate key 구문을 이용해서 <insert_test2>의 데이터를 <insert_test>으로 insert 하도록 하겠습니다.

 

insert into on duplicate key SQL문을 작성하고 실행하기 전에 미리 결과를 예상해보면, <insert_test> 테이블의 key 칼럼은 seq입니다. <insert_test2>에는 seq 칼럼에 4,5,6,7 값이 존재하는데 <insert_test> 테이블의 seq에는 1,2,3,4,5의 데이터가 있습니다.

 

즉, <insert_test2>에서 seq값이 4,5인 것은 <insert_test> 테이블로 insert into on duplicate key 될 때 insert가 아니라 update가 될 것이며, <insert_test> 테이블에 없는 seq값인 6,7은 insert 될 것으로 예상됩니다.

 

2. insert into on duplicate key 문 사용 예제

insert into insert_test
select *
from insert_test2 b
on duplicate key update cont       = b.cont,
                        name       = b.name,
                        tel_num    = b.tel_num,
                        input_date = now();

insert into on duplicate key 문은 위 SQL문을 참고로 해서 작성하시면 됩니다. 이미 insert문의 개념을 잘 파악하고 계시다면 어렵지 않으실 겁니다.

 

간단하게 예제에 쓰인 문장을 해설해드리면, 우선 기본적으로 <insert_test> 테이블에 <insert_test2> 테이블의 데이터를 select 해서 넣는 구문입니다. 4번째 줄을 보면 on duplicate key update문이 있습니다. 이 구문의 뜻은 만약 key가 on duplicate 즉, 중복된다면 update 하라는 의미입니다. 무엇을 update 하느냐, 바로 뒤에 따라오는 내용들이 update 됩니다.

cont = b.cont,
name = b.name,
tel_num = b.tel_num,
input_date = now();

<insert_test> 테이블의 cont 칼럼에 b.cont, 즉 <insert_test2> 테이블의 cont값을 update 하라는 의미가 되는 거죠, 나머지 name, tel_num, input_date 역시 마찬가지로 update 하게 됩니다.

 

그럼 위에서 작성한 insert into on duplicate key 문을 실행한 결과를 보겠습니다.

위 결과를 보시면 빨간 박스 안에 있는 데이터에 변화가 생긴 것을 표시했고요, 그중에 파란 박스 안에 내용(seq가 4,5인 것)은 update가 될 내용임을 확인할 수 있습니다.

 

3. insert into on duplicate key 문 사용 예제 2

이번에는 insert into on duplicate key 문에 위와 같이 select 문을 사용하는 것이 아니라, 데이터를 넣는 경우의 예제를 작성해보겠습니다.

insert into insert_test
values (8,'사회적 거리두기를 실천 합시다.', '손흥민', 1012345678, now())
on duplicate key update cont = '사회적 거리두기를 실천 합시다.',
                        name = '손흥민',
                        tel_num = 1012345678,
                        input_date = now();

 위와 같이 insert into on duplicate key 문을 실행하게 되면 어떻게 될까요? key값인 seq칼럼의 데이터가 8입니다. 지금 시점에서 <insert_test> 테이블에는 seq가 8인 데이터가 없기 때문에 insert가 될 것입니다. <insert_test> 테이블을 확인해 보겠습니다.

seq가 8인 데이터가 입력된 것을 확인했습니다.

 

이번에는 아래와 같이 insert into on duplicate key 문을 작성해서 실행해 보겠습니다.

insert into insert_test
values (2,'사회적 거리두기를 실천 합시다.', '손흥민', 1012345678, now())
on duplicate key update cont = '사회적 거리두기를 실천 합시다.',
                        name = '손흥민',
                        tel_num = 1012345678,
                        input_date = now();

이번에는 seq값이 2입니다. <insert_test> 테이블에 seq가 2인 값이 있기 때문에 update가 될 것입니다. <insert_test> 테이블을 확인해 보겠습니다.

update가 된 것을 확인할 수 있습니다.

 

insert into on duplicate key 문에 대한 내용, 여기서 줄이도록 하겠습니다.

 

포스팅 내용에 관련하여 질의사항이 있으신 분은 댓글로 부탁드리겠습니다.

 

open.kakao.com/o/szfhqYec

Database/남/db개발님의 오픈프로필

---

## 다음 강의 보러 가기 ##

2020/06/12 - [Database/sql 강의] - MySQL DDL문 완전정복 SQL 독학 강의#21편 -sTricky

MySQL DDL문 완전정복 SQL 독학 강의#21편 -sTricky

 

 

insert into on duplicate key 문은 DB를 이용한 프로그램을 만들 때 매우 유용하게 쓰일 수 있습니다. 굳이 update 할지 insert 할지 기존 데이터를 확인하지 않더라도 하나의 SQL문으로 처리할 수 있으닌깐요.

 

이 내용을 잘 이해하셔서, 유용하게 잘 쓰일 수 있길 바랍니다.

 

감사합니다.

 

 

 

by.sTricky