MySQL :: MySQL 9.0 參考手冊 :: 15.2.12 REPLACE 語句

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / REPLACE 陳述式

15.2.12 REPLACE 陳述式

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ...
      |
      VALUES row_constructor_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 ... | TABLE table_name}

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

REPLACE 的運作方式與 INSERT 完全相同，差別在於如果資料表中舊的列 (row) 的 PRIMARY KEY 或 UNIQUE 索引值與新列相同，則會在插入新列之前刪除舊列。請參閱第 15.2.7 節，「INSERT 陳述式」。

REPLACE 是 MySQL 對 SQL 標準的擴充。它不是插入，就是刪除再插入。另一個 MySQL 對標準 SQL 的擴充 — 不是插入，就是更新 — 請參閱第 15.2.7.2 節，「INSERT ... ON DUPLICATE KEY UPDATE 陳述式」。

在 MySQL 5.6 中，DELAYED 插入和取代已被棄用。在 MySQL 9.0 中，不支援 DELAYED。伺服器會識別但忽略 DELAYED 關鍵字，將取代操作視為非延遲取代來處理，並產生一個 ER_WARN_LEGACY_SYNTAX_CONVERTED 警告：REPLACE DELAYED 已不再支援。該陳述式已轉換為 REPLACE。DELAYED 關鍵字預計在未來的版本中移除。

注意

只有在資料表具有 PRIMARY KEY 或 UNIQUE 索引時，REPLACE 才有意義。否則，它會等同於 INSERT，因為沒有索引可以用來判斷新列是否與另一列重複。

所有欄位的值都取自 REPLACE 陳述式中指定的值。任何遺失的欄位都會設為其預設值，如同 INSERT 的情況。您無法參考目前列的值並在新列中使用它們。如果您使用諸如 SET col_name = col_name + 1 的賦值，則右側的欄位名稱參照會被視為 DEFAULT(col_name)，因此賦值等同於 SET col_name = DEFAULT(col_name) + 1。

您可以使用 VALUES ROW() 指定 REPLACE 嘗試插入的欄位值。

若要使用 REPLACE，您必須具有資料表的 INSERT 和 DELETE 權限。

如果明確取代產生的欄位，唯一允許的值為 DEFAULT。如需產生欄位的資訊，請參閱第 15.1.20.8 節，「CREATE TABLE 和產生欄位」。

REPLACE 支援使用 PARTITION 子句明確選取分割區，該子句包含以逗號分隔的分割區、子分割區或兩者的名稱清單。與 INSERT 相同，如果無法將新列插入這些分割區或子分割區中的任何一個，REPLACE 陳述式將會失敗，並顯示錯誤訊息找不到符合指定分割區集的列。如需更多資訊和範例，請參閱第 26.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 的列的排序，並且無法始終保證此排序，因此在記錄來源和複本的這些陳述式時可能會出現差異。因此，REPLACE ... SELECT 陳述式會標示為對於基於陳述式的複寫是不安全的。當使用基於陳述式的模式時，此類陳述式會在錯誤日誌中產生警告，並且當使用 MIXED 模式時，會使用基於列的格式寫入二進位日誌。另請參閱第 19.2.1.1 節，「基於陳述式和基於列的複寫之優缺點」。

MySQL 9.0 支援 TABLE 以及 SELECT 與 REPLACE 一起使用，就像它對 INSERT 所做的一樣。如需更多資訊和範例，請參閱第 15.2.7.1 節，「INSERT ... SELECT 陳述式」。

在修改現有未分割的資料表以容納分割，或修改已分割資料表的分割時，您可以考慮變更資料表的主索引鍵（請參閱第 26.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)

現在我們建立第二個資料表，它與第一個資料表幾乎相同，但主索引鍵現在涵蓋 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 上執行與原始 test 資料表相同的兩個 REPLACE 陳述式時，我們會得到不同的結果：

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 欄位值都必須與現有列的值相符，才能取代該列；否則會插入一列。

上一個首頁向上下一個