MySQL REPLACE 用法与实例

在本文中,我们讨论了在 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 INTOVALUES 都是关键字。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,当前操作的用户必须对表具有 INSERTDELETE 权限。

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 表示重复了一行。

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

SELECT * FROM user;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Jim   |   18 |
|  2 | James |   20 |
|  3 | Alice |   18 |
|  4 | Kobe  |   16 |
+----+-------+------+
4 rows in set (0.00 sec)

结论

在本文中,我们讨论了在 MySQL 中使用 REPLACE 语句将数据插入到表中。 REPLACE 语句要点如下:

  • REPLACE 语句与 SELECT 用法类似,用于插入一行或多行数据到表中。
  • 如果插入的数据存在重复的主键或者重复的唯一索引,REPLACE 语句将先删除旧行,再插入新行。
  • REPLACE 语句还可以使用 SET 关键词插入或者替换一个数据行。