星期六, 2018-02-24 16:09 — adminshiping1

新建一个test表，三个字段，id，title，uid, id是自增的主键，uid是唯一索引；

插入两条数据

   test(title,uid)  (,   test(title,uid)  (,);

执行单条插入数据可以看到，执行结果如下：

[SQL]insert into  test(title,uid) VALUES ('123465','1001');
受影响的行: 1
时间: 0.175s

使用 replace into插入数据时：

  test(title,uid)  (,);

执行结果：
[SQL]REPLACE INTO test(title,uid) VALUES ('1234657','1003');
受影响的行: 1
时间: 0.035s

当前数据库test表所有数据如下：

当uid存在时，使用replace into 语句

REPLACE INTO test(title,uid) VALUES ('1234657','1001');

[SQL]REPLACE INTO test(title,uid) VALUES ('1234657','1001');
受影响的行: 2
时间: 0.140s

replace into t(id, update_time) values(1, now());

或

replace into t(id, update_time) select 1, now();

replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

MySQL replace into 有三种形式：

1. replace into tbl_name(col_name, ...) values(...)

2. replace into tbl_name(col_name, ...) select ...

3. replace into tbl_name set col_name=value, ...

第一种形式类似于insert into的用法，

第二种replace select的用法也类似于insert select，这种用法并不一定要求列名匹配，事实上，MYSQL甚至不关心select返回的列名，它需要的是列的位置。例如，replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2;?这个例子使用replace into从?tb2中将所有数据导入tb1中。

第三种replace set用法类似于update set用法，使用一个例如“SET col_name = col_name + 1”的赋值，则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。

前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

来自 https://www.cnblogs.com/c-961900940/p/6197878.html

MySQL中replace into语句的用法详解

转载 2014-08-26 投稿：junjie 我要评论

这篇文章主要介绍了MySQL中replace into语句的用法详解,本文讲解了replace into语句的多种写法,replace into语句的作用等内容,需要的朋友可以参考下

在向表中插入数据的时候，经常遇到这样的情况：

1、首先判断数据是否存在；
2、如果不存在，则插入；
3、如果存在，则更新。

在 SQL Server 中可以这样写：

复制代码代码如下:

if not exists (select 1 from table where id = 1) insert into table(id, update_time) values(1, getdate()) else update table set update_time = getdate() where id = 1

在MySQL 中也可以先select，判断是否存在，存在则 update 否则 insert
但在MySQL 中有更简单的方法，使用 replace into关键字

复制代码代码如下:

replace into table(id, update_time) values(1, now());

或

复制代码代码如下:

replace into table(id, update_time) select 1, now();

replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中。

1、如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
2、否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

MySQL中replace into有三种写法：

复制代码代码如下:

replace into table(col, ...) values(...)
replace into table(col, ...) select ...
replace into table set col=value, ...

前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。
另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

可惜的是replace不支持update某些特性，也就不能直接当作update使用：

常见update写法：update table set col=col+1 where id=1;
使用replace into不支持这样的写法：replace into table set col=col+1,id=1;

1、首先判断数据是否存在；（没问题）
2、如果不存在，则插入；（没问题）
3、如果存在，某字段值在原来的基础上加上或减去某个数，如加一操作。（不支持）

来自 http://www.jb51.net/article/54345.htm

MySQL "replace into" 的坑

MySQL 对 SQL 有很多扩展，有些用起来很方便，但有一些被误用之后会有性能问题，还会有一些意料之外的副作用，比如 REPLACE INTO。

比如有这样一张表：

CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoD

auto 表有一个自增的 id 字段作为主键，字段 k 有 UNIQUE KEY 做唯一性约束。写入几条记录之后会是这样：

xupeng@diggle7:3600(dba_m) [dba] mysql> INSERT INTO auto (k, v, extra) VALUES (1, '1', 'extra 1'), (2, '2', 'extra 2'), (3, '3', 'extra 3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  1 | 1 | 1    | extra 1 |
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
+----+---+------+---------+
3 rows in set (0.00 sec)

在 slave 节点上是和 master 一致的：

xupeng@diggle8:3600(dba_s) [dba] mysql> SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  1 | 1 | 1    | extra 1 |
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
+----+---+------+---------+
3 rows in set (0.00 sec)

xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

可以看到，写入三条记录之后，auto 表的 AUTO_INCREMENT 增长为 4，也就是说下一条不手工为 id 指定值的记录，id 字段的值会是 4。

接下来使用 REPLACE INTO 来写入一条记录：

xupeng@diggle7:3600(dba_m) [dba] mysql> REPLACE INTO auto (k, v) VALUES (1, '1-1');
Query OK, 2 rows affected (0.01 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 1 | 1-1  | NULL    |
+----+---+------+---------+
3 rows in set (0.00 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

可以看到 MySQL 说 “2 rows affected”，可是明明是只写一条记录，为什么呢？这是因为 MySQL 在执行 REPLACE INTO auto (k) VALUES (1) 时首先尝试 INSERT INTO auto (k) VALUES (1)，但由于已经存在一条 k=1 的记录，发生了 duplicate key error，于是 MySQL 会先删除已有的那条 k=1 即 id=1 的记录，然后重新写入一条新的记录。

这时候 slave 上出现了诡异的问题：

xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

可以知道，当前表内数据 id 字段的最大值是 4，AUTO_INCREMENT 应该为 5，但在 slave 上 AUTO_INCREMENT 却并未更新，这会有什么问题呢？把这个 slave 提升为 master 之后，由于 AUTO_INCREMENT 比实际的 next id 还要小，写入新记录时就会发生 duplicate key error，每次冲突之后 AUTO_INCREMENT += 1，直到增长为 max(id) + 1 之后才能恢复正常：

xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (4, '4');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (5, '5');
Query OK, 1 row affected (0.00 sec)

xupeng@diggle8:3600(dba_s) [dba] mysql> SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 1 | 1-1  | NULL    |
|  5 | 5 | 5    | NULL    |
+----+---+------+---------+
4 rows in set (0.00 sec)

没有预料到 MySQL 在数据冲突时实际上是删掉了旧记录，再写入新记录，这是使用 REPLACE INTO 时最大的一个误区，拿之前的例子来说，执行完 REPLACE INTO auto (k, v) VALUES (1, ‘1-1’) 之后，由于新写入记录时并未给 extra 字段指定值，原记录 extra 字段的值就「丢失」了，而通常这并非是业务上所预期的，更常见的需求实际上是，当存在 k=1 的记录时，就把 v 字段的值更新为 ‘1-1’，其他未指定的字段则保持原状，而满足这一需求的 MySQL 方言是 INSERT INTO auto (k, v) VALUES (1, ‘1-1’) ON DUPLICATE KEY UPDATE v=VALUES(v);

鉴于此，很多使用 REPLACE INTO 的场景，实际上需要的是 INSERT INTO … ON DUPLICATE KEY UPDATE，在正确理解 REPLACE INTO 行为和副作用的前提下，谨慎使用 REPLACE INTO。

来自 https://www.cnblogs.com/monian/archive/2014/10/09/4013784.html

普通分类:

mysql

You are here

MYSQL 中 replace into 的用法有大用

MySQL中replace into语句的用法详解

MySQL "replace into" 的坑

友情链接

搜索表单

用户登录

You are here

MYSQL 中 replace into 的用法 有大用

MySQL中replace into语句的用法详解

MySQL "replace into" 的坑

友情链接

MYSQL 中 replace into 的用法有大用