当前位置:首页 > 开发 > 数据库 > 正文

MySQL AUTO_INCREMENT 要点记录

发表于: 2013-05-31   作者:tvmovie   来源:转载   浏览:
摘要: 参考: [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 AUT

参考:

[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)

...

)

 

SERIALBIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的一个别名。

在整数列定义中,SERIAL DEFAULT VALUENOT 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表方式类似,但较好地解决了性能瓶颈和单点问题。

 

 

MySQL AUTO_INCREMENT 要点记录

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号