15.2.12 REPLACE Statement

15.2.12 REPLACE Statement

1REPLACE [LOW_PRIORITY | DELAYED]
2    [INTO] tbl_name
3    [PARTITION (partition_name [, partition_name] ...)]
4    [(col_name [, col_name] ...)]
5    { {VALUES | VALUE} (value_list) [, (value_list)] ...
6      |
7      VALUES row_constructor_list
8    }
9
10REPLACE [LOW_PRIORITY | DELAYED]
11    [INTO] tbl_name
12    [PARTITION (partition_name [, partition_name] ...)]
13    SET assignment_list
14
15REPLACE [LOW_PRIORITY | DELAYED]
16    [INTO] tbl_name
17    [PARTITION (partition_name [, partition_name] ...)]
18    [(col_name [, col_name] ...)]
19    {SELECT ... | TABLE table_name}
20
21value:
22    {expr | DEFAULT}
23
24value_list:
25    value [, value] ...
26
27row_constructor_list:
28    ROW(value_list)[, ROW(value_list)][, ...]
29
30assignment:
31    col_name = value
32
33assignment_list:
34    assignment [, assignment] ...

REPLACE는 INSERT와 정확히 동일하게 동작하지만, 테이블의 기존 row가 새 row와 PRIMARY KEY 또는 UNIQUE 인덱스의 값이 동일한 경우, 새 row를 insert하기 전에 기존 row를 delete합니다. Section 15.2.7, “INSERT Statement”를 참조하십시오.

REPLACE는 SQL standard에 대한 MySQL 확장입니다. 이 구문은 insert를 수행하거나, delete 후 insert를 수행합니다. insert 또는 update를 수행하는 SQL standard에 대한 또 다른 MySQL 확장에 대해서는 Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”를 참조하십시오.

DELAYED insert 및 replace는 MySQL 5.6에서 deprecated되었습니다. MySQL 9.5에서 DELAYED는 지원되지 않습니다. 서버는 DELAYED 키워드를 인식하지만 무시하고, replace를 non-delayed replace로 처리하며, ER_WARN_LEGACY_SYNTAX_CONVERTED 경고를 생성합니다: REPLACE DELAYED is no longer supported. The statement was converted to REPLACE. DELAYED 키워드는 향후 릴리스에서 제거될 예정입니다. release.

참고

REPLACE는 테이블에 PRIMARY KEY 또는 UNIQUE 인덱스가 있는 경우에만 의미가 있습니다. 그렇지 않으면, 새로운 row가 기존 row를 중복하는지 판단할 인덱스가 없기 때문에 INSERT와 동일해집니다.

모든 column의 값은 REPLACE 구문에 지정된 값에서 가져옵니다. 누락된 column은 INSERT의 경우와 마찬가지로 그들의 기본값으로 설정됩니다. 현재 row의 값을 참조하여 새 row에서 사용할 수는 없습니다. SET col_name = col_name + 1과 같은 할당을 사용하는 경우, 오른쪽에 있는 column 이름에 대한 참조는 DEFAULT(col_name)으로 처리되므로, 이 할당은 SET col_name = DEFAULT(col_name) + 1과 동일합니다.

REPLACE가 insert를 시도하는 column 값은 VALUES ROW()를 사용하여 지정할 수 있습니다.

REPLACE를 사용하려면 해당 테이블에 대해 INSERT 권한과 DELETE 권한을 모두 가지고 있어야 합니다.

generated column을 명시적으로 replace하는 경우, 허용되는 유일한 값은 DEFAULT입니다. generated column에 대한 정보는 Section 15.1.24.8, “CREATE TABLE and Generated Columns”를 참조하십시오.

REPLACE는 PARTITION 절에서 파티션, 서브파티션 또는 둘 다의 이름을 콤마로 구분한 목록을 사용하여 명시적인 파티션 선택을 지원합니다. INSERT의 경우와 마찬가지로, 새 row를 이들 파티션이나 서브파티션 중 어느 곳에도 insert할 수 없는 경우, REPLACE 구문은 Found a row not matching the given partition set 오류와 함께 실패합니다. 자세한 정보와 예제는 Section 26.5, “Partition Selection”을 참조하십시오.

REPLACE 구문은 영향을 받는 row의 수를 나타내는 카운트를 반환합니다. 이 값은 delete된 row와 insert된 row의 합입니다. single-row [REPLACE](https://dev.mysql.com/doc/refman/9.5/en/replace.html "15.2.12 REPLACE Statement)에 대해 카운트가 1이면, row가 insert되었고 delete된 row는 없습니다. 카운트가 1보다 크면, 새 row가 insert되기 전에 하나 이상의 기존 row가 delete되었습니다. 테이블에 여러 유니크 인덱스가 있고, 새 row가 서로 다른 유니크 인덱스에서 서로 다른 기존 row의 값을 중복하는 경우, 하나의 row가 둘 이상의 기존 row를 replace하는 것도 가능합니다.

영향을 받는 row 수 카운트를 통해 REPLACE가 row를 단순히 추가했는지, 아니면 row를 replace했는지를 쉽게 판별할 수 있습니다: 카운트가 1(추가)인지, 1보다 큰지(대체) 확인하십시오.

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

서브쿼리에서 동일한 테이블을 select하면서 그 테이블에 replace할 수는 없습니다.

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

1. 새 row를 테이블에 insert하려고 시도합니다.

2. primary key 또는 유니크 인덱스에 대해 duplicate-key 에러가 발생하여 insert가 실패하는 동안에는:

   1. duplicate key 값을 가진 충돌하는 row를 테이블에서 delete합니다.

   2. 새 row를 테이블에 insert하기를 다시 시도합니다.

duplicate-key 에러가 발생하는 경우, 스토리지 엔진이 delete 후 insert가 아닌 update로 REPLACE를 수행할 수 있지만, 시맨틱은 동일합니다. 스토리지 엔진이 Handler_xxx 상태 변수를 증가시키는 방식에 차이가 있을 수 있다는 점을 제외하면 사용자가 볼 수 있는 효과는 없습니다.

REPLACE ... SELECT 구문의 결과는 SELECT로부터의 row 순서에 의존하며, 이 순서는 항상 보장될 수 있는 것이 아니기 때문에, 이러한 구문을 로깅할 때 소스와 레플리카가 서로 달라질 수 있습니다. 이러한 이유로, REPLACE ... SELECT 구문은 구문 기반 복제에서 unsafe한 것으로 표시됩니다. 이러한 구문은 구문 기반 모드를 사용할 때 에러 로그에 경고를 생성하며, MIXED 모드를 사용할 때는 row 기반 포맷을 사용하여 바이너리 로그에 기록됩니다. Section 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”도 참조하십시오.

MySQL 9.5는 INSERT와 마찬가지로, REPLACE와 함께 TABLE뿐만 아니라 SELECT도 지원합니다. 자세한 정보와 예제는 Section 15.2.7.1, “INSERT ... SELECT Statement”를 참조하십시오.

파티셔닝을 지원하도록 파티션되지 않은 기존 테이블을 수정하거나, 이미 파티션된 테이블의 파티셔닝을 수정할 때는, 테이블의 primary key를 변경하는 것을 고려할 수 있습니다(참조: Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”).

이렇게 하는 경우, nonpartitioned 테이블의 primary key를 변경하는 경우와 마찬가지로, REPLACE 구문의 결과에 영향이 있을 수 있다는 점을 알고 있어야 합니다. 다음과 같은 CREATE TABLE 구문으로 생성된 테이블을 고려해 보십시오:

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

이 테이블을 생성한 후 mysql 클라이언트에서 아래에 표시된 구문을 실행하면, 결과는 다음과 같습니다:

1mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
2Query OK, 1 row affected (0.04 sec)
3
4mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
5Query OK, 2 rows affected (0.04 sec)
6
7mysql> SELECT * FROM test;
8+----+------+---------------------+
9| id | data | ts                  |
10+----+------+---------------------+
11|  1 | New  | 2014-08-20 18:47:42 |
12+----+------+---------------------+
131 row in set (0.00 sec)

이제 두 번째 테이블을 생성하는데, primary key가 이제 2개의 column을 포함한다는 점만 (강조된 텍스트) 원래 테이블과 거의 동일합니다:

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

원래 test 테이블에서와 동일한 두 개의 REPLACE 구문을 test2에서 실행하면, 다른 결과를 얻습니다:

1mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
2Query OK, 1 row affected (0.05 sec)
3
4mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
5Query OK, 1 row affected (0.06 sec)
6
7mysql> SELECT * FROM test2;
8+----+------+---------------------+
9| id | data | ts                  |
10+----+------+---------------------+
11|  1 | Old  | 2014-08-20 18:47:00 |
12|  1 | New  | 2014-08-20 18:47:42 |
13+----+------+---------------------+
142 rows in set (0.00 sec)

이는 test2에서 실행될 때, row가 replace되려면 id와 ts column 값 모두가 기존 row의 값과 일치해야 하고, 그렇지 않으면 row가 insert되기 때문입니다.