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


            工作过程中需要将基于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执行后效果如下所示:
    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 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

  • 相关阅读:
    Dos命令%date:~0,10%
    SharePoint显示错误信息
    VM打开虚拟机文件报错
    Sharepoint2013切换用户菜单
    批量创建域用户
    通过数据库恢复SharePoint网站
    SharePoint2013修复报错
    js 循环
    js 正则表达式
    阶乘函数
  • 原文地址:https://www.cnblogs.com/wanggangblog/p/4037543.html
Copyright © 2020-2023  润新知