MySQL ON DUPLICATE KEY (insert 기존에 있으면 update )

 

ON DUPLICATE KEY는 기존 데이터는 UPDATE하고 신규 데이터는 INSERT 하는 기능을 제공합니다.

 

주로 저는 배치로 많은 데이터? 등록 수정할때 사용합니다.

 

1. 테스트 테이블 생성 DDL 

    - 회원 테이블 간단하게 만든다고 만들었는데 .. 

CREATE TABLE `MB_MEMBER_INFO` (
  `MEMBER_ID_SQ` INTEGER AUTO_INCREMENT COMMENT '회원ID일련번호',
  `MEMBER_EMAIL_ID` varchar(250) UNIQUE NOT NULL COMMENT '회원이메일ID',
  `MEMBER_PW` varchar(256) NOT NULL COMMENT '회원비밀번호',
  `MEMBER_NM` varchar(50) NOT NULL COMMENT '회원명',
  `SEX_DS` varchar(8) DEFAULT NULL COMMENT '성별',
  `BIRTH_DS` varchar(8) DEFAULT NULL COMMENT '생년월일',
  `MOBILE_NO` varchar(20) NOT NULL COMMENT '휴대폰번호',
  `POST_NO` varchar(8) DEFAULT NULL COMMENT '회원주소_우편번호',
  `ADDRESS_DS` varchar(256) DEFAULT NULL COMMENT '회원주소_기본',
  `ADDRESS_DTL_DS` varchar(256) DEFAULT NULL COMMENT '회원주소_상세',
  `INSERT_DT` datetime NOT NULL COMMENT '등록일시',
  `INSERT_ID` varchar(64) NOT NULL COMMENT '등록자ID',
  PRIMARY KEY (`MEMBER_ID_SQ`)
) ENGINE = INNODB AUTO_INCREMENT=1000000001 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = '회원정보';

 

간단한 설명

- UNIGUE : 추후 고정 변경되지 않을 컬럼 ON DUPLICATE KEY 했을시 기준

- AUTO_INCREMENT : 자동 증가 1씩 자동 증가합니다.

- AUTO_INCREMENT=1000000001 : 1000000001 부터 증가 함 초기값 지정

 

2. 테스트 데이터 입력

INSERT INTO MB_MEMBER_INFO(
MEMBER_EMAIL_ID,MEMBER_PW,MEMBER_NM,SEX_DS,BIRTH_DS,MOBILE_NO,POST_NO,ADDRESS_DS,ADDRESS_DTL_DS,INSERT_DT,INSERT_ID
)VALUES(
'ho1@naver.com','ho1','ho1Test01','M','20240228','',NULL,NULL,NULL,NOW(),'ho1Test01');
	
INSERT INTO MB_MEMBER_INFO(
MEMBER_EMAIL_ID,MEMBER_PW,MEMBER_NM,SEX_DS,BIRTH_DS,MOBILE_NO,POST_NO,ADDRESS_DS,ADDRESS_DTL_DS,INSERT_DT,INSERT_ID
)VALUES(
'ho2@naver.com','ho2','ho1Test02','M','20240228','',NULL,NULL,NULL,NOW(),'ho1Test02');

INSERT INTO MB_MEMBER_INFO(
MEMBER_EMAIL_ID,MEMBER_PW,MEMBER_NM,SEX_DS,BIRTH_DS,MOBILE_NO,POST_NO,ADDRESS_DS,ADDRESS_DTL_DS,INSERT_DT,INSERT_ID
)VALUES(
'ho3@naver.com','ho3','ho1Test03','M','20240228','',NULL,NULL,NULL,NOW(),'ho1Test03');

 

 

데이터 확인

 

3. ON DUPLICATE KEY 데이터 입력 / 수정

업데이트 할 데이터 UNIGUE MEMBER_EMAIL_ID 값이 동일하게 있는걸 확인할수 있습니다.

-- 업데이트 할 기존 데이터 
INSERT INTO MB_MEMBER_INFO ( 
	MEMBER_EMAIL_ID,MEMBER_PW,MEMBER_NM,SEX_DS,BIRTH_DS,MOBILE_NO,POST_NO,ADDRESS_DS,ADDRESS_DTL_DS,INSERT_DT,INSERT_ID
) VALUES (
	 'ho3@naver.com','ho4','ho1Test04','M','20240228','',NULL,NULL,NULL,NOW(),'ho1Test04')
	 ON DUPLICATE KEY UPDATE 
	 MEMBER_EMAIL_ID  = VALUES(MEMBER_EMAIL_ID), 
	 MEMBER_PW = VALUES(MEMBER_PW), 
	 MEMBER_NM = VALUES(MEMBER_NM),
	 SEX_DS = VALUES(SEX_DS),
	 BIRTH_DS = VALUES(BIRTH_DS),
	 MOBILE_NO = VALUES(MOBILE_NO),
	 POST_NO = VALUES(POST_NO),
	 ADDRESS_DS = VALUES(ADDRESS_DS),
	 ADDRESS_DTL_DS = VALUES(ADDRESS_DTL_DS),
	 INSERT_DT = VALUES(INSERT_DT),
	 INSERT_ID = VALUES(INSERT_ID)
;

등록할 데이터

 -- 신규 데이터
  INSERT INTO MB_MEMBER_INFO (
  	MEMBER_EMAIL_ID,MEMBER_PW,MEMBER_NM,SEX_DS,BIRTH_DS,MOBILE_NO,POST_NO,ADDRESS_DS,ADDRESS_DTL_DS,INSERT_DT,INSERT_ID
  ) VALUES ( 'ho5@naver.com','ho5','ho1Test05','M','20240228','',NULL,NULL,NULL,NOW(),'ho1Test05')
  ON DUPLICATE KEY UPDATE 
  	 MEMBER_EMAIL_ID  = VALUES(MEMBER_EMAIL_ID), 
	 MEMBER_PW = VALUES(MEMBER_PW), 
	 MEMBER_NM = VALUES(MEMBER_NM),
	 SEX_DS = VALUES(SEX_DS),
	 BIRTH_DS = VALUES(BIRTH_DS),
	 MOBILE_NO = VALUES(MOBILE_NO),
	 POST_NO = VALUES(POST_NO),
	 ADDRESS_DS = VALUES(ADDRESS_DS),
	 ADDRESS_DTL_DS = VALUES(ADDRESS_DTL_DS),
	 INSERT_DT = VALUES(INSERT_DT),
	 INSERT_ID = VALUES(INSERT_ID)
	 ;

 

확인 

빨간색이 수정 

흰색이 추가 입니다.

 

보시면 MEMBER_ID_SQ가 1000000003 다음 1000000005가 된것을 볼수있습니다.

 

ON DUPLICATE KEY 작동 방식을 보면 알수있습니다.

1. INSERT 실행

2. UNIGUE 값 중복 확인

3. 중복값이 있으면 UPDATE

4. 처음 INSERT 한 UNIGUE 중복데이터 DELETE 

 

이처럼 처음 INSERT 하기떄문에 키 값이 증가하는걸 확인할수있습니다.