MySQL replace語句

MySQL replace語法

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)] ...
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
SET assignment_list
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
SELECT ...
value:
{expr | DEFAULT}
value_list:
value [, value] ...
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...

REPLACE的工作方式與INSERT完全相同，不同的是，如果表中的舊行與PRIMARY KEY或UNIQUE索引中的新行具有相同的值，則在插入新行之前刪除舊行。

REPLACE是MySQL對SQL標準的擴充套件。它要麼插入，要麼刪除和插入。

DELAYED插入和替換在MySQL 5.6中已被棄用。MySQL 5.7不支援DELAYED。伺服器識別但忽略DELAYED關鍵字，將替換作為非延遲替換處理，並生成er_warn_legacy_syntax_convert警告。(不再支援REPLACE DELAYED。語句被轉換為REPLACE。)DELAYED關鍵字將在未來的版本中被刪除。

只有當一個表有一個主鍵或唯一索引時，REPLACE才有意義。否則，它將等價於INSERT，因為沒有索引可用於確定新行是否與另一行重複。

所有列的值都取自REPLACE語句中指定的值。所有缺失的列都被設定為預設值，就像INSERT一樣。不能引用當前行的值並在新行中使用它們。如果使用諸如SET col_name = col_name + 1這樣的賦值，對右邊列名的引用將被視為DEFAULT(col_name)，因此賦值等價於

SET col_name = DEFAULT(col_name) + 1。

要使用REPLACE，必須同時擁有表的INSERT和DELETE特權。

如果顯式替換生成的列，則唯一允許的值是DEFAULT

REPLACE支援顯式分割槽選擇，使用partition關鍵字和分割槽、子分割槽或兩者的相對名稱列表。與INSERT一樣，如果不可能將新行插入到任何這些分割槽或子分割槽中，則REPLACE語句將失敗，並出現Found a row not matching the given partition set錯誤。

REPLACE語句返回一個計數，以指示受影響的行數。這是刪除和插入的行之和。如果對於單行REPLACE計數為1，則插入一行，不刪除任何行。如果計數大於1，則在插入新行之前刪除一個或多箇舊行。如果表包含多個唯一索引，並且新行在不同唯一索引中重複不同舊行的值，則單個行可以替換多箇舊行。

受影響的行數可以很容易地確定REPLACE是隻新增了一行，還是也替換了任何行:檢查計數是否為1(新增)或更大(替換)。

如果您使用的是C API，受影響的行數可以透過mysql_affected_rows()函式獲得。

您不能在子查詢中替換一個表並從同一表中進行選擇

MySQL對於replace(和load data ... replace)使用如下演算法:
1.嘗試將新行插入到表中
2.當因為主鍵或唯一索引出現重複鍵錯誤而插入失敗時：
a.從表中刪除具有重複鍵值的衝突行
b.請再次將新行插入表中

在出現重複鍵錯誤的情況下，儲存引擎可能會將REPLACE作為更新而不是刪除加插入來執行，但語義是相同的。除了儲存引擎增加Handler_xxx狀態變數的方式不同之外，沒有使用者可見的影響

因為REPLACE …SELECT語句的結果依賴於SELECT語句中的行順序，並且這種順序不能總是得到保證，當記錄這些語句時，主伺服器和從伺服器可能出現分歧。基於這個理由，replace ... select語句對於基於語句的複製來說被標記為不安全。當使用基於語句的模式時，這些語句會在錯誤日誌中產生一個警告，而當使用MIXED模式時，這些語句會使用基於行的格式寫入二進位制日誌。

當修改沒有分割槽的現有表以適應分割槽時，或者在修改已分割槽表的分割槽時，可以考慮修改表的主鍵(參見22.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.03 sec)
mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.10 sec)
mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.01 sec)

現在我們建立第二個表，與第一個表幾乎相同，除了主鍵現在是包含2列的複合主鍵，如下所示(強調文字)：

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)
);

當我們在test2上執行與在原始測試表上相同的兩個REPLACE語句時，我們得到了不同的結果:

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.06 sec)
mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.01 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.01 sec)

這是因為，當執行test2時，id和ts列值必須與現有行的值匹配，以便替換該行;否則，插入一行。

使用MyISAM等儲存引擎(使用表級鎖)的REPLACE語句影響分割槽表，只要不更新表分割槽列，則只鎖那些包含匹配REPLACE語句WHERE子句的行的分割槽;否則鎖定整個表。(對於像InnoDB這樣使用行級鎖的儲存引擎，不會發生分割槽鎖。)