首页 > 代码库 > MySQL下创建序列及创建自定义函数方法介绍

MySQL下创建序列及创建自定义函数方法介绍

        工作过程中需要将基于DB2数据库的应用以及数据迁移到MySQL中去,在原应用中,大量使用了SEQUENCE,考虑尽量减少代码的修改,决定在迁移后的应用中继续保留SEQUENCE的使用,这就要求在MySQL中寻找替代SEQUENCE的解决方案。

        在DB2中创建一个SEQUENCE的方法如下:

DROP SEQUENCE TRZ_MEMBER.SEQ_TRZ_MEMBER_NO;CREATE SEQUENCE TRZ_MEMBER.SEQ_TRZ_MEMBER_NOAS BIGINTINCREMENT BY 1START WITH 10000000000MAXVALUE 99999999999NO CYCLECACHE 20ORDER;

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执行后效果如下所示:
image

 

通过如下语句验证效果:

image

该函数只完成了获取当前序列值的作用,还需要定义一个函数来完成获取下一个序列值的功能,代码如下所示:

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中执行后的结果如下所示:

image

通过如下语句验证函数是否生效:

SELECT NEXTVAL(SEQ_TRZ_MEMBER_NO);

image

至此非并发方式的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 BIGINTBEGIN 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

MySQL下创建序列及创建自定义函数方法介绍