MySQL系列教程 --- 37、MySQL 替换（REPLACE） 语句详解

MySQL 中的 REPLACE 语句是 SQL 标准的扩展。此语句的工作方式与 INSERT 语句相同，不同之处在于，如果旧行与表中 PRIMARY KEY 或 UNIQUE 索引的新记录匹配，则此命令会在添加新行之前删除旧行。

当我们要将现有记录更新到表中以保持更新时，需要此语句。如果我们为此使用标准插入查询，它将给出 PRIMARY KEY 的 Duplicate entry 或 UNIQUE key 错误。在这种情况下，我们将使用 REPLACE 语句来执行我们的任务。REPLACE 命令需要执行以下两种可能的操作之一：

• 如果找不到与现有数据行匹配的值，则执行标准 INSERT 语句。
• 如果发现重复记录，替换命令将删除现有行，然后在表中添加新记录。

在 REPLACE 语句中，更新分两步执行。首先，它将删除现有记录，然后添加新更新的记录，类似于标准的 INSERT 命令。因此，我们可以说 REPLACE 语句执行两个标准函数，DELETE和INSERT。

句法

以下是MySQL中REPLACE语句的语法：

REPLACE [INTO] table_name(column_list)  
VALUES(value_list);  

MySQL 替换示例

让我们通过一个例子来理解 MySQL 中 REPLACE 语句的工作原理。首先，我们将使用以下语句创建一个名为“Person”的表：

CREATE TABLE Person (  
  ID int AUTO_INCREMENT PRIMARY KEY,  
  Name varchar(45) DEFAULT NULL,  
  Email varchar(45) DEFAULT NULL UNIQUE,  
  City varchar(25) DEFAULT NULL  
);  

接下来，我们需要使用INSERT语句将记录填充到表中，如下所示：

INSERT INTO Person(ID, Name, Email, City)   
VALUES (1,'Mike', 'mike@javatpoint.com', 'California'),   
(2, 'Alexandar', 'alexandar@javatpoint.com', 'New York'),   
(3, 'Adam', 'adam@javatpoint.com', 'Los Angeles'),  
(4, 'Peter', 'Peter@javatpoint.com', 'Alaska');  

执行SELECT语句以验证可以在以下输出中显示的记录：

在将数据验证到表中后，我们可以使用 REPLACE 语句将任何旧行替换为新行。执行以下语句，更新id 为 4 的人的城市。

REPLACE INTO Person (id, city)  
VALUES(4,'Amsterdam');  

上述语句执行成功后，需要再次查询表Person的数据来验证替换。

在值name和email列空了。这是因为 REPLACE 语句的工作方式如下：

• 此语句首先尝试将新行插入到 Person 表中。但是插入新行失败，因为表中已经存在 id = 4。
• 所以这个语句首先删除 id = 4 的行，然后插入一个与阿姆斯特丹具有相同 id 和城市的新行。由于我们尚未指定 name 和 email 列的值，因此将其设置为 NULL。

MySQL REPLACE 语句更新一行

我们可以使用以下 REPLACE 语句将行数据更新到表中：

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

除了 REPLACE 关键字外，上述语法与UPDATE 语句类似。需要注意的是，我们不能在此语句中使用WHERE 子句。

执行以下示例，该示例使用 REPLACE 语句将名为Mike的人的城市从California 更新为Birmingham。

REPLACE INTO Person  
SET ID = 1,  
    Name = 'Mike',  
    City = 'Birmingham';

验证表后，我们可以看到如下输出：

如果我们没有在SET 子句中指定列的值，则此命令的工作方式类似于UPDATE 语句，这意味着 REPLACE 语句将使用该列的默认值。

MySQL REPLACE 从 SELECT 语句插入数据。

我们可以使用以下 REPLACE INTO 语句将数据插入到表中，并从查询返回数据。

REPLACE INTO table1(column_list)  
SELECT column_list  
FROM table2  
WHERE condition;  

需要注意的是，上面的 REPLACE 查询类似于INSERT INTO SELECT语句。执行以下示例，该示例使用 REPLACE INTO 语句复制同一表中的一行。

REPLACE INTO Person(Name, City)  
SELECT Name, City   
FROM Person WHERE id = 2;  

验证表后，我们将得到以下输出。在此输出中，我们可以看到成功添加了同一表中的行副本。