MySQL AUTO_INCREMENT 要点记录

yl197837

参考：

[MySQL Cookbook(Edition 2)] Chaper 11 Generating and Using Sequences

[MySQL 5.1  参考手册]

google

　　
　　1. AUTO_INCREMENT 列定义
　　1) 语法：
　　CREATE TABLE xxx
　　(
　　...
　　id INT UNSIGNED NOT NULL AUTO_INCREMENT,
　　PRIMARY KEY (id)
　　or
　　UNIQUE (id)
　　...
　　)
　　
　　SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的一个别名。
　　在整数列定义中，SERIAL DEFAULT VALUE是NOT NULL AUTO_INCREMENT UNIQUE的一个别名。
　　
　　2) 能定义为AUTO_INCREMENT的列类型：整数类型。
　　3) UNSIGNED作用：将序列的取值范围增加一倍，
　　如TINYINT取值范围为-128~127，未指定UNSIGNED序列值为1~127，指定后则序列值为1~255。
　　4) AUTO_INCREMENT列必须被索引化。
　　5) MyISAM是支持含有AUTO_INCREMENT列的表的最佳引擎。



 
2. AUTO_INCREMENT 列值
1) 最大值取决于它所使用的整数类型。
　　2) 序列值的重用（含AUTO_INCREMENT 列的表数据被删除后所产生的场景）：
a) 空洞值（如表中已有记录1,2,3,4,5, 删除了2, 则形成了2这个空洞值）：无论何种表引擎，均不会重用。
b) 顶端值（如表中已有记录1,2,3,4,5, 则5是顶端值，然后删除了5）：BDB会重用（下一个序列值为5），MyISAM、InnoDB不会重用（下一个序列值为6）。
 
　　3) 序列值的查询：
a) 通过数据库函数: LAST_INSERT_ID() 这个返回值基于服务器的每一个客户端连接。
b) 通过JDBC API: Java: getLastInsertID() 方法：

long seq = ((com.mysql.jdbc.Statement) s).getLastInsertID();
or
long seq = ((com.mysql.jdbc.PreparedStatement) s).getLastInsertID();

　　注意事项：
　　a) 生成和获取AUTO_INCREMENT操作在同一个MySQL连接内，否则将会得到0。
　　b) 客户端序列值的有效性与每一条语句相关，而不仅仅由生成AUTO_INCREMENT值的语句决定。
　　使用如下原则可以避免错误：当生成一个不会马上使用的序列值，可以先保存到一个变量中。


　　4) 序列值的范围扩展：
　　a) 如果列值类型是有符号的，改为UNSIGNED。
　　b) 如果已经是UNSIGNED并不是最大的整数类型（BIGINT），则变更列类型为最大的整数类型。



5) 序列值的重建：　　从表中删除这一列，然后再添加回去，MySQL会将列值重新序列化为一个连续序列。
　　
　　6) 指定步长、偏移量：
　　a) 全局配置方式：
　　在 my.ini 中增加以下配置项：
　　auto_increment_increment=n
　　auto_increment_offset=x
　　
　　Replication时，为防止auto_increment列值重复 ，则是在 my.cnf 中增加以上2个配置项：
　　如在A服务器的my.cnf设置如下： 
auto_increment_offset = 1 
auto_increment_increment = 2 
则A的auto_increment字段产生的数值是：1, 3, 5, 7, ... 

在B服务器的my.cnf设置如下： 
auto_increment_offset = 2 
auto_increment_increment = 2 
则B的auto_increment字段产生的数值是：2, 4, 6, 8, ... 
　　b) 针对某表：
　　CREATE TABLE 加上 AUTO_INCREMENT=n  
　　or
　　ALTER TABLE  AUTO_INCREMENT=n  


　　如果表是非MyISAM或InnoDB引擎，则可以这样：
　　插入具有序列值n-1的“假”行，然后在插入了一行或多行“真”数据后删除这个“假”行。
　　
　　3. AUTO_INCREMENT 其他应用场景：
1) 复合主键：
　　昆虫采集表：

CREATE TABLE bug
(
id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
name    VARCHAR(30) NOT NULL, # type of bug
date    DATE NOT NULL,        # date collected
origin  VARCHAR(30) NOT NULL, # where collected
PRIMARY KEY (name, id)
);

　　插入一些数据，然后使用 order by 查询表中数据，可以看到MySQL为每一个唯一的name值创建了一个独立的序列：
　　

mysql> SELECT * FROM bug ORDER BY name, id;
+----+-----------+------------+-------------------+
| id | name      | date       | origin            |
+----+-----------+------------+-------------------+
|  1 | ant       | 2006-10-07 | kitchen           |
|  2 | ant       | 2006-10-07 | front yard        |
|  3 | ant       | 2006-10-07 | front yard        |
|  4 | ant       | 2006-10-11 | garden            |
|  1 | beetle    | 2006-10-07 | basement          |
|  2 | beetle    | 2006-10-08 | front yard        |
|  1 | cricket   | 2006-10-08 | garage            |
|  2 | cricket   | 2006-10-10 | basement          |
|  3 | cricket   | 2006-10-11 | garden            |
|  1 | honeybee  | 2006-10-08 | back yard         |
|  2 | honeybee  | 2006-10-11 | garden            |
|  1 | millipede | 2006-10-07 | basement          |
|  1 | termite   | 2006-10-09 | kitchen woodwork  |
|  2 | termite   | 2006-10-11 | bathroom woodwork |
+----+-----------+------------+-------------------+

　　
　　
　　2) 计数器：
采用一个计数器占用一行的序列生成机制。
　　
　　INSERT语句中加上 ON DUPLICATE KEY UPDATE
　　例：
　　INSERT INTO tbl (col, num) VALUES('test', LAST_INSERT_ID(n))
　　ON DUPLICATE KEY UPDATE num = LAST_INSERT_ID(num+n);
　　
3) 循环序列：
　　使用 division 和 modulo 操作符生成循环元素。
　　业务场景：
　　假设你正在生产药品或汽车零件，你必须通过批号跟踪所有商品，如果以后发现了产品问题，要求召回售出的某一批产品。假设你把12个产品包装为1盒，6盒包装为1箱。
　　这种情况下，产品编号为3个部分：单品编号（1到12）、盒编号（1到6）、1个批号（从1到任意值）。
　　根据序列编号生成箱、盒和单品编号的公式如下：
　　unit_num = ((seq - 1) % 12) + 1
　　box_num = (int ((seq - 1) / 12) % 6) + 1
　　case_num = int ((seq - 1)/(6 * 12)) + 1
　　下表说明了序列值与对应的箱、盒、单品编号之间的关系：
　　


seq
case
box
unit

1
1
1
1


12
1
1
12


13
1
2
1


72
1
6
12


73
2
1
1


144
2
6
12
　　
　　
　　4. Oracle MySQL 的 sequence 和 AUTO_INCREMENT 互转：
　　MySQL---ORACLE序列解决方案
　　MySQL全局序列的实现方式（待实践研究）：
　　1) sequence表：缺点：可能会成为性能瓶颈。
　　2) Flickr：与sequence表方式类似，但较好地解决了性能瓶颈和单点问题。