MySQL5.6 REPLACE 구문

13.2.8 REPLACE 구문

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

또는 :

 REPLACE [LOW_PRIORITY | DELAYED]
     [INTO] tbl_name
     [PARTITION ( partition_name ...) 
     SET col_name = { expr | DEFAULT}, ...

또는 :

 REPLACE [LOW_PRIORITY | DELAYED]
     [INTO] tbl_name
     [PARTITION ( partition_name ...)  
     [( col_name , ...)]
     SELECT ...

REPLACE 은 INSERT 와 똑같이 작동합니다. 그러나 테이블의 이전 행에 PRIMARY KEY 또는 UNIQUE 인덱스에 대해 새로운 행과 동일한 값이 포함되어 있으면 이전 행은 새 행이 삽입되기 전에 삭제됩니다. 섹션 13.2.5 "INSERT 구문" 을 참조하십시오.

REPLACE 는 SQL 표준에 대한 MySQL 확장입니다. 이것은 삽입을 수행하거나 삭제 및 삽입합니다. 표준 SQL에 다른 MySQL 확장 (삽입 또는 업데이트합니다) 내용은 섹션 13.2.5.3 "INSERT ... ON DUPLICATE KEY UPDATE 구문" 을 참조하십시오.

테이블에 PRIMARY KEY 또는 UNIQUE 인덱스가 존재하지 않는 한, REPLACE 문을 사용하여도 아무 의미가 없습니다. 새로운 행이 다른 행을 복제했는지 여부를 판정하기 위해서 사용되는 인덱스가 존재하지 않기 때문에 그것은 INSERT 와 동등합니다.

모든 컬럼의 값이 REPLACE 문에 지정된 값에서 가져옵니다. 열이 없으면 INSERT 에서의 처리뿐만 아니라 그 열은 기본값으로 설정됩니다. 현재 행의 값을 참조하고 그것을 새로운 행에서 사용할 수 없습니다. SET col_name = col_name + 1 등의 대입을 사용하면 오른쪽에있는 컬럼 이름에 대한 참조는 DEFAULT( col_name ) 로 처리되기 때문에 대입이 SET col_name = DEFAULT( col_name ) + 1 과 동일합니다.

REPLACE 를 사용하려면이 테이블에 대한 INSERT 권한과 DELETE 권한이 모두 필요합니다.

MySQL 5.6.2에서 REPLACE 는 파티션 서브 파티션 또는 두 이름의 쉼표로 구분 된 목록을 포함 PARTITION 옵션을 사용하여 명시 적 파티션 선택을 지원하고 있습니다. INSERT 와 마찬가지로 이들 중 하나의 파티션 또는 서브 파티션에 새 행을 삽입 할 수없는 경우 REPLACE 문은 Found a row not matching the given partition set 오류로 실패합니다. 자세한 내용은 섹션 19.5 "파티션 선택" 을 참조하십시오.

REPLACE 는 영향을받은 행 수를 나타내는 숫자를 반환합니다. 이것은 삭제 된 행과 삽입 된 행의 합계입니다. 이 숫자는 한 줄의 REPLACE 에 대해 1 인 경우 행이 삽입되고 삭제 된 행이 없습니다. 이 값이 1보다 큰 경우, 새로운 행이 삽입되기 전에 하나 이상의 기존 행이 삭제되었습니다. 테이블에 여러 개의 고유 인덱스가 존재하면 새로운 행이 다른 고유 인덱스의 다른 오래된 행의 값을 복제 한 경우는 단 일행이 여러 오래된 줄을 바꿀 수 있습니다.

영향을받은 행 수에 따라 REPLACE 이 행을 추가했을뿐하거나 행의 대체도 행했는지를 판정하는 것이 용이합니다. 그 수가 1 (추가) 또는 그것보다 큰 (대체) 여부를 확인합니다.

C API를 사용하는 경우 mysql_affected_rows() 함수를 사용하여 영향을받은 행 수를 얻을 수 있습니다.

현재 테이블에 대체를 실시하여 하위 쿼리 같은 테이블에서 선택 할 수 없습니다.

MySQL은 REPLACE (및 LOAD DATA ... REPLACE )에 다음의 알고리즘을 사용합니다.

테이블에 새로운 행 삽입을 시도합니다
기본 키 또는 고유 인덱스에 중복 키 오류가 발생했기 때문에 삽입이 실패하는 동안 다음을 수행합니다.
1. 중복 키 값을 포함 충돌하는 행을 테이블에서 제거합니다
2. 테이블에 새로운 행 삽입을 시도합니다

중복 키 에러가 발생했을 경우, 스토리지 엔진이 삭제 및 삽입이 아니라 업데이트로 REPLACE 를 실행할 수 있지만 그 의미는 동일합니다. 스토리지 엔진이 Handler_ xxx 상태 변수를 증가하는 방법이 다를 수있다를 제외하고 사용자에게 보이는 영향은 없습니다.

REPLACE ... SELECT 문의 결과는 SELECT 에서 행의 순서에 따라, 또한이 순서를 항상 보장 할 수 없기 때문에 로깅시에 이러한 문이 마스터와 슬레이브로 다를 수 있습니다. 따라서 MySQL 5.6.4 이후에서는 REPLACE ... SELECT 문에는 문 기반 복제는 안전하지 않은 플래그가 지정됩니다. 이러한 변경으로 인해 이러한 문은 STATEMENT 바이너리 로깅 모드를 사용하는 경우에는 로그에 경고를 생성하고 MIXED 모드를 사용하는 경우 행 기반 형식을 사용하여 기록됩니다 . 섹션 17.1.2.1 "문 기반 및 열 기반 리플리케이션의 장점과 단점" 을 참조하십시오.

분할되지 않은 기존 테이블을 파티셔닝을 지원하도록 변경하고 있거나 이미 분할 된 테이블의 파티션을 변경하는 경우 해당 테이블의 기본 키의 변경을 검토하는 수 있습니다 ( 섹션 19.6.1 "파티셔닝 키, 기본 키 및 고유 키" 를 참조하십시오). 이렇게하면 분할되지 않은 테이블의 기본 키를 변경 한 경우와 마찬가지로, REPLACE 문의 결과에 영향을 줄 수 있습니다. 다음의 CREATE TABLE 문에 의해 생성 된 테이블을 생각해 보겠습니다.

 CREATE TABLE test (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   data VARCHAR (64) DEFAULT NULL,
   ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (id)
 );

이 테이블을 만들고 mysql 클라이언트에 표시된 문을 실행하면 결과는 다음과 같습니다.

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)

여기에서 다음과 같이 (강조 표시된 텍스트) 기본 키가 두 개의 열이있는 점을 제외하고 첫 번째 테이블과 거의 동일한 두 번째 테이블을 만듭니다.

 CREATE TABLE test2 (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   data VARCHAR (64) DEFAULT NULL,
   ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (id, ts)
 );

원래 test 테이블에 대해 실행 한 것과 동일한 2 개의 REPLACE 문을 test2 에 대해 실행하면 다른 결과를 얻을 수 있습니다.

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

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

이것은 test2 에서 수행 한 경우 id 컬럼과 ts 된 컬럼의 값이 대체 행에 대한 기존 행의 값과 일치해야하고, 그렇지 않으면 행이 삽입되기 때문입니다 .

MySQL 5.6.6 이전에는 테이블 수준의 잠금을 채용 한 MyISAM 등의 스토리지 엔진을 사용하는 파티션 된 테이블에 영향을 미칠 REPLACE 의해 그 테이블의 모든 파티션이 잠겨있었습니다. 이것은 REPLACE ... PARTITION 문에도 적용되었습니다. (이것은 행 레벨 락을 채용 한 InnoDB 등의 스토리지 엔진에서 발생하지 않았고 현재도 발생하지 않습니다.) MySQL 5.6.6 이후에서는, MySQL은 파티션 잠금 가지 치기를 사용합니다. 이렇게하면 그 테이블의 모든 파티션 컬럼이 업데이트되지 않는 한, REPLACE 문 WHERE 절에 일치하는 행을 포함하는 파티션 만 실제로 잠기도록합니다. 그렇지 않으면 전체 테이블이 잠겨 있습니다. 자세한 내용은 섹션 19.6.4 "파티셔닝 및 잠금" 을 참조하십시오.