MySQL REPLACE INTO语句

介绍

在向表中插入数据时，我们经常会：首先判断数据是否存在；如果不存在，则插入；如果存在，则更新。

但在 MySQL 中有更简单的方法，replace into（insert into 的增强版），当表中的旧行与 PRIMARY KEY或 UNIQUE 索引的新行具有相同的值，则在插入新行之前删除旧行，否则只就新增插入。

REPLACE 它的工作方式与 INSERT 完全相同，它是 SQL 标准的 MySQL 扩展。它要么插入，要么删除和插入。

REPLACE仅当表具有PRIMARY KEY或 UNIQUE索引时才有意义。否则，它就等于INSERT，因为没有索引可用于确定新行是否与另一行重复。
要使用 REPLACE，必须同时拥有表的INSERT和 DELETE权限。

REPLACE INTO用法

作用

当表中的旧行与 PRIMARY KEY或 UNIQUE 索引的新行具有相同的值，则在插入新行之前删除旧行，否则就只新增插入。

语法

REPLACE INTO 表名 (字段名, ...)  values(字段值, ...);

insert into 和 replace into 的区别

使用 insert into时，当表中的旧行与 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，就会插入失败。

而使用 replace into，会完成记录替换。

示例

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

创建 test 表，运行以下语句：

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

创建第二张表与第一张表几乎相同，但主键现在是覆盖两列，如下所示：

CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);

当我们在test表上运行test2相同的两个REPLACE语句时，我们得到了不同的结果：

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

这是因为，当运行 REPLACE INTO 时，test2表中，列值id和ts列值，必须与要替换行的现有行的值进行相匹配；否则，插入一行。

MySQL REPLACE函数：字符串替换使用关联子查询解决组内筛选的过程