mysql易百_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 语句的工作原理如下：

在REPLACE 语句首先按语句中的列表中提供的值插入新行到 city 表。但插入失败，因为城市ID为2的数据记录在 city 表中已经存在，因此，MySQL引发重复键错误。

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 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语句来在表中插入或更新数据。