mysql怎么实现表自增 mysql设置自增列( 二 )

若设定了自增id充当主键，当达到了自增id的上限值时，会发生什么样的事情呢？还是以上面创建的 t表为例，先回顾它的表结构：
CREATETABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_binDEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
无符号的int类型，上限是2147483647 。这里我们将表的自增值设为2147483647，再插入两行数据：
[root@localhost][test1]alter table t auto_increment=2147483647;
QueryOK, 0 rows affected (0.01 sec)
Records:0Duplicates: 0Warnings: 0
[root@localhost][test1]insert into t(name) values ('test');
QueryOK, 1 row affected (0.01 sec)
[root@localhost][test1]insert into t(name) values ('test');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
可以看到，第一个插入没问题，因为自增列的值为2147483647 ，这是达到了上限，还没有超过，第二行数据插入时，则报出主键重复，在达到上限后，无法再分配新的更大的自增值，也没有从1开始从头分配，在这里表的auto_increment值会一直是2147483647 。
对于写入量大，且经常删除数据的表，自增id设为int类型还是偏小的，所以我们为了避免出现自增id涨满的情况，这边统一建议自增id的类型设为unsigned bingint，这样基本可以保障表的自增id是永远够用的。
这里内容比较多， innodb是索引组织表，所以涉及到索引的知识，但这不是本文的重点，我们快速回顾索引知识：
1.Innodb索引分为主键跟辅助索引，主键即全表，辅助索引叶子节点保存主键的值，而主键的叶子节点保存数据行，中间节点存着叶子节点的路由值。
2.Innodb存储数据（索引）的单位是页，这里默认是16K，这也意味着，数据本身越?。?一个页中能存数据的量越多，而检索效率不仅仅由索引的层数来决定，更是由一次能够缓存的数据量来定，也就是说数据本身越?。蛞淮蜪O能够提取到缓冲区的数据越多（OS每次IO的量是固定的4K），查询的效率越好。
其实能够理解索引的结构及索引写入插入、更新的原理，则自然就明白为何建议使用自增id 。这里我直接列出使用自增id 当主键的好处吧：
1.顺序写入，避免了叶的分裂，数据写入效率好
2.缩小了表的体积，特别是相比于UUID当主键，甚至组合字段当主键时，效果更明显
3.查询效率好，原因就是我上面说到索引知识的第二点。
4.某些情况下，我们可以利用自增id来统计大表的大致行数。
5.在数据归档or垃圾数据清理时，也可方便的利用这个id去操作，效率高。
容易出现不连续的id
有的同志会发现，自己的表中id值存在空洞，如类似于1、2、3、8、9、10这样，有的适合有想依赖于自增id的连续性来实现业务逻辑，所以会想方设法去修改id让其变的连续，其实，这是没有必要的，这一块的业务逻辑交由MySQL实现是很不理智的，表的记录小还好，要是表的数据量很大，修改起来就糟糕了。那么，为什么自增id会容易出现空洞呢？
自增id的修改机制如下：
在MySQL里面，如果字段id被定义为AUTO_INCREMENT ，在插入一行数据的时候，自增值的行为如下：
1. 如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的
AUTO_INCREMENT值填到自增字段；
2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。