MySQL AUTO_INCREMENT 要点记录

参考：

[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表方式类似，但较好地解决了性能瓶颈和单点问题。