①? 通过auto_increment关键字来指定自增的列,并指定自增列的初始值为1.
首先：表中自增列的上限是根据自增列的字段类型来定的.
若设定了自增id充当主键,当达到了自增id的上限值时,会发生什么样的事情呢?还是以上面创建的 t表为例, 先回顾它的表结构：
CREATETABLE +t+ (
+id+ int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (+id+)
QueryOK, 0 rows affected (0.01 sec)
Records:0? Duplicates: 0? Warnings: 0
QueryOK, 1 row affected (0.01 sec)
对于写入量大,且经常删除数据的表,自增id设为int类型还是偏小的,所以我们为了避免出现自增id涨满的情况,这边统一建议自增id的类型设为unsigned bingint,这样基本可以保障表的自增id是永远够用的.
这里内容比较多,innodb是索引组织表,所以涉及到索引的知识,但这不是本文的重点,我们快速回顾索引知识：
①? Innodb索引分为主键跟辅助索引,主键即全表,辅助索引叶子节点保存主键的值,而主键的叶子节点保存数据行,中间节点存着叶子节点的路由值.
其实能够理解索引的结构及索引写入插入、更新的原理,则自然就明白为何建议使用自增id.这里我直接列出使用自增id 当主键的好处吧：
①? 顺序写入,避免了叶的分裂,数据写入效率好
容易出现不连续的id
自增id的修改机制如下：
在MySQL里面,如果字段id被定义为AUTO_INCREMENT,在插入一行数据的时候,自增值的行为如下：
① 如果插入数据时id字段指定为0、null 或未指定值,那么就把这个表当前的
AUTO_INCREMENT值填到自增字段；
根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同.假设,某次要插入的值是X,当前的自增值是Y.
① 如果XY,那么这个表的自增值不变；
新的自增值生成算法是：从auto_increment_offset开始,以auto_increment_increment为步长,持续叠加,直到找到第一个大于X的值,作为新的自增值.
Insert、update、delete操作会让id不连续.
Delete、update：删除中间数据,会造成空动,而修改自增id值,也会造成空洞（这个很少）.
Insert：插入报错（唯一键冲突与事务回滚）,会造成空洞,因为这时候自增id已经分配出去了,新的自增值已经生成,如下面例子：
+----+------+
| id | name |
|? 1| aaa? |
+----------------+
| Auto_increment |
①. row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
①. row in set (0.01 sec)
可以看到,虽然事务回滚了,但自增id已经回不到从前啦,唯一键冲突也是这样的,这里就不做测试了.
在批量插入时（insert select等）,也存在空洞的问题.看下面实验：
①? 语句执行过程中,第一次申请自增id,会分配1个；
①.：mysql的默认设置.普通的insert语句申请后立马释放,insert select、replace insert、load data等批量插入语句要等语句执行结束后才释放,并发读得到提升
在彻底了解了MYSQL的自增机制以后,在实际生产中就能灵活避坑,这里建议不要用自增id值去当表的行数,当需要对大表准确统计行数时,可以去count(*)从库,如果业务很依赖大表的准确行数,直接弄个中间表来统计,或者考虑要不要用mysql的innodb来存储数据,这个是需要自己去权衡.另外对于要求很高的写入性能,但写入量又比较大的业务,自增id的使用依然存在热点写入的问题,存在性能瓶颈,这时候可通过分库分表来解决.
①.、清空表,恢复到初始值：
若修改的值与现有数据冲突,会自动修改为当前最大值+1.
设置自增列
MYSQL的自增列一定要是有索引的列,设置种子值要在表的后面设置
--mysql
-- 设置自增ID从N开始
CREATE TABLE empautoinc(
ID INT PRIMARY KEY AUTO_INCREMENT
) AUTO_INCREMENT = 100 ; --（设置自增ID从100开始）
insert into empautoinc(id) values(null);
mysql select * from empautoinc;
+-----+
| ID |
| 100 |
show table status like 'empautoinc'G;
*************************** 1. row ***************************
Name: empautoinc
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 101
Update_time: NULL
Check_time: NULL
Checksum: NULL
Create_options:
Comment:
设置自增列的步长,可以分为全局级别和会话级别
如果是会话级别,那么当用户新建一个会话的时候,那么步长又回到了全局级别,所以mysql的步长跟sqlserver的步长有很大的不同
mysql不能设置为 表级别 的步长