MySQL REPLACE 用法与实例

在 MySQL 中，如果你想向表中插入数据，除了使用 INSERT 语句，还可以使用 REPLACE 语句。

REPLACE 语句和 INSERT 语句很像，它们的不同之处在于，当插入过程中出现了重复的主键或者重复的唯一索引的时候，INSERT 语句会产生一个错误，而 REPLACE 语句则先删除旧的行，再插入新的行。

REPLACE 语句不在标准 SQL 的范畴。

REPLACE 语法

我们可以使用一个 REPLACE 语句插入一行或多行数据。 REPLACE 语句的语法如下：

REPLACE [INTO] table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),
       (value_21, value_22, ...)
       ...;

说明：

REPLACE INTO 和 VALUES 都是关键字。INTO 可省略。
REPLACE INTO 后跟表名 table_name。
表名 table_name 后跟要插入数据的列名列表。列名放在小括号中，多个列表使用逗号分隔。
VALUES 关键字之后的小括号中是值列表。值的数量要和字段的数量相同。值的位置和列的位置一一对应。
当插入多行数据时，多个值列表之间使用逗号分隔。

REPLACE 语句与 INSERT 语句类似。

REPLACE 语句还可以使用 SET 关键词，这只适用于操作单行。语法如下：

REPLACE [INTO] table_name
SET column1 = value1,
    column2 = value2,
    ...;

这种用法与 UPDATE 语句的相似，但也是不同的。 UPDATE 只更新符合条件的行的指定字段的值，未指定的字段保留原值。REPLACE 则会删掉旧行，再插入新行，REPLACE 语句中未指定的字段则为默认值或者 NULL。

如果想要正常使用 REPLACE，当前操作的用户必须对表具有 INSERT 和 DELETE 权限。

REPLACE 实例

为了演示 REPLACE 语句的用法，我们通过以下 CREATE TABLE 语句创建一个表命名为 user。如下：

CREATE TABLE user (
    id INT,
    name VARCHAR(255) NOT NULL,
    age INT,
    PRIMARY KEY (id)
);

这里，我们创建了一个 user 表，它包含 3 个字段： id, name, age，并且 id 字段为主键。

插入单行数据

我们通过以下语句向 user 表中插入 1 个新行：

REPLACE INTO user (id, name, age)
VALUES (1, "Jim", 18);

Query OK, 1 row affected (0.00 sec)

从输出中的 1 row affected 表示已经成功插入 1 行。我们也可以通过以下查询语句以验证操作：

SELECT * FROM user;

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Jim  |   18 |
+----+------+------+
1 row in set (0.01 sec)

插入多行数据

我们通过以下语句向 user 表中插入 2 个新行：

REPLACE INTO user (id, name, age)
VALUES (2, "Tim", 19), (3, "Lucy", 16);

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

从输出中的 2 row affected 表示已经成功插入 2 行。我们也可以通过以下查询语句以验证操作：

SELECT * FROM user;

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Jim  |   18 |
|  2 | Tim  |   19 |
|  3 | Lucy |   16 |
+----+------+------+
3 rows in set (0.00 sec)

替换单行数据

我们通过以下语句向 user 表中插入一个具有重复主键的行：

REPLACE INTO user (id, name, age)
VALUES (3, "Alice", 18);

Query OK, 2 rows affected (0.01 sec)

本例中，我们准备插入的行的 id 的值为 3。然而 user 表已经存在具有相同 id 的行。

与插入一个新行的输出不同，这里的输出是 2 rows affected，它表示删除了一行数据，又插入了一行数据。

我们也可以通过以下查询语句以验证操作：

SELECT * FROM user;

+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Jim   |   18 |
|  2 | Tim   |   19 |
|  3 | Alice |   18 |
+----+-------+------+
3 rows in set (0.00 sec)

替换和插入

我们通过以下语句向 user 表中插入一个新行并替换一个已有的行：

REPLACE INTO user (id, name, age)
VALUES (2, "James", 20), (4, "Kobe", 16);

Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

本例中，

3 rows affected 表示删除了一行，又插入了两行。
Records: 2 表示语句中有两行。
Duplicates: 1 表示重复了一行。