Mysql 的 auto_increment 详解

曾经建了个Mysql的数据库，用的是ISAM，结果发现auto_increment自动增加序号，最大值被删除了，则在插入新记录时，该值被重用。当时没怎么注意，后来应用时感觉头大了。看了看帖子，原来MySQL支持多种存储引擎，每种存储引擎的自增属性都有差异。

一、auto_increment使用方法
1.创建table时设置auto_increment属性和初始值100
create table nonove
(
       id bigint unsigned not null primary key auto_increment,
       value   text NOT NULL
)
auto_increment = 100;

2.修改table的auto_increment初始值
alter table table_name auto_increment=n

二、特性
Mysql存储引擎主要有MyISAM、InnoDB、BDB、ISAM、MERGE、HEAP、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。其中常用的是MyISAM和InnoDB。
下面说说常用的几种存储引擎auto_increment的特性：见下表（仅供参考）

	编号是否可以重用	是否可以设置和修改初始值	是否支持复合索引
MyISAM	可重用	可以	是
InnoDB	不可重用	不可以	否
HEAP(Memory)	不可重用	可以	否
BerkeleyDB(BDB)	可重用	不可以	是

1.MyISAM

删除最大编号的记录后，该编号不可重用，可设定和修改初始值。

可使用复合索引在同一个数据表里创建多个相互独立的自增序列。

2.InnDB

不可重用编号，不可设置和修改初始值。

不支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。

3.HEAP (Memory)
HEAP数据表从MySQL4.1开始才允许使用自增列，编号不可重用，可设置和修改初始值。
HEAP数据表不支持在一个数据表中使用复合索引来生成多个互不干扰的序列编号。

4.BDB
可重用编号，不可改变自增初始值。
支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。

三、一些使用技巧
1.设置AUTO_INCREMENT属性的数据列是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序列的编号个可增加一倍，同时一般配合使用bigint，数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。

2.全表删除时，编号重新从1开始。全表删除相当于把表删掉，重新创建了一个，所以编号自然地重新计数。全表删除语句：
       delete from table_name;
       ortruncate table table_name;
如果想删除所有的数据行又想保留序列编号信息，可以 delete from table_name where 1;

3.在不用AUTO_INCREMENT的情况下生成序列，可利用带参数的LAST_INSERT_ID()函数。如果用一个带参数的 LAST_INSERT_ID(sn)去插入或修改一个数据列，紧接着又调用不带参数的LAST_INSERT_ID()函数，则第二次函数调用返回的就是sn的值。
例子：先创建一个只有一个数据行的数据表：create table seq_table (id int unsigned not null);insert into seq_table values (0);接着用以下操作检索出序列号：update seq_table set seq = LAST_INSERT_ID( seq + 1 );select LAST_INSERT_ID();通过修改seq+1中的常数值，可生成不同步长的序列，如seq+10可生成步长为10的序列。

该方法可用于计数器，在数据表中插入多行以记录不同的计数值。再配合LAST_INSERT_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK，UNLOCK表就可生成唯一的序列编号。不会影响其它客户程序的正常表操作。