①? 通过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的使用依然存在热点写入的问题,存在性能瓶颈,这时候可通过分库分表来解决.
①主键语法
①创建时：create table sc (
studentno int,
courseid int,
score int,
primary key (studentno) );
②修改时：ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(列名);
前提是原先没有设置主键.
foreign key (courseid) );
②修改时：
ALTER TABLE news_info[子表名] ADD CONSTRAINT FK_news_info_news_type[约束名] FOREIGN KEY (info_id)[子表列] REFERENCES news_type[主表名] (id)[主表列] ;
如果一列不能唯一区分一个表里的记录时,可以考虑多个列组合起来达到区分表记录的唯一性,形式
primary key (studentno,courseid) );
前提是原来表中没有设置主键,若原先已有主键则会报错.
--?创建表
+code+?varchar(100)?DEFAULT?NULL,
--?添加主键
①.、创建表时指定AUTO_INCREMENT自增值的初始值（即起始值）:
ALTER TABLE XXX AUTO_INCREMENT=100;
即使在你将整个表中的所有数据delete清空后,之前的自增序列最大值还是会被重用.
解决办法是:
使用 ALTER TABLE XXX AUTO_INCREMENT=0; 重新设置自增初始值.
①.、清空表,恢复到初始值：
若修改的值与现有数据冲突,会自动修改为当前最大值+1.
第一段：修改自增长序列的值
alter table table_name auto_increment=n;
注意：n只能大于已有的auto_increment的整数值,小于的值无效.
show table status like 'table_name' 的返回结果里的auto_increment列就是表的现有值.
第二段：控制主键的起点
create table 表名
(
......
) engine=INNODB auto_increment=1001 default charset=gbk;
第三段：自增主键归零
如果曾经的数据都不需要的话,可以直接清空所有数据,并将自增字段恢复从1开始计数
truncate table 表名
第四段：获取自增主键
通过SQL select LAST_INSERT_ID()函数
通过SQL @@IDENTITY 变量
第五段：说明
AUTO_INCREMENT数据列必须有唯一索引,以避免序号重复.
AUTO_INCREMENT数据列必须具备NOT NULL属性.
设置AUTO_INCREMENT属性的数据列应该是一个正数序列,所以应该把该数据列声明为UNSIGNED,这样序列的编号个可增加一倍.