使用方法:

CREATE TABLE test
(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(15) NOT NULL
)
AUTO_INCREMENT = 100;

在数据库应用我们经常要用到唯编号以标识记录在MySQL中可通过数据列AUTO_INCREMENT属性来自动生成MySQL支持多种数据表每种数据表自增属性都有差异这里将介绍各种数据表里数据列自增属性

ISAM表

如果把个NULL插入到个AUTO_INCREMENT数据列里去MySQL将自动生成下个序列编号编号从1开始并1为基数递增

把0插入AUTO_INCREMENT数据列效果和插入NULL值样但不建议这样做还是以插入NULL值为好

当插入记录时没有为AUTO_INCREMENT明确指定值则等同插入NULL值

当插入记录时如果为AUTO_INCREMENT数据列明确指定了个数值则会出现两种情况情况如果插入值和已有编号重复则会出现出错信息AUTO_INCREMENT数据列值必须是唯；情况 2如果插入值大于已编号值则会把该插入到数据列中并使在下个编号将从这个新值开始递增也就是说可以跳过些编号

如果自增序列最大值被删除了则在插入新记录时该值被重用

如果用UPDATE命令更新自增列如果列值和已有值重复则会出错如果大于已有值则下个编号从该值开始递增

如果用replace命令基于AUTO_INCREMENT数据列里值来修改数据表里现有记录即AUTO_INCREMENT数据列出现在了replace命令where子句里相应AUTO_INCREMENT值将不会发生变化但如果replace命令是通过其它PRIMARY KEY OR UNIQUE索引来修改现有记录(即AUTO_INCREMENT数据列没有出现在replace命令where子句中)相应AUTO_INCREMENT值--如果设置其为NULL(如没有对它赋值)话--就会发生变化

last_insert_id可获得自增列自动生成最后个编号但该只和服务器本次会话过程中生成值有关如果在和服务器本次会话中尚未生成AUTO_INCREMENT值则该返回0

其它数据表自动编号机制都以ISAM表中机制为基础

MyISAM数据表

删除最大编号记录后该编号不可重用

可在建表时可用“AUTO_INCREMENT=n”选项来指定个自增值

可用alter table table_name AUTO_INCREMENT=n命令来重设自增起始值

可使用复合索引在同个数据表里创建多个相互独立自增序列具体做法是这样:为数据表创建个由多个数据列组成PRIMARY KEY OR UNIQUE索引并把AUTO_INCREMENT数据列包括在这个索引里作为它最后个数据列这样这个复合索引里前面那些数据列每构成种独无 2组合最末尾AUTO_INCREMENT数据列就会生成个和该组合相对应序列编号

HEAP数据表

HEAP数据表从MySQL4.1开始才允许使用自增列

自增值可通过CREATE TABLE语句 AUTO_INCREMENT=n选项来设置

可通过ALTER TABLE语句AUTO_INCREMENT=n选项来修改自增始初值

编号不可重用

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

BDB数据表

不可通过CREATE TABLE OR ALTER TABLEAUTO_INCREMENT=n选项来改变自增值

可重用编号

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

InnDB数据表

不可通过CREATE TABLE OR ALTER TABLEAUTO_INCREMENT=n选项来改变自增值

不可重用编号

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

在使用AUTO_INCREMENT时应注意以下几点:

AUTO_INCREMENT是数据列种属性只适用于整数类型数据列

设置AUTO_INCREMENT属性数据列应该是个正数序列所以应该把该数据列声明为UNSIGNED这样序列编号个可增加倍

AUTO_INCREMENT数据列必须有唯索引以避免序号重复

AUTO_INCREMENT数据列必须具备NOT NULL属性

AUTO_INCREMENT数据列序号最大值受该列数据类型约束如TINYINT数据列最大编号是127,如加上UNSIGNED则最大为255旦达到上限AUTO_INCREMENT就会失效

当进行全表删除时AUTO_INCREMENT会从1重新开始编号全表删除意思是发出以下两条语句时:

delete from table_name;ortruncate table table_name
这是进行全表操作时MySQL实际是做了这样优化操作:先把数据表里所有数据和索引删除然后重建数据表如果想删除所有数据行又想保留序列编号信息可这样用个带wheredelete命令以抑制MySQL优化:

delete from table_name where 1;
这将迫使MySQL为每个删除数据行都做次条件表达式求值操作

强制MySQL不复用已经使用过序列值思路方法是:另外创建个专门用来生成AUTO_INCREMENT序列数据表并做到永远不去删除该表记录当需要在主数据表里插入条记录时先在那个专门生成序号表中插入个NULL值以产生个编号然后在往主数据表里插入数据时利用LAST_INSERT_ID取得这个编号并把它赋值给主表存放序列数据列如:

insert o id id = NULL;insert o _id = LAST_INSERT_ID;
可用alter命令给个数据表增加个具有AUTO_INCREMENT属性数据列MySQL会自动生成所有编号

要重新排列现有序列编号最简单思路方法是先删除该列再重建该MySQL会重新生连续编号序列

在不用AUTO_INCREMENT情况下生成序列可利用带参数LAST_INSERT_ID如果用个带参数LAST_INSERT_ID(expr)去插入或修改个数据列紧接着又不带参数LAST_INSERT_ID则第 2次返回就是expr值下面演示该思路方法具体操作:

先创建个只有个数据行数据表:create table seq_table (id unsigned not null);insert o seq_table values (0);接着用以下操作检索出序列号:


update seq_table seq = LAST_INSERT_ID( seq + 1 );select LAST_INSERT_ID;
通过修改seq+1中常数值可生成区别步长序列如seq+10可生成步长为10序列
该思路方法可用于计数器在数据表中插入多行以记录区别计数值再配合LAST_INSERT_ID返回值生成区别内容计数值这种思路方法优点是不用事务或LOCKUNLOCK表就可生成唯序列编号不会影响其它客户正常表操作