Month: September 2020

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/