背景:某天,我老婆蛋,发了数据库的博文给我看,因为之前一直没有仔细研究过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> 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 |
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> 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=5 DEFAULT CHARSET=utf8 |
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的时候,字段要写全,防止老的字段数据被删除。