Multi Rows Insert - ON DUPLICATE KEY UPDATE

[출처] Multi Rows Insert - ON DUPLICATE KEY UPDATE (MySQL Power Group) |작성자 ham

http://cafe.naver.com/mysqlpg/585

Insert ~ ON DUPLICATE KEY UPDATE 글(http://cafe.naver.com/mysqlpg/583)을 보고,

예전에 테스트 했던 내용이 생각나 올려보네요.

 

Reference : http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

 

use test;

 

drop table if exists xxx;

create table xxx (
  useridx     bigint        not null
, memo        varchar(20)
, reg_date    datetime
, upd_date    datetime
, primary key (useridx)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 1) Single Row Insert

 

INSERT INTO xxx (useridx, memo, reg_date) VALUES (0, 'TEST-0', now())
   ON DUPLICATE KEY UPDATE useridx = 0;

 

select * from xxx;

+---------+--------+---------------------+----------+
| useridx | memo   | reg_date            | upd_date |
+---------+--------+---------------------+----------+
|       0 | TEST-0 | 2014-01-12 09:16:25 | NULL     |
+---------+--------+---------------------+----------+

 

-- 2) Multi Row Insert

 

INSERT INTO xxx (useridx, memo, reg_date)
     VALUES (1, 'TEST-1', now())
           ,(2, 'TEST-2', now())
           ,(3, 'TEST-3', now())
           ,(4, 'TEST-4', now())
           ,(5, 'TEST-5', now())
   ON DUPLICATE KEY UPDATE useridx = VALUES(useridx);

 

select * from xxx;

+---------+--------+---------------------+----------+
| useridx | memo   | reg_date            | upd_date |
+---------+--------+---------------------+----------+
|       0 | TEST-0 | 2014-01-12 09:16:25 | NULL     |
|       1 | TEST-1 | 2014-01-12 09:16:37 | NULL     |
|       2 | TEST-2 | 2014-01-12 09:16:37 | NULL     |
|       3 | TEST-3 | 2014-01-12 09:16:37 | NULL     |
|       4 | TEST-4 | 2014-01-12 09:16:37 | NULL     |
|       5 | TEST-5 | 2014-01-12 09:16:37 | NULL     |
+---------+--------+---------------------+----------+

 

-- 3) Multi Row Insert & Update

 

INSERT INTO xxx (useridx, memo, reg_date)
     VALUES (1, 'TEST-11', now())
           ,(2, 'TEST-12', now())
           ,(3, 'TEST-13', now())
   ON DUPLICATE KEY UPDATE useridx  = VALUES (useridx)
                          ,memo     = VALUES (memo)
                          ,upd_date = now();

 

select * from xxx;

+---------+---------+---------------------+---------------------+
| useridx | memo    | reg_date            | upd_date            |
+---------+---------+---------------------+---------------------+
|       0 | TEST-0  | 2014-01-12 09:16:25 | NULL                |
|       1 | TEST-11 | 2014-01-12 09:16:37 | 2014-01-12 09:16:54 |
|       2 | TEST-12 | 2014-01-12 09:16:37 | 2014-01-12 09:16:54 |
|       3 | TEST-13 | 2014-01-12 09:16:37 | 2014-01-12 09:16:54 |
|       4 | TEST-4  | 2014-01-12 09:16:37 | NULL                |
|       5 | TEST-5  | 2014-01-12 09:16:37 | NULL                |
+---------+---------+---------------------+---------------------+

 

[출처] Multi Rows Insert - ON DUPLICATE KEY UPDATE (MySQL Power Group) |작성자 ham