[MySQL] ON DUPLICATE KEY UPDATE 사용하기[MySQL] ON DUPLICATE KEY UPDATE 사용하기
Posted at 2020. 5. 14. 17:13 | Posted in MySQL/MySQL참고 : https://sdevstudy.tistory.com/12
■ ON DUPLICATE KEY UPDATE 사용하기
MySQL에서는 대량의 파일을 등록 및 수정하는경우
기존 데이터는 UPDATE하고 신규 데이터는 INSERT 시키는 ON DUPLICATE KEY UPDATE 기능을 제공한다.
해당 기능을 잘 사용하게 되면 대량의 데이터 처리하는데 많은 도움이 된다.
먼저 ON DUPLICATE KEY UPDATE 기능을 알아볼 테스트 테이블을 하나 생성해 보자.
# 테이블 생성
CREATE TABLE onepiece ( seq_num INT( 11 ) NOT NULL AUTO_INCREMENT , name VARCHAR( 20 ) NOT NULL , age INT( 2 ) NOT NULL , stature INT( 3 ) DEFAULT NULL , prize_money VARCHAR( 20 ) DEFAULT NULL , blood VARCHAR( 2 ) DEFAULT NULL , PRIMARY KEY ( seq_num ) ) ENGINE=INNODB DEFAULT CHARSET=utf8 |
테이블이 정상적으로 생성되었다면 초기값을 먼저 세팅해 보자.
# 데이터 입력 - 초기값 설정
INSERT INTO onepiece (name, age, stature, prize_money, blood) VALUE ('Monkey D. Luffy', '17', '172', '3억', 'F'); INSERT INTO onepiece (name, age, stature, prize_money, blood) VALUE ('Roronoa Zoro', '19', '178', '1억 2천만', 'XF'); INSERT INTO onepiece (name, age, stature, prize_money, blood) VALUE ('Sanji', '19', '177', '7천 7백만', 'S'); |
초기값을 INSERT 하였다면 SELECT 하여 내용을 한번 확인해 보자.
# 데이터 출력 - 입력내역 확인
SELECT seq_num, name, age, stature, prize_money, blood FROM onepiece |
이제 해당 샘플 테이블에서 절대로 변경되지 않을 name 컬럼을
UNIQUE KEY로 지정해야 한다.
# 테이블 변경 - UNIQUE KEY 적용
ALTER TABLE onepiece ADD UNIQUE ( name ); |
UNIQUE KEY 지정이 완료되었다면
본격적으로 ON DUPLICATE KEY UPDATE 사용하였을때 어떤 결과를 얻을 수 있는지
새로운 데이터를 INSERT 하여 확인해보자.
# 데이터 입력 - 변경 및 추가 데이터 입력
-- 업데이트 할 기존 데이터 INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Monkey D. Luffy', '19', '174', '15억', 'F' ) ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money ); INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Roronoa Zoro', '21', '181', '3억 2천만', 'XF' ) ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money ); INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Sanji', '21', '180', '3억 3천만', 'S' ) ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money ); -- 신규 데이터 INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Usopp', '19', '176', '2억', 'S' ) ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money ); INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Nami', '20', '170', '6천 6백만', 'X' ) ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money ); INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Tony Tony Chopper', '17', '90', '백', 'X' ) ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money ); INSERT INTO onepiece ( name, age, stature, prize_money, blood ) VALUE ( 'Nico Robin', '30', '188', '1억 3백', 'S' ) ON DUPLICATE KEY UPDATE age = age + 2, stature = VALUES( stature ), prize_money = VALUES( prize_money ); |
ON DUPLICATE KEY UPDATE를 사용하는 기존 데이터와 신규 데이터의 입력이 완료되었다면
다시 SELECT 문으로 테스트 테이블의 내용을 확인해 보자.
# 데이터 출력 - 변경 내역 확인
SELECT seq_num, name, age, stature, prize_money, blood FROM onepiece |
ON DUPLICATE KEY UPDATE 를 사용한 출결결과를 보면
여기서 age = age + 2 는 기존 값이 존재한다면 기존 값에서 +2를 더해주었다.
완전히 새로운 데이터로 초기화 하기 위해서는 stature = VALUES( stature ), prize_money = VALUES( prize_money ) 와 같이
VALUES를 활용하게 되면 완전히 새로운 데이터로 치환되는것을 확인 할 수 있다.
한가지 더 눈여겨 봐야 할 것은 데이터가 추가될 때마다 자동 증가되는 seq_num 컬럼 시퀀스 값이다.
1, 2, 3 다음에 4, 5, 6이 생략되고 7, 8, 9 10이 추가되는 것을 볼 수 있는데
이를 통해 ON DUPLICATE KEY UPDATE 작동방식을 유추해 볼 수 있다.
① INSERT 문을 실행하여 신규 데이터를 입력한다. ② UNIQUE KEY( name컬럼 )의 에 중복되는 값을 확인한다. ③ 중복된 UNIQUE KEY값이 존재하면 ON DUPLICATE KEY UPDATE 지정된 컬럼( age, stature, prize_money )컬럼을 UPDATE 한다. ④ 마지막으로 새로 INSERT된 중복되는 UNIQUE KEY값을 가진 항목을 DELETE 한다. |
필자는 항상 ON DUPLICATE KEY UPDATE 를 알기 전까지는 항상
if( UNIQUE KEY == TRUE ) { console.log( UPDATE문 실행. ); } else { console.log( INSERT문 실행. ); } |
의 선택지 외에는 존재하지 않았다.
물론 이와 같은 방법으로도 해결은 가능하지만,
참고 : https://mssun.tistory.com/entry/MySQL-replace-into
REPLACE INTO 관련내용 정리
'MySQL > MySQL' 카테고리의 다른 글
| [MySQL] 전체 날짜 출력하기 - 데이터가 없는 경우 포함 (2) | 2020.02.07 |
|---|---|
| [PHP] MySQL 한글 깨지는 경우 (0) | 2018.09.09 |
| [MySQL] 날짜 YYYY-MM-DD 형식으로 출력하기. (0) | 2018.08.20 |
[MySQL] 전체 날짜 출력하기 - 데이터가 없는 경우 포함[MySQL] 전체 날짜 출력하기 - 데이터가 없는 경우 포함
Posted at 2020. 2. 7. 10:22 | Posted in MySQL/MySQL참고 : https://dyang34.tistory.com/362
■ 값이 존재하지 않는 데이터의 날짜 출력하기
# 조회쿼리
SELECT a.date_ymd FROM ( SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as date_ymd FROM ( SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) AS a CROSS JOIN ( SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) AS b CROSS JOIN ( SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) AS c ) AS a WHERE 1 = 1 AND a.date_ymd BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59' -- AND a.date_ymd LIKE '2020-01-%' -- 한달만 검색 하려고 하는경우 ORDER BY a.date_ymd ASC |
# 출력결과
'MySQL > MySQL' 카테고리의 다른 글
| [MySQL] ON DUPLICATE KEY UPDATE 사용하기 (0) | 2020.05.14 |
|---|---|
| [PHP] MySQL 한글 깨지는 경우 (0) | 2018.09.09 |
| [MySQL] 날짜 YYYY-MM-DD 형식으로 출력하기. (0) | 2018.08.20 |
[MariaDB] Ubuntu MariaDB 시간 변경[MariaDB] Ubuntu MariaDB 시간 변경
Posted at 2019. 7. 30. 15:22 | Posted in MySQL/MariaDB참고 : https://jootc.com/p/201905052779
■ 우분투 마리아DB 시간대 변경
MariaDB> SELECT @@system_time_zone, NOW() FROM DUAL; |
$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf |
/* 이 하 생 략 */ [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking default-time-zone = "+9:00" /* 이 하 생 략 */ |
MariaDB> SELECT @@system_time_zone, NOW() FROM DUAL; |
'MySQL > MariaDB' 카테고리의 다른 글
| [MariaDB] 테이블 구조 복사 쿼리 (0) | 2019.01.09 |
|---|---|
| [MariaDB] root 계정의 비밀번호 및 보안 설정 - Ubuntu (0) | 2018.12.11 |
| [MariaDB] MariaDB의 데이터 베이스 및 사용자 계정 추가하기 (0) | 2018.06.26 |
| [MariaDB] Windows에서 MariaDB 세팅하기 (0) | 2018.06.26 |
| [MariaDB] Windows에 MariaDB 다운받고 설치하기 (0) | 2018.04.10 |
[MariaDB] 테이블 구조 복사 쿼리[MariaDB] 테이블 구조 복사 쿼리
Posted at 2019. 1. 9. 00:30 | Posted in MySQL/MariaDB참고 : https://extbrain.tistory.com/59
■ MariaDB 테이블 구조 복사 쿼리 생성
많은 Data Base 툴들은 기본적으로 DB 구조를 복사할 쿼리를 자동으로 생성해 주지만.
간혹 뭔가 지정해둔 속성이 빠지거나 하는 경우가 많아.
따로 정리하게 되었다.
MariaDB > SHOW CREATE TABLE 테이블명 |
위의 결과를 출력하면 해당 테이블을 생성하는 CREATE 쿼리문이 자동으로 생성되어 나오는 것을 확인 할 수 있다.
■ MariaDB 테이블 컬럼명 조회
MariaDB > SHOW COLUMNS FROM 테이블명 |
|
'MySQL > MariaDB' 카테고리의 다른 글
| [MariaDB] Ubuntu MariaDB 시간 변경 (0) | 2019.07.30 |
|---|---|
| [MariaDB] root 계정의 비밀번호 및 보안 설정 - Ubuntu (0) | 2018.12.11 |
| [MariaDB] MariaDB의 데이터 베이스 및 사용자 계정 추가하기 (0) | 2018.06.26 |
| [MariaDB] Windows에서 MariaDB 세팅하기 (0) | 2018.06.26 |
| [MariaDB] Windows에 MariaDB 다운받고 설치하기 (0) | 2018.04.10 |
