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

2021-08-27 11:59:33

工作过程中需要将基于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

码农公寓

相关文章