9.8 REPLACE 语句

在本教程中，您将学习如何使用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语句来插入或更新表中的数据。