MySQL replace語句

2019-10-16 22:58:18

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

MySQL REPLACE語句簡介

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

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

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

要使用MySQL REPLACE語句,至少需要具有INSERTDELETE許可權。

請注意,有一個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表中已經存在ID2的行記錄,因此,MySQL引發了重複鍵錯誤。

  • 然後,REPLACE語句更新具有id列值為2指定的行記錄。在正常進程中,它將先刪除具有衝突id2的舊行,然後插入一個新行。

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將引發錯誤。這是REPLACEINSERT語句之間的區別。

例如,在以下語句中,僅指定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;

假設要複製ID1的城市行記錄,可以使用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 UPDATEAFTER UPDATE觸發器。

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