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

¥ 我要打赏

纠错/补充

收藏

加QQ群啦，易百教程官方技术学习群

注意：建议每个人选自己的技术方向加群，同一个QQ最多限加 3 个群。