mysql自动序列生成

MySQL AUTO_INCREMENT及一些注意事项


MySQL的中AUTO_INCREMENT类型的属性用于为一个表中记录自动生成ID功能，可在一定程度上代替Oracle，PostgreSQL等数据库中的sequence。一个表只能有一个AUTO_INCREMENT属性，且该属性必须为主键的一部分。AUTO_INCREMENT属性可以是任何整数类型（tinyint，smallint，int，bigint等）。

在插入记录时让系统为AUTO_INCREMENT属性自动生成值的通常方法在VALUES列表中不指定该属性的值，如若有表t定义如下:
CREATE TABLE t (a INT AUTO_INCREMENT PRIMARY KEY, b INT);
则一般使用如下插入语句:
INSERT INTO t(b) VALUES(1);
即在插入时不指定a的值。

第二种方法是插入时为AUTO_INCREMENT属性指定值为0，即:
INSERT INTO t VALUES(0, 1);
与
INSERT INTO t(b) VALUES(1);
的效果是一样的。但这种方法没有第一种方法通用，并且在5.0及其后续版本中只有在SQL MODE中不包含NO_AUTO_VALUE_ON_ZERO时才生效（如果在SQL MODE指定了NO_AUTO_VALUE_ON_ZERO，则真的是插入了0），不推荐使用。还有其它的方法，如指定AUTO_INCREMENT属性的值为DEFAULT等，也不推荐使用。

获得新生成的AUTO_INCREMENT属性的值也有多种方法。推荐的方法是使用LAST_INSERT_ID()函数，即在INSERT之后马上使用:
SELECT LAST_INSERT_ID();
来获得新生成的AUTO_INCREMENT属性值。

另外还可以用"WHERE auto_col IS NULL"条件选择出新插入的行，即在INSERT后马上用:
SELECT * FROM t WHERE a IS NULL;
选择出来的将是新插入的行，而非真正的满足"a IS NULL"条件的行。但你要是再执行一次上述查询，则返回的又变成了真正的满足"a IS NULL"条件的行，由于a是主键，因此肯定会返回空集。这看上去很诡异是吗，不过MySQL也不想这么干，但ODBC标准里曾有这种用法，为了支持 ODBC，MySQL也是没办法啊。不过可以将SQL_AUTO_IS_NULL设为0来禁止这一用法。

此外AUTO_INCREMENT属性也给复制带来了麻烦。一般情况下复制AUTO_INCREMENT属性能正确工作，但以下情况还是有问题：
1. INSERT DELAYED ... VALUES(LAST_INSERT_ID())不能被正确复制
2. 存储过程插入的使用AUTO_INCREMENT属性的记录不能被正确复制
3. 通过"ALTER TABLE"命令增加AUTO_INCREMENT属性时在主从节点上产生的值可能是不一样的，因为这个各行AUTO_INCREMENT属性的值取决于物理上的存储顺序。




用法:

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数据列的值必须是唯一的；情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。

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

如果用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数据列包括在这个索引里作为它的最后一个数据列。这样，这个复合索引里，前面的那些数据列每构成一种独一无二的组合，最末尾的AUTO_INCREMENT数据列就会生成一个与该组合相对应的序列编号。[NextPage]

HEAP数据表

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

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

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

编号不可重用。

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

BDB数据表

不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。

可重用编号。

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

InnDB数据表

不可通过CREATE TABLE OR ALTER TABLE的AUTO_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实际是做了这样的优化操作：先把数据表里的所有数据和索引删除，然后重建数据表。如果想删除所有的数据行又想保留序列编号信息，可这样用一个带where的delete命令以抑制MySQL的优化：

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

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

insert into id set id = NULL;insert into main set main_id = LAST_INSERT_ID();
可用alter命令给一个数据表增加一个具有AUTO_INCREMENT属性的数据列。MySQL会自动生成所有的编号。

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

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

先创建一个只有一个数据行的数据表：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表就可生成唯一的序列编号。不会影响其它客户程序的正常表操作。


[align=center][/align][size=x-large][/size]