MySQL的Replace into 与Insert into ..... on duplicate key update

来源：互联网发布：mac flamingo 珊瑚编辑：程序博客网时间：2024/04/29 09:58

背景：某天，我老婆蛋，发了数据库的博文给我看，因为之前一直没有仔细研究过replace into 与 on duplicate key update区别，此次燃起了我的好奇心，故作了验证了。

以下是在mysql 5.1.50验证的

1. 建表：

mysql> CREATE TABLE `check_t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` varchar(10) DEFAULT NULL,
`c` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2. 插入数据：

insert into check_t1(b,c) values('b1','c1'),('b2','c2'),('b3','c3');

mysql> show create table check_t1;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| check_t1 | CREATE TABLE `check_t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` varchar(10) DEFAULT NULL,
`c` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. replace into：

mysql> replace into check_t1(a,b) value(1,'a11');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from check_t1;
+---+------+----+
| a | b | c |
+---+------+----+
| 1 | a11 | |
| 2 | b2 | c2 |
| 3 | b3 | c3 |
+---+------+----+

mysql> insert into check_t1(b,c) value('b4','c4');
Query OK, 1 row affected (0.00 sec)

mysql> select * from check_t1;
+---+------+----+
| a | b | c |
+---+------+----+
| 1 | a11 | |
| 2 | b2 | c2 |
| 3 | b3 | c3 |
| 4 | b4 | c4 |
+---+------+----+

所以当与主键冲突时，replace覆盖相关字段，其它字段填充默认值，可以理解为删除重复key的记录，新插入一条记录，一个delete原有记录再insert的操作，但自增长值不变。

4.insert .. on deplicate update

mysql> insert into check_t1(a,b) value(3,'b33') on duplicate key update b='b33';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from check_t1;
+---+------+----+
| a | b | c |
+---+------+----+
| 1 | a11 | |
| 2 | b2 | c2 |
| 3 | b33 | c3 |
| 4 | b4 | c4 |
+---+------+----+

由此可看当主键有时，只执行后面的udate操作语句，可以理解为删除重复key的记录，新插入一条记录，一个delete原有记录再insert的操作，但自增长值不变。

5.总结：

insert .. on deplicate udpate保留了所有字段的旧值，再覆盖然后一起insert进去，而replace没有保留旧值，直接删除再insert新值。从底层执行效率上来讲，replace要比insert .. on deplicate update效率要高，但是在写replace的时候，字段要写全，防止老的字段数据被删除。

0 0