[MySQL] INSERT INTO ... DUPLICATE KEY UPDATE & REPLACE INTO

1) REPLACE INTO

REPLACE INTO `TABLE_NAME`
VALUE (col_value1, col_value1, col_value1);

> 데이터 col_value1, col_value2, col_value3를 TABLE에 삽입한다.

중복된 레코드는 삭제 후 삽입한다. (주의! auto_increment 증가함)

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test; 
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

REPLACE INTO ... SELECT 도 가능하다.

REPLACE INTO `TABLE1`
SELECT col_name1, col_name2, col_name3

FROM `TABLE2`
WHERE where_condition

> TABLE2의 데이터 중 where_condition에 매칭하는 데이터만을 TABLE1에 삽입한다.

중복되면 삭제 후 삽입한다.

주의!

REPLACE를 사용하려면 테이블에 인덱스가 존재하여야 한다.

( REPLACE makes sense only if a table has a PRIMARY KEY or UNIQUE index. Otherwise, it becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another. )

참고 URL:
http://dev.mysql.com/doc/refman/5.7/en/replace.html

2) INSERT INTO ... ON DUPLICATE KEY UPDATE

인덱스에 의해 중복되는 레코드의 삽입을 진행할 경우 INSERT INTO는 에러를 발생시킨다. 하지만 INSERT IN ... ON DUPLICATE KEY UPDATE는 중복이 발생할 경우 이 레코드에 대해 ON DUPLICATE KEY UPDATE 뒤의 조건에 따라 업데이트를 수행한다.

인덱스 중복이 일어난 경우 다음의 두문장은 동일한 역할을 수행한다.

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

여러 컬럼에 대한 명령의 수행도 가능하며 각 값에 조건에 따른 업데이트도 가능하다.

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

위 구문은 아래와 동일한 쿼리이다.

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

참고 URL:
http://dev.mysql.com/doc/refman/5.7/en/insert.html
http://dev.mysql.com/doc/refman/5.7/en/insert-select.html
http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html


PS. 
근래 MySQL에 대한 것을 자주 올리네.. 흠..
내가 어쩌다가 이렇게 된거지;;;; 
DB를 직접 만들어쓰던 내가 어쩌다가 이렇게;; 주르륽.. ㅠㅠ
.

[MySQL]INSERT ... ON DUPLICATE KEY UPDATE Syntax

MySQL에서 레코드가 이미 있다면 Update를 해야하는 경우 다음의 Query를 이용하면 편하다.

INSERT INTO 테이블이름 (이름,점수)
VALUES (“홍길동”,“100”)
ON DUPLICATE KEY UPDATE
점수=100;

혹은 카운트를 계산하여야 하는 경우..

INSERT INTO 테이블이름 (이름,방문횟수)
VALUES (“홍길동”,“100”)
ON DUPLICATE KEY UPDATE
방문횟수 = 방문횟수 +1;

(중복키를 이용하는 경우이니 당연히 UNIQUE가 걸린 필드를 기준으로 삼아야 할 것이다. 위의 경우는 이름필드가 UNIQUE가 되어야 할 것이다.)



(아래는, MySQL 설명을 내맘대로 번역해봤다. 번역이 틀렸다면.... 미안하다!!)

영어원문: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax


만일 ON DUPLICATE KEY UPDATE를 설정하고 INSERT를 수행하면 UNIQUE index나 PRIMARY KEY로 지정된 컬럼의 중복이 있을 시 UPDATE를 수행한다. 예를들어, UNIQUE로 선언된 a 컬럼이 1의 값을 가지고 있다면 다음의 두 문장은 동일한 명령을 수행한다:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;

ON DUPLICATE KEY UPDATE는 comma로 구분된 여러절의 적용도 가능하다. ON DUPLICATE KEY UPDATE로서 행당 영향을 받는 컬럼은 새로운 행으로 삽입되며, 중복시에는 업데이트가 수행된다.
만일 b 컬럼이 unique라면, INSERT 대신 UPDATE 명령이 수행된다.

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

만일 a=1 OR b=2 조건에 일치하는 행이 여러개 존재한다면, 단지 하나의 행(one)만이 업데이트 된다.
일반적으로 여러개의 고유 인덱스가 있는 테이블의 절에는 ON DUPLICATE KEY UPDATE의 사용을 자제해야 한다.


당신은 INSERT ... UPDATE문의 INSERT에서 열 값을 참조하는 UPDATE 절의 VALUES(col_name)를 사용할 수 있다. 이 VALUES()는 다중행 삽입 시 특히 유용하다.

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

위 문장은 아래와 동일하다:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9; 

AUTO_INCREMENT 컬럼을 가진 테이블에 INSERT ... UPDATE 로서 삽입을 실행하는 경우, LAST_INSERT_ID()는 AUTO_INCREMENT의 값을 리턴한다.
만일 행의 삽입을 진행하는 경우 LAST_INSERT_ID()는 의미가 없다.
그러나, 당신은 LAST_INSERT_ID(expr)로서 INSERT ... UPDATE를 진행할 수 있다.

id가 AUTO_INCREMENT컬럼이라고 가정할 경우, LAST_INSERT_ID()가 의미를 가지게 하려면, 다음과 같이 행을 삽입/갱신하면 된다.

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

당신이 ON DUPLICATE KEY UPDATE를 사용할 때엔, DELAYED는 무시된다.





.