CREATE TABLE t_user_logs (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(30) NOT NULL,
action_ts TIMESTAMP NOT NULL,
action_type VARCHAR(30) NOT NULL,
KEY `idx_user_logs_uniq` (`username`,`action_ts`)
);Upsert
INSERT INTO t_user_logs
(username, action_ts, action_type)
VALUES
('user1', now(), 'LOGIN'), ('user2', now() 'LOGIN)
ON DUPLICATE KEY UPDATE
action_type=VALUES(action_type);Replace
REPLACE INTO t_user_logs
( username, action_ts, action_type)
VALUES
('user1', now(), 'LOGIN');Insert ignore
대용량 넣을 때 반복데이터가 들어올 때가 간혹 있는 경우
INSERT IGNORE INTO t_user_logs
(username, action_ts, action_type)
VALUES
('user1', '2000-01-01 11:30:10', 'LOGIN'), ('user2', '2000-01-01 11:30:11', 'LOGIN');Insert Batch
ignore안하고 해도 됨
INSERT INTO t_user_logs (username, action_ts, action_type) VALUES ('user1', '2000-01-01 11:30:10', 'LOGIN'), ('user2', '2000-01-01 11:30:11', 'LOGIN');참고
- https://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query
- https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/
- https://www.techbeamers.com/mysql-upsert/
- https://number1.co.za/mysql-insert-if-not-exists/