MySQL Replace语句

在本教程中，您将学习如何使用 MySQL的REPLACE语句来插入或更新数据库表中的数据。

MySQL REPLACE语句介绍

MySQL的REPLACE语句是一个MySQL扩展于SQL标准的语句。 MySQL REPLACE语句的工作原理如下：

• 如果新行已经存在，MySQL的REPLACE  语句插入一个新行；
• 如果新行不存在，则REPLACE  语句首先删除旧的行，然后插入一个新行。在某些情况下，REPLACE  语句只更新现有行； 

要确定新行是否已经存在于表中，MySQL使用PRIMARY KEY或唯一键（ UNIQUE KEY）索引。如果表中没有使用一个索引，REPLACE  语句相当于INSERT语句。

要使用MySQL REPLACE 语句，至少需要有插入和删除权限。

请注意，MySQL 中也有一个名称为 REPLACE()函数，它不是在本教程中所说的REPLACE语句。

MySQL REPLACE语句示例

让我们来看看如何使用REPLACE语句，这里使用一个例子以方便我们更好的理解它是如何工作。

首先，创建一个新表 city ，其定义语句如下：

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

接下来，插入一些行 数据到 city 表：

INSERT INTO  studymysql.city(name,population)
VALUES('上海', 18008278),
  ('广州',13694825),
  ('深圳',11223405);

我们从 city 表中查询数据，以验证插入操作。

SELECT * FROM  studymysql.city;

执行上面的查询，结果如下所示 - 

可以看到，在 city 表中有插入了三个城市。

然后，假设要更新广州市的人口为 11008256。我们可以使用 UPDATE语句如下：

UPDATE  studymysql.city
SET
    population=11008256
WHERE
    id=2;

我们执行上面的查询以验证更新结果，结果如下所示 - 

SELECT  * FROM studymysql.city;

如预期的那样，UPDATE语句已经更新了数据。

在此之后，使用REPLACE语句将广州市的人口更新为 13696820。

REPLACE INTO studymysql.city(id,population)
VALUES(2, 13696820);

最后，再次查询 city 表中的数据验证执行的结果。

SELECT  * FROM studymysql.city;

执行上面的查询，结果如下所示 - 

城市ID为2的 name 列的值现在是 NULL 。您可能会想到这个 name 列的值保持不变。然而，REPLACE 语句并不像想象的那样。在这种情况下，REPLACE 语句的工作原理如下：

1. 在REPLACE 语句首先按语句中的列表中提供的值插入新行到 city 表。但插入失败，因为城市ID为2的数据记录在 city 表中已经存在，因此，MySQL引发重复键错误。
2. REPLACE 语句然后将一个使用ID字段值指定的行记录更新，因为没有指定 name 字段的值，所以它使用了该列的默认值。在正常的执行过程中，它会先删除冲突ID的旧行，然后插入一个新行。

但是在这里我们知道，REPLACE语句并没有删除旧行再插入新行，因为这里 id 列的值是 2，而不是 4。

MySQL REPLACE 和 INSERT语句

REPLACE语句的第一种形式是类似INSERT 语句，除了关键字 INSERT 被替换 REPLACE 关键字如下：

REPLACE INTO table_name(column_list)
VALUES(value_list);

例如，如果想插入新行到 city 表中，可以使用下面的查询：

REPLACE INTO studymysql.city(name,population)
VALUES('海口', 1321523);

注意，REPLACE语句没有出现用于插入到相应列的列默认值。如果它是一个 NOT NULL属性并没有提供一个默认值，在REPLACE语句不指定列的值，MySQL将引发一个错误。这是REPLACE 和INSERT语句之间的差异。 

例如，在下面的语句中，我们仅指定name 列的值，但不指定 population  列的值。 MySQL将会引发错误消息。

由于population 列不接受 NULL 值，在定义 citie表结构时，我们没有为 population  指定一个默认值。

REPLACE INTO studymysql.city(name) VALUES('杭州');

这是 MySQL 发出的错误信息：

21:59:21    REPLACE INTO studymysql.city(name) VALUES('杭州')    Error Code: 1364. Field 'population' doesn't have a default value    0.000 sec

MySQL REPLACE 和UPDATE

REPLACE语句的第二种形式类似于UPDATE 语句如下：

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

请注意，在REPLACE语句中没有WHERE子句。

例如，如果想要将 海口 的人口更新为 1768980，您可以使用如下REPLACE语句： 

REPLACE INTO studymysql.city
SET id=4,
    name='海口',
    population=1768980;

不同于UPDATE 语句，如果不指定 SET 子句中的列对应的值，REPLACE 语句将使用该列的默认值。

SELECT  * FROM studymysql.city;

执行上面的SQL查询后，得到以下结果 - 

MySQL REPLACE INTO 和 SELECT

REPLACE 语句的第三种形式类似 INSERT INTO SELECT语句：

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

假设，要复制城市编号是1  的信息，使用REPLACE INTO SELECT语句，如下面的查询：

REPLACE INTO studymysql.city(name,population)
SELECT name,population FROM studymysql.city
WHERE id=1;

MySQL REPLACE语句用法

以下几个重要的地方在使用REPLACE语句您需要知道：

• 如果您开发不仅要支持MySQL数据库，还包括其他关系数据库管理系统（RDBMS）的应用程序，应该避免使用REPLACE语句，因为其他RDBMS可能不支持它。 可以在一个事务中使用 DELETE 和 INSERT 语句的组合。
• 如果使用REPLACE语句在有触发器并且有重复键错误发生时，触发器将在下列顺序自动删除表中数据：BEFORE INSERT  BEFORE DELETE , AFTER DELETE , AFTER INSERT 在 REPLACE 语句删除当前行并插入新行。当REPLACE语句更新当前行BEFORE UPDATE和AFTER UPDATE触发器被触发。

在本教程中，你已经学会了使用不同形式的 MySQL REPLACE语句来在表中插入或更新数据。

转载于:https://my.oschina.net/u/2263383/blog/749012