[Mysql] on duplicate key update

INSERT 할 때, 유니크 키에서 발생하는 SQL 오류.

중복방지를 위해 유니크 키를 걸어두는데, 새로운 데이터를 넣어야되는데 유니크 키 때문에 중복에러가 발생하는 경우가 있다. 그렇다고 안 걸기에는 애매하고  ,, 

 

물론 유니크 키에 해당하는 데이터가 있는지 조회하고, 있으면 업데이트 하거나 

/ 데이터가 있는지 조회하고, 삭제했다가 다시 추가하는 방법도 있다.

 

내가 부딪힌 문제는 for문으로 쿼리문을 돌리는데,

조회하고, 있으면 -> update / 없으면 -> insert 하는 과정에서 update 할 때 이미 유니크 키에 해당하는 다른 데이터가 존재해서 update도 안 되는 상황.

 

 

예시를 보면서 얘기하자면,

 

CREATE TABLE li_test_tbl (
	idx INT(10) NOT NULL AUTO_INCREMENT
	, name VARCHAR(20) NOT NULL 
	, active VARCHAR(10)
	, active_no INT(5)
	, PRIMARY KEY(idx)
	, UNIQUE KEY(active, active_no)
);

 

이런 테이블이 있다고 해보자.

 

예를 들면 체육시간에 test1, test2 이라는 사람이 있고 game, music 이란 활동이 있으며 체육시간이 두 파트로, 1번째 파트에 할지, 2번째 파트에 할지 저장한다고 해보자. ( 활동과 파트는 겹칠 수 없도록 유니크 키를 걸었다고 하자 )

 

- name : 이름 ( test1, test2 )

- active : 활동명 ( game, music )

- active_no : 파트시간 ( 1, 2)

 

INSERT INTO li_test_tbl (NAME, active, active_no) VALUE ('test1', 'game', 1);
INSERT INTO li_test_tbl (NAME, active, active_no) VALUE ('test2', 'music', 2);
SELECT * FROM li_test_tbl;

 

test1이 game이란 활동을 1번째 파트에 하기로 했고,

test2가 music이란 활동을 2번째 파트에 하기로 했다.

 

여기서, test1과 test2가 서로 활동을 바꾸고 싶어해서 데이터상 수정해야 된다.

test1 부터 데이터를 수정해주자.

UPDATE li_test_tbl SET active='music', active_no=2 WHERE NAME='test1';

 

" Duplicate entry 'music-2' for key 'active' " 에러가 발생했다. (insert도 같은 에러로 안 된다.)

 

* 여기서 'active'는 내가 테이블을 생성할 때 유니크 키에 관한 유니크 키 이름인데 내가 지정을 안 해주면 알아서 만들어준다. show create table 로 보면 알 수 있음.

SHOW CREATE TABLE li_test_tbl;

-- show create table 명령어로 본 create문
CREATE TABLE `li_test_tbl` (
  `idx` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `active` varchar(10) DEFAULT NULL,
  `active_no` int(5) DEFAULT NULL,
  PRIMARY KEY (`idx`),
  UNIQUE KEY `active` (`active`,`active_no`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

 

이럴 경우, delete 하고 다시 insert 해야되는게 ,, 방법이긴 하겠지만 delete 는 위험성이 있기 때문에 INSERT문에 조건문을 추가하자.

 

INSERT INTO li_test_tbl (NAME, active, active_no) VALUE ('test1', 'music', 2)
ON DUPLICATE KEY UPDATE NAME=VALUES(NAME), active=VALUES(active), active_no=VALUES(active_no);

INSERT INTO li_test_tbl (NAME, active, active_no) VALUE ('test2', 'game', 1)
ON DUPLICATE KEY UPDATE NAME=VALUES(NAME), active=VALUES(active), active_no=VALUES(active_no);

중복되는 키의 데이터가 있으면 무시하고 내가 입력하려던 데이터로 update 시켜주는 걸 볼 수 있다.