一.场景准备
测试场景为MySQL 8.0：
1、建表，包含主键及唯一约束
CREATE TABLE t1(id int(11) NOT NULL auto_increment,c1 varchar(64) DEFAULT NULL,c2 int(11) DEFAULT NULL,PRIMARY Key (id),UNIQUE KEY uk_c1 (c1)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、写入初始测试数据
insert into t1 (c1,c2) values ('a',1),('b',2),('c',3);mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | a|1 ||2 | b|2 ||3 | c|3 |+----+------+------+3 rows in set (0.00 sec)
二.开始测试
insert into
# 测试主键重复mysql> insert into t1 values (1,'aaa', 111);ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'# 测试唯一键重复mysql> insert into t1 (c1,c2) values('a', 4);ERROR 1062 (23000): Duplicate entry 'a' for key 't1.uk_c1'
insert ignore into
insert方式插入数据在处理过程中发生主键传统等错误时候，语句会被终止，并告知错误的原因。而使用insert ignore的方式进行数据插入，则会忽略插入错误的行继续插入没有问题的行记录，最终以warning进行提示。
# 测试主键重复mysql> insert ignore into t1 values (1,'aaa', 111);Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show warnings;+---------+------+------------------------------------------+| Level| Code | Message|+---------+------+------------------------------------------+| Warning | 1062 | Duplicate entry '1' for key 't1.PRIMARY' |+---------+------+------------------------------------------+1 row in set (0.01 sec)# 测试唯一键重复mysql> insert ignore into t1 (c1,c2) values('a', 4);Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show warnings;+---------+------+----------------------------------------+| Level| Code | Message|+---------+------+----------------------------------------+| Warning | 1062 | Duplicate entry 'a' for key 't1.uk_c1' |+---------+------+----------------------------------------+1 row in set (0.00 sec)
在测试过程中惊奇地发现测试表中的主键自增列发生了改变，经过之前的操作已经变成了7：
mysql> show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT,`c1` varchar(64) DEFAULT NULL,`c2` int DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uk_c1` (`c1`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb31 row in set (0.00 sec)# 可是表的行数据并没有增加mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | a|1 ||2 | b|2 ||3 | c|3 |+----+------+------+3 rows in set (0.00 sec)# 新写入一条数据后，自增 id 变成 7mysql> insert into t1 (c1,c2) values('d', 4);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | a|1 ||2 | b|2 ||3 | c|3 ||7 | d|4 |+----+------+------+4 rows in set (0.00 sec)
replace into
最后，replace into的方式导致如果插入数据是原值的情况，然后主键冲突，就对该主键的内容进行替换，如果唯一键冲突，唯一值所在行就会删除，重新插入新的行，如果都不冲突则正常插入数据。
# 测试主键重复mysql> replace into t1 values (1,'aaa', 111);Query OK, 2 rows affected (0.00 sec)mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | aaa|111 ||2 | b|2 ||3 | c|3 ||7 | d|4 |+----+------+------+4 rows in set (0.00 sec)# 测试唯一键重复mysql> replace into t1 (c1,c2) values('b', 4);Query OK, 2 rows affected (0.01 sec)mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | aaa|111 ||3 | c|3 ||7 | d|4 ||8 | b|4 |+----+------+------+4 rows in set (0.00 sec)
上文测试了三种插入数据的方式，可是测试过程中发现插入失败的时候，自增列的自增值居然变大了。
三.问题分析
为了更好地理解，首先让我们具体认识一下AUTO_INCREMENT属性在不同的存储引擎当中，其自增值的保存策略有所不同：
可是理解了这个并不能马上理解现在的这个问题，我们知道当数据进行数据插入的时候，如果插入的数据中自增列不指定其值的时候，该列就会以当前自增值作为其值，如果指定其值就会插入指定的值，当然也有满足唯一的原则，同时插入指定值大于自增值时，自增值也会随之改变。而自增值使用的算法是以auto_increment_offset参数决定开始，以auto_increment_increment决定步长来实现的,默认情况都是1：
mysql> select @@auto_increment_offset;+-------------------------+| @@auto_increment_offset |+-------------------------+|1 |+-------------------------+1 row in set (0.00 sec)mysql> select @@auto_increment_increment;+----------------------------+| @@auto_increment_increment |+----------------------------+|1 |+----------------------------+1 row in set (0.00 sec)
那么，为什么会出现插入数据未成功，自增值却变大了的情况呢？原因很简单，用插入数据的流程来进行分析：
因为自增值的保存是在插入数据真正执行前完成的，因此就会出现这种问题了。
这个时候有人就会想了，可以把AUTO_INCREMENT值改回去吗？简单测试一下：
mysql> show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT,`c1` varchar(64) DEFAULT NULL,`c2` int DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uk_c1` (`c1`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb31 row in set (0.00 sec)mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | a|1 ||3 | c|3 ||7 | d|4 ||8 | b|4 |+----+------+------+4 rows in set (0.00 sec)# 自增值修改为15mysql> alter table t1 auto_increment = 15;Query OK, 0 rows affected (0.01 sec)Records: 0Duplicates: 0Warnings: 0# 修改成功mysql> show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT,`c1` varchar(64) DEFAULT NULL,`c2` int DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uk_c1` (`c1`)) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb31 row in set (0.00 sec)# 未插入任何值，修改回去，修改成功mysql> alter table t1 auto_increment = 9;Query OK, 0 rows affected (0.02 sec)Records: 0Duplicates: 0Warnings: 0mysql> show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT,`c1` varchar(64) DEFAULT NULL,`c2` int DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uk_c1` (`c1`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb31 row in set (0.00 sec)# 修改回自增中间的值mysql> alter table t1 auto_increment = 5;Query OK, 0 rows affected (0.01 sec)Records: 0Duplicates: 0Warnings: 0# 不会报错但无法修改mysql> show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT,`c1` varchar(64) DEFAULT NULL,`c2` int DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uk_c1` (`c1`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb31 row in set (0.00 sec)
显然，如果自增值往大的方向修改是没有问题的，但如果往小的修改就要看目前数据库插入的值是否会将修改后的自增值“卡”在中间，如果出现这种情况是没办法改回去的，原因显而易见，自增属性与主键配套使用，如果现在表里id=4和id=6之间差了个5的值，将自增值改回5，当插入数据时，自增值就会插入5的值并且把自增值加1，问题就出现了，此时自增值再进行插入就违背了唯一的原则了
四.问题拓展
在生产环境中还存在很多类似的问题，如：
# 目前的插入值为8，自增值为9mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | a|1 ||3 | c|3 ||7 | d|4 ||8 | b|4 |+----+------+------+4 rows in set (0.00 sec)# 插入数据相当于(9,'t1', 1)mysql> insert into t1 values (null,'t1', 1);Query OK, 1 row affected (0.00 sec)# 开启事务mysql> begin;Query OK, 0 rows affected (0.00 sec)# 插入数据相当于(10,'t2', 2)mysql> insert into t1 values (null,'t2', 2);Query OK, 1 row affected (0.00 sec)# 事务回滚mysql> rollback;Query OK, 0 rows affected (0.00 sec)# 插入数据相当于(11,'t3', 3)mysql> insert into t1 values (null,'t3', 3);Query OK, 1 row affected (0.01 sec)mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | a|1 ||3 | c|3 ||7 | d|4 ||8 | b|4 ||9 | t1|1 || 11 | t3|3 |+----+------+------+6 rows in set (0.00 sec)
在插入过程中，开启了一个事务，在插入的时候发生了事务的回滚，当回滚后再次插入数据，发现自增值又出现了“空洞”，那么问题又来了，为什么在插入数据的时候发生了回滚，数据回滚了，自增值却没有回滚呢？为了更直观，继续测试，假设有两个事务。
测试前数据：
mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | a|1 ||3 | c|3 ||7 | d|4 ||8 | b|4 ||9 | t1|1 || 11 | t3|3 || 13 | t4|3 |+----+------+------+7 rows in set (0.00 sec)
进行测试：
|
insert into t1 values (null,’s1′, 1); |
|
insert into t1 values (null,’s2′, 2); |
测试后数据：
mysql> select * from t1;+----+------+------+| id | c1| c2|+----+------+------+|1 | a|1 ||3 | c|3 ||7 | d|4 ||8 | b|4 ||9 | t1|1 || 11 | t3|3 || 13 | t4|3 || 15 | s2|2 |+----+------+------+8 rows in set (0.00 sec)
发现还是“空洞”了，而且此时答案也十分清楚了，在不同事务在进行写入操作的时候申请自增值，为了避免两个事务申请到相同的自增值，所以需要对其加锁，按照一定顺序进行申请自增值。根据前面的例子来看：
此时就出现了前面说到的问题了，没办法回滚，回滚就会出现自增值“卡”在中间的情况了，以后有机会再继续聊聊自增锁的问题。
SQL自增字段，有数据删除后，如何实现自增字段的连续
对于自增字段确实有这个问题，也无法改变，这是由于自增字段的值是内部计算，每使用一次都会自动+1，有点类似线序，你可以使用如下两种方法解决：1、自增字段改为不用手工增加，每次都取最大值+1来存储2、不改自增字段类型，采用逻辑删除的方法，比如在表中增加一个字段isdel（1表示已删除，0或者其它值表示没有删除），记录当前记录是否属于删除状态，
找到主键的最后一条记录 再添加一条记录该怎么写 ，主键是不自动增长的
先用MAX函数找到最后一条记录的主键，然后插入MAX+1
sql server 怎么建立联合主键？
主键是数据库表的一个重要属性，建立主键可以避免表中存在完全相同的记录，也就是说主键在一张表中的记录值是唯一的。 建立主键有两种方法：一种是在数据库提供的GUI环境中建立，另一种是通过SQL语句执行建立，下面分别介绍。 1.在数据库提供的GUI环境中建立（以SQL7为例）。 输入表信息后按Ctrl键同时选中多行，然后点上面的主键按钮就行了。 2.通过SQL语句执行建立。 又分两种，一是在建表语句中直接写，二是建表之后更改表结构。 在建表语句中直接写：Create Table 表名 (字段名1 Int Not Null, 字段名2 nvarchar(13) Not Null Primary Key (字段名1, 字段名2), 字段名3………… 字段名N………… )建表之后更改表结构： CREATE TABLE 表名 (字段名1 Int Not Null,字段名2 nvarchar(13) Not Null字段名3…………字段名N…………) GOALTER TABLE 表名 WITH NOCHECK ADDCONSTRAINT [PK_表名] PRIMARY KEYNONCLUSTERED ( [字段名1], [字段名2]) GO可以参考一下，相关的资料网上很多。
发表评论