工作过程中需要将基于DB2数据库的应用以及数据迁移到MySQL中去,在原应用中,大量使用了SEQUENCE,考虑尽量减少代码的修改,决定在迁移后的应用中继续保留SEQUENCE的使用,这就要求在MySQL中寻找替代SEQUENCE的解决方案。
在DB2中创建一个SEQUENCE的方法如下:
DROP SEQUENCE TRZ_MEMBER.SEQ_TRZ_MEMBER_NO; CREATE SEQUENCE TRZ_MEMBER.SEQ_TRZ_MEMBER_NO AS BIGINT INCREMENT BY 1 START WITH 10000000000 MAXVALUE 99999999999 NO CYCLE CACHE 20 ORDER;
MySQL自增长与Oracle(DB2)序列的区别:
自增长只能用于表中的其中一个字段;
自增长只能被分配给固定表的固定的某一字段,不能被多个表共用;
自增长会把一个未指定或NULL值的字段自动填上。
要想在MySQL中替代SEQUENCE功能需要做一下几件事:
1:建立SEQUENCE表,存储多条SEQUENCE信息;
2:完成自定义函数的定义,在程序中通过该函数完成生成的序列的获取;
接下来将介绍两种生成方式,一种是非并发方式,一种是并发方式,前一种不能够处理并发访问中存在的问题,后一种则能够处理,两种方式的第一步都相同,就是创建SEQUENCE表:
非并发方式:
一:创建SEQUENCE表:
DROP TABLE IF EXISTS sequence; CREATE TABLE sequence ( name VARCHAR(50) NOT NULL, current_value BIGINT NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (name) ) ENGINE=InnoDB;
该表用来存储多条sequence信息,每条sequence为一个序列,其效果等同于之前介绍的DB2中的sequence。假设需要替换DB2中的SEQ_TRZ_MEMBER_NO(如前定义),则插入MySQL中表sequence的插入语句如下:
INSERT INTO sequence VALUES ('SEQ_TRZ_MEMBER_NO',10000000000,1);
二:创建MySQL自定义函数,用以获取当前sequence值:
1:首先明确的是,自定义函数是对MySQL提供的函数库的一种补充,用来完成自定义功能,新建MySQL函数必须通过MySQL Commond Line键入命令行的方式进行创建,而不能通过第三方提供的图形化数据库操作软件来创建;
2:首先提供一个范例,好有一个总体的认识:
DELIMITER $$ DROP FUNCTION IF EXISTS currval; CREATE FUNCTION currval (seq_name VARCHAR(50)) RETURNS BIGINT BEGIN DECLARE c_value BIGINT DEFAULT 0; SET c_value = 0; SELECT current_value into c_value FROM sequence WHERE name = seq_name; RETURN c_value; END $$ DELIMITER ;该函数的功能为返回指定序列的当前值。
其中第一行代码(DELIMITER $$ )定义一个结束标识符,因为MySQL默认是以分号作为SQL语句的结束符的,而函数体内部要用到分号,所以会跟默认的SQL结束符发生冲突,所以需要先定义一个其他的符号作为SQL的结束符。没有加这个定义的话会报如下错误:
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 1
第二行(DROP FUNCTION IF EXISTS currval; )是删除同名的类,不然如果已经存在了同名函数,会报如下错误:
错误码: 1304
FUNCTION currval already exists
第三,第四行定义函数名称和函数返回值;
而函数体必须定义在Begin和End中间。
通过MySQL Commond Line执行后效果如下所示:
通过如下语句验证效果:
该函数只完成了获取当前序列值的作用,还需要定义一个函数来完成获取下一个序列值的功能,代码如下所示:
DROP FUNCTION IF EXISTS nextval; DELIMITER $$ CREATE FUNCTION nextval (seq_name VARCHAR(50)) RETURNS BIGINT CONTAINS SQL BEGIN UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name; RETURN currval(seq_name); END $$ DELIMITER ;
该函数用来获取下一个序列值,在MySQL Commond Line中执行后的结果如下所示:
通过如下语句验证函数是否生效:
SELECT NEXTVAL('SEQ_TRZ_MEMBER_NO');
至此非并发方式的sequence生成方式就实现完了。要想使得sequence的生成能够处理并发的方式,只需要少做修改即可。
并发生成方式:
sequence的并发生成方式同非并发生成方式都需要建立sequence表,如下:
一:创建SEQUENCE表:
DROP TABLE IF EXISTS sequence; CREATE TABLE sequence ( name VARCHAR(50) NOT NULL, current_value BIGINT NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (name) ) ENGINE=InnoDB;插入定义的序列:
INSERT INTO sequence VALUES ('SEQ_TRZ_MEMBER_NO',10000000000,1);二:自定义函数实现:
DROP FUNCTION IF EXISTS seq; DELIMITER $$ CREATE FUNCTION seq(seq_name char (20)) returns BIGINT BEGIN UPDATE sequence SET current_value=last_insert_id(current_value+increment) WHERE name=seq_name; RETURN last_insert_id(); END $$ DELIMITER;
函数内部调用了MySQL内部提供的last_insert_id()函数完成并发控制。
而有关于last_insert_id的相关资料请参考:
http://it.100xuexi.com/view/otdetail/20120619/73a6cc8f-36b8-4b70-8904-57c18d3ab385.html
文中代码部分引自:http://meetrice.iteye.com/blog/89426
http://www.blogjava.net/Skynet/archive/2011/03/23/301847.html