블로그 통계에서 제일 조회수가 많은 것들은 모두 가장 기본적인 부분이다.
그래서 오늘은 기초적이면서도 헷갈리는 내용에 대해서 작성해볼까 하는데 바로 INSERT / UPDATE와 관련된 내용이다. 분명 언젠가 INSERT와 관련된 내용을 포스팅했던 기억이 나는데, 아니나 다를까 찾아보니 INSERT 구문에 대해 mysql document를 보면서 정리해놓은 내용이 있더라...
포스팅에 부지런했던 나의 과거 ^_ㅜ
2011/07/07 - [DataBase/MySQL] - DML(Data Manipulation Language) - INSERT 사용에 대해서
DML(Data Manipulation Language) - INSERT 사용에 대해서
데이터베이스를 사용할 때 가장 많이 사용하게 되는 DML 문에 대해서 하나씩 정리해보려고 합니다. 우선 가장 기본적으로 테이블에 데이터가 있어야 뭐든 가능하므로 데이터를 입력하는 INSERT 구문에 대해서 알아..
ojava.tistory.com
오늘은 INSERT 시 Duplicate entry ~~~~ for key 'PRIMARY' 등의 에러메시지를 자주 마주한 사람들에게 제시할 수 있는 대안인 INSERT INTO ON DUPLICATE KEY UPDATE 구문에 대해 알아보자.
INSERT 시 Duplicate entry 에러가 발생한다면?
: INSERT INTO ON DUPLICATE KEY UPDATE
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException : Duplicate entry '~~~~' for key 'PRIMARY'
이런 에러를 한 번이라도 만나봤다면 해당 코드는 분명 해당 조건값에 count를 확인한 뒤에 없으면 INSERT하고 있으면 UPDATE 하도록 구문을 따로 등록해놓은 프로세스일거다.
대부분 더블 클릭 등의 사유로 인해 중복으로 프로세스가 처리되어 insert가 일어난 뒤 또다시 insert를 처리하려고 해서 중복된 key값을 생성하려고 시도해서 에러가 나는 경우가 많다.
이런 경우, 조건에 따른 SELECT 문을 통해 COUNT 결과값을 확인한 후 INSERT 혹은 UPDATE로 분기해놓은 부분을 INSERT INTO ON DUPLICATION KEY UPDATE로 묶어버리면 어떨까 하는 생각을 했다.
이 두 방식의 성능 차이에 대한 궁금증을 안고 실험해본 포스팅을 보고 기존의 내용을 변경하기로 마음 먹었다.
AS-IS
- 조건절을 포함한 SELECT 문을 통해 결과값 조회
- 결과 여부에 따라 INSERT 또는 UPDATE 구문 실행
>> 최소 두 번의 쿼리문을 수행해야 함
- 단, 시간 차이 또는 짧은 시간 내 같은 프로세스 중복 발생하는 경우 오류 발생
TO-BE
- 원하는 INSERT/UPDATE 구문을 하나로 합쳐서 수행
>> 최대 한 번만 쿼리문을 수행함
- SELECT 구문과 분기처리 조건 모두 수행하지 않으며, 중복키 INSERT에 따른 에러 발생 가능성 없음
INSERT INTO ON DUPLICATE KEY UPDATE 구문 구조를 살펴보자.
|
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] |
위 구문 이외에도 INSERT SELECT 구문인 경우에도 ON DUPLICATE KEY UPDATE 절을 사용할 수 있는 듯 하다.
- UNIQUE INDEX 혹은 PRIMARY KEY 값에 중복이 발생하는 경우만 수행됨!!
- UPDATE 절에서 VALUES(컬럼명) 을 사용해서 INSERT 절에 사용했던 값을 참조할 수 있다.
여러개의 값을 한 번에 INSERT UPDATE 하는 경우에 유용하게 사용될 수 있으며, 해당 VALUES() 함수는 ON DUPLICATE KEY UPDATE 절 또는 INSERT 문에서만 의미가 있고, 다른 곳에서 사용하면 NULL을 반환한다.
- MariaDB 10.3.2 버전까지는 VALUES() 로 쓰였지만, 10.3.3 버전 이후부터는 VALUE() 로 사용된다.
현재 문제가 되는 부분에 대해 해당 구문으로 변경 적용할 예정이다.
담당하던 업무를 넘길때가 되니까 간헐적으로 발생하는 에러들이 신경쓰인다. 다음 사람을 위해서라도 지금 다 잡아놔야 마음 편히 떠날 수 있을 듯 하다.
* 포스팅 참조 내용
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
MySQL :: MySQL 8.0 Reference Manual :: 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax
13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs. For example, if column a is declar
dev.mysql.com
https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/
INSERT ON DUPLICATE KEY UPDATE
INSERT if no duplicate key is found, otherwise UPDATE
mariadb.com
UPDATE VS INSERT ON DUPLICATE KEY UPDATE
지금껏 단순하게 update 가 insert on duplicate key update 구문보다 더 빠를 것이라 생각했습니다. 물론 update의 조건절(where)에는 pk가 주어지는 상태일 경우를 말합니다. 테이블 구조는 CREATE TABLE egg_ta..
egg.pe.kr
'DataBase > MySQL, MariaDB' 카테고리의 다른 글
| [AWS] ubuntu에서 MariaDB 설치 및 기본 설정하기 (1) | 2021.05.14 |
|---|---|
| Commons DBCP 설정값 알아보기 (0) | 2018.08.06 |
| DB 전체에서 특정 필드를 찾아야 할 때 (0) | 2017.11.29 |
| SELECT JOIN 문을 이용한 UPDATE QUERY (0) | 2017.03.30 |
| DML(Data Manipulation Language) - INSERT 사용에 대해서 (1) | 2011.07.07 |
