参考:
[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表方式类似,但较好地解决了性能瓶颈和单点问题。
分享到:
相关推荐
MySQL AUTO_INCREMENT 和主键等解释
MySQL 序列 AUTO_INCREMENT详解及实例代码 MySQL序列是一组整数:1, 2, 3, …,由于一张数据表只能有一个字段自增主键, 如果你想实现其他字段也实现自动增加,就可以使用MySQL序列来实现。 本章我们将介绍如何使用...
主要介绍了MySQL查询和修改auto_increment的方法,实例分析了select查询auto_increment及ALTER修改auto_increment的技巧,需要的朋友可以参考下
主要介绍了python mysql自增字段AUTO_INCREMENT值的修改方式,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧
当清空一个表的时候,重新插入数据,发现auto_increment属性的字段计数不是从1开始的时候,可以使用以下命令
重置 MySQL 自增列 AUTO_INCREMENT 初时值 注意, 使用以下任意方法都会将现有数据删除. 方法一: delete from tb1; ALTER TABLE tbl AUTO_INCREMENT = 100; (好处, 可以设置 AUTO_INCREMENT 为任意值开始) 提示:...
04_auto_increment.avi MYSQL应用 MYSQL视频 MYSQL教程,讲解中连接上一集03集
本篇文章是对mysql中的auto_increment的问题进行了详细的分析介绍,需要的朋友参考下
blog_auto_increment
mysql中的自增auto_increment功能相信每位phper都用过,也都知道如何设置字段为自增字段,但并不是所有phper都知道auto_increment的起始值和递增量是如何设置的!本文就为大家分享一下mysql字段自增功能的具体查看及...
`id` bigint(20) NOT NULL AUTO_INCREMENT, `category_id` bigint(20) NOT NULL COMMENT '内容类目ID', `title` varchar(200) DEFAULT NULL COMMENT '内容标题', `sub_title` varchar(100) DEFAULT NULL COMMENT...
像MySQL一样,将Mongoid id字段更改为Integer的宝石。 MongoDB文档中的想法: 注意! 这个宝石已经帮助超过了4年,并产生了超过一百万行,这是非常...gem 'mongoid_auto_increment_id' , "0.6.1" # Mongoid 3.1.x gem
innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时,相关锁的行为; 通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡 【0】我们先对insert做一下分类 首先insert大致上可以...
mongoose_auto_increment 创建一个Counter用来记录所有Collections增长数,以Mongoose plugin的方式与对应的Scheme相关联,以此实现Mongoose自动增长列
表名:chart2010 语句:id int primary key auto_increment, num int 表名:menu 语句: id int primary key auto_increment, name varchar(20), cost varchar(10) 表名:uid 语句: id int primary key auto_increment, ...