用过一段mysql的replace into 语句,期望在主键冲突,捕获到DuplicateKeyException的异常后,执行replace 覆盖。线上运行了1年多时间,没见到问题,据说会有问题,今天测试下。
测试表表明test_table,表结构为
mysql> show create table test_table;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------+
| Table | Create Table
|
+------------+--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`kk` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `kk_uq` (`kk`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
其中id是 auto_increment 自增、主键。
执行插入一条数据,只填写非自增的列。
mysql> select * from test_table;
+----+------+
| id | kk |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)
mysql> insert test_table(kk) values (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'kk_uq'
mysql> insert test_table(kk) values (2);
Query OK, 1 row affected (0.02 sec)
mysql> select * from test_table;
+----+------+
| id | kk |
+----+------+
| 1 | 1 |
| 3 | 2 |
+----+------+
2 rows in set (0.00 sec)
id字段跳过了2直接到了3,是什么原因呢?推测是自增的sequence被用掉了,后面就会跳过,而不管执行的insert是否成功。
再插入一条数据试试
mysql> insert test_table(kk) values (3);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_table;
+----+------+
| id | kk |
+----+------+
| 1 | 1 |
| 3 | 2 |
| 4 | 3 |
+----+------+
3 rows in set (0.00 sec)
id自增,从3变成了4。那么replace into是什么表现呢?
先执行插入,表现和insert into相同 (当目标表不存在要新增的记录时,和insert into相同)
mysql> replace into test_table (kk) values(4);
Query OK, 1 row affected (0.20 sec)
mysql> select * from test_table;
+----+------+
| id | kk |
+----+------+
| 1 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
+----+------+
4 rows in set (0.00 sec)
唯一约束相同的时候什么表现?
mysql> replace into test_table (kk) values(4);
<span style="color:#ff0000;">Query OK, 2 rows affected (0.12 sec)</span>
mysql> select * from test_table;
+----+------+
| id | kk |
+----+------+
| 1 | 1 |
| 3 | 2 |
| 4 | 3 |
| 6 | 4 |
+----+------+
4 rows in set (0.00 sec)
执行完后发现 id等于5的行丢掉了,同时新增了(6,4)新的行
replace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中, 1. 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。 2. 否则,直接插入新数据。
可以看到 MySQL 说 “2 rows affected”,可是明明是只写一条记录,为什么呢?这是因为 MySQL 在执行 REPLACE INTO auto (k) VALUES (1) 时首先尝试 但由于已经存在一条 kk=4的记录,发生了 duplicate key error,于是 MySQL 会先删除已有的那条 kk=4即 id=5的记录,然后重新写入一条新的记录。
下面一段文字引用自 http://www.jb51.net/article/39222.htmreplace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中, 1. 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。 2. 否则,直接插入新数据。
REPLACE的运行与INSERT很相似。只有一点例外,假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除。
注意,除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义。该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行。
继续试验,插入一条(6, 5),覆盖掉自增值:
mysql> replace into test_table (id,kk) values(6,5);
Query OK, 2 rows affected (0.01 sec)
mysql> select * from test_table;
+----+------+
| id | kk |
+----+------+
| 1 | 1 |
| 3 | 2 |
| 4 | 3 |
| 6 | 5 |
+----+------+
4 rows in set (0.00 sec)