MySQL replace語句 - tw511教學網

在本教學中，您將學習如何使用MySQL REPLACE語句向資料庫表插入或更新資料。

MySQL REPLACE語句簡介

MySQL REPLACE語句是標準SQL的MySQL擴充套件。 MySQL REPLACE語句的工作原理如下：

如果給定行資料不存在，那麼MySQL REPLACE語句會插入一個新行。
如果給定行資料存在，則REPLACE語句首先刪除舊行，然後插入一個新行。在某些情況下，REPLACE語句僅更新現有行。

MySQL使用PRIMARY KEY或UNIQUE KEY索引來要確定表中是否存在新行。如果表沒有這些索引，則REPLACE語句等同於INSERT語句。

要使用MySQL REPLACE語句，至少需要具有INSERT和DELETE許可權。

請注意，有一個REPLACE字串函式，它不是本教學中說述的REPLACE語句。

MySQL REPLACE語句範例

我們來看一下使用REPLACE語句來更好地了解它的工作原理的例子。

首先，建立一個名為cities的新表，如下所示：

USE testdb;

CREATE TABLE cities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    population INT NOT NULL
);

接下來，在cities表中插入一些行記錄：

INSERT INTO cities(name,population)
VALUES('New York',8008278),
   ('Los Angeles',3694825),
   ('Shanghai',1923400);

下面查詢cities表中的資料，以驗證插入操作 -

mysql> SELECT * FROM cities;
+----+-------------+------------+
| id | name        | population |
+----+-------------+------------+
|  1 | New York    |    8008278 |
|  2 | Los Angeles |    3694825 |
|  3 | Shanghai    |    1923400 |
+----+-------------+------------+
3 rows in set

現在，在cities表中有三個城市資料。

那麼，假設我們要將紐約市的人口更新為1008256，可以使用UPDATE語句如下：

UPDATE cities 
SET 
    population = 1008256
WHERE
    id = 1;

再次查詢cities表中的資料來驗證更新結果 -

mysql> UPDATE cities 
SET 
    population = 1008256
WHERE
    id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM cities;
+----+-------------+------------+
| id | name        | population |
+----+-------------+------------+
|  1 | New York    |    1008256 |
|  2 | Los Angeles |    3694825 |
|  3 | Shanghai    |    1923400 |
+----+-------------+------------+
3 rows in set

UPDATE語句的確按照預期更新了資料。

之後，使用REPLACE宣告將洛杉磯市的人口更新為3696820。

REPLACE INTO cities(id,population) VALUES(2,3696820);
Query OK, 2 rows affected

上面執行返回結果中提示：2 rows affected，說明有兩行資料受影響。

最後，再次查詢城市表的資料來驗證替換的結果。

mysql> SELECT  * FROM  cities;
+----+----------+------------+
| id | name     | population |
+----+----------+------------+
|  1 | New York |    1008256 |
|  2 | NULL     |    3696820 |
|  3 | Shanghai |    1923400 |
+----+----------+------------+
3 rows in set

現在name列為NULL。您可能期望name列的值保持不變。但是，REPLACE語句不這樣做。在這種情況下，REPLACE語句的工作原理如下：

REPLACE語句首先使用列列表提供的資訊將新行插入到cities表中。但是插入失敗，因為在cities表中已經存在ID為2的行記錄，因此，MySQL引發了重複鍵錯誤。
然後，REPLACE語句更新具有id列值為2指定的行記錄。在正常進程中，它將先刪除具有衝突id為2的舊行，然後插入一個新行。

MySQL REPLACE和INSERT

REPLACE語句的第一種形式類似於INSERT語句，除了INSERT關鍵字換成REPLACE關鍵字以外，如下所示：

REPLACE INTO table_name(column_list)
VALUES(value_list);

例如，如果要在cities表中插入新行，請使用以下查詢：

REPLACE INTO cities(name,population)
VALUES('Phoenix',1321523);

請注意，沒有出現在REPLACE語句中的列將使用預設值插入相應的列。如果列具有NOT NULL屬性並且沒有預設值，並且您如果沒有在REPLACE語句中指定該值，則MySQL將引發錯誤。這是REPLACE和INSERT語句之間的區別。

例如，在以下語句中，僅指定name列的值，而沒有指定population列。MySQL引發錯誤訊息。因為population列不接受NULL值，而我們定義cities表時，也沒有指定population列的預設值。

REPLACE INTO cities(name)
VALUES('Houston');

執行上面語句後，MySQL發出如下的錯誤訊息：

Error Code: 1364. Field 'population' doesn't have a default value

MySQL REPLACE和UPDATE

REPLACE語句的第二種形式類似於UPDATE語句，如下所示：

REPLACE INTO table
SET column1 = value1,
    column2 = value2;

請注意，REPLACE語句中沒有WHERE子句。

例如，如果要將Phoenix城市的人口更新為1768980，請使用REPLACE語句，如下所示：

REPLACE INTO cities
SET id = 4,
    name = 'Phoenix',
    population = 1768980;

與UPDATE語句不同，如果不在SET子句中指定列的值，則REPLACE語句將使用該列的預設值。

SELECT * FROM cities;

MySQL REPLACE INTO和SELECT

REPLACE語句的第三種形式類似於INSERT INTO SELECT語句：

REPLACE INTO table_1(column_list)
SELECT column_list
FROM table_2
WHERE where_condition;

假設要複製ID為1的城市行記錄，可以使用REPLACE INTO SELECT語句，如下查詢範例：

REPLACE INTO cities(name,population)
SELECT name,population FROM cities 
WHERE id = 1;

MySQL REPLACE語句用法

使用REPLACE語句時需要知道幾個重點：

如果您開發的應用程式不僅支援MySQL資料庫，而且還支援其他關聯式資料庫管理系統(RDBMS)，則應避免使用REPLACE語句，因為其他RDBMS可能不支援。代替的作法是在事務中使用DELETE和INSERT語句的組合。
如果在具有觸發器的表中使用了REPLACE語句，並且發生了重複鍵錯誤的刪除，則觸發器將按以下順序觸發：在刪除前刪除，刪除之後，刪除後，如果REPLACE語句刪除當前行並插入新行。如果REPLACE語句更新當前行，則觸發BEFORE UPDATE和AFTER UPDATE觸發器。

在本教學中，您學習了不同形式的REPLACE語句來插入或更新表中的資料。