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)
mysql>
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)
这里我们插入一条主键已经存在的4的数据
mysql> replace into auto(id,k)values(4,5);
Query OK, 2 rows affected (0.01 sec)
mysql> select * from auto;
+----+---+------+---------+
| id | k | v | extra |
+----+---+------+---------+
| 2 | 2 | 2 | extra 2 |
| 3 | 3 | 3 | extra 3 |
| 4 | 5 | NULL | NULL |
+----+---+------+---------+
3 rows in set (0.00 sec)
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)
发现,auto_increment并没有+1,而是针对原来的那一条id=4的记录进行了update,因为没有指定其他列(v,extra)的值,所以,update的时候都使用了默认值.
mysql> select * from auto;
+----+---+------+---------+
| id | k | v | extra |
+----+---+------+---------+
| 2 | 2 | 2 | extra 2 |
| 3 | 3 | 3 | extra 3 |
| 4 | 5 | NULL | NULL |
| 5 | 6 | 6 | NULL |
+----+---+------+---------+
4 rows in set (0.00 sec)
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=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
mysql>
mysql> replace into auto(id,k,extra)values(5,6,77);
Query OK, 2 rows affected (0.01 sec)
mysql> select * from auto;
+----+---+------+---------+
| id | k | v | extra |
+----+---+------+---------+
| 2 | 2 | 2 | extra 2 |
| 3 | 3 | 3 | extra 3 |
| 4 | 5 | NULL | NULL |
| 5 | 6 | NULL | 77 |
+----+---+------+---------+
4 rows in set (0.00 sec)
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=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
我们发现,auto_increment也并没有+1,而是针对原来的那一条id=6的记录进行了update,因为没有指定其他列(v)的值,所以,update的时候都v使用了默认值变成了null
我们来看下:
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=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from auto;
+----+---+------+---------+
| id | k | v | extra |
+----+---+------+---------+
| 2 | 2 | 2 | extra 2 |
| 3 | 3 | 3 | extra 3 |
| 4 | 5 | NULL | NULL |
| 6 | 6 | 66 | NULL |
+----+---+------+---------+
4 rows in set (0.00 sec)
mysql> replace into auto(id,k,v)values(6,2,88);
Query OK, 3 rows affected (0.03 sec)
像上面的,主键id=6对应一条记录,唯一索引k=2对应id=2的另外一条记录,所以我们当前插入的记录就会跟2行数据有冲突,我们replace into 看看会有什么结果
mysql> replace into auto(id,k,v)values(6,2,88);
Query OK, 3 rows affected (0.03 sec)
mysql> select * from auto;
+----+---+------+---------+
| id | k | v | extra |
+----+---+------+---------+
| 3 | 3 | 3 | extra 3 |
| 4 | 5 | NULL | NULL |
| 6 | 2 | 88 | NULL |
+----+---+------+---------+
3 rows in set (0.00 sec)
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=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
我们发现auto_increment并没有+1,MySQL把原来的id=6的这条记录上进行uppdate,但是发现唯一索引k出现了冲突,所以就把对应冲突的那条数据删除,再进行更新,由于没有指定更新字段extra的数据,所以就把extra更新为默认数据
mysql> select * from auto;
+----+---+------+---------+
| id | k | v | extra |
+----+---+------+---------+
| 2 | 2 | 2 | extra 2 |
| 3 | 3 | 3 | extra 3 |
| 4 | 5 | NULL | NULL |
| 5 | 6 | NULL | 77 |
+----+---+------+---------+
4 rows in set (0.00 sec)
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=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
mysql> replace into auto(k,v)values(6,66);
Query OK, 2 rows affected (0.04 sec)
mysql> select * from auto;
+----+---+------+---------+
| id | k | v | extra |
+----+---+------+---------+
| 2 | 2 | 2 | extra 2 |
| 3 | 3 | 3 | extra 3 |
| 4 | 5 | NULL | NULL |
| 6 | 6 | 66 | NULL |
+----+---+------+---------+
4 rows in set (0.00 sec)
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=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
这时候,我们发现,,auto_increment已经+1了。MySQL这时候的执行步骤是,首先往表里面插入一条数据,这时候auto_increment+1,但是在插入的时候发现唯一索引的k冲突了,然后把冲突的这条数据删除,然后重新插入,对于没有指定其他列(extra)的值,如extra都使用了默认值变成了null
现在我们可以下结论了: