在MySQL数据库中使用自定义函数实现序列

记录：264

场景：在MySQL数据库中使用自定义函数实现序列。

功能：获取当前序列值、获取下一个序列值、设置序列值。

本例环境：MySQL 5.7.33

1.建表语句

CREATE TABLE `HUB_SEQUENCE` (
  `SEQ_CODE` VARCHAR(64) NOT NULL COMMENT '序列代码',
  `SEQ_NAME` varchar(256) DEFAULT NULL COMMENT '序列名称',
  `CURRENT_VAL` BIGINT(16) NOT NULL COMMENT '当前序列值',
  `INCREMENT_VAL` INT(8) NOT NULL DEFAULT '1' COMMENT '增长值',
  PRIMARY KEY (`SEQ_CODE`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='序列表';

功能：用于存储序列的代码、名称、当前值、增长值。

2.获取当前序列值

函数名：CURRVAL

入参：V_SEQ_CODE  类型 VARCHAR (64)

返回：BIGINT类型数值

设计逻辑：在函数中从HUB_SEQUENCE表的CURRENT_VAL值返回，即当前序列值。

函数SQL:

DELIMITER $$
USE `hub_db` $$
DROP FUNCTION IF EXISTS `CURRVAL` $$
CREATE DEFINER = `hub_db` @`%` FUNCTION `CURRVAL` (V_SEQ_CODE VARCHAR (64)) 
    RETURNS BIGINT (16) 
    DETERMINISTIC
BEGIN
  DECLARE VALUE BIGINT;
  SET VALUE = 0;
  SELECT
    CURRENT_VAL INTO VALUE
  FROM
    HUB_SEQUENCE
  WHERE SEQ_CODE = V_SEQ_CODE;
  RETURN VALUE;
END $$

3.获取下一个序列值

函数名：NEXTVAL

入参：V_SEQ_CODE  类型 VARCHAR (64)

返回：BIGINT类型数值

设计逻辑：在函数中从HUB_SEQUENCE表的CURRENT_VAL + INCREMENT_VAL求和的值返回，即获取下一个序列值。

函数SQL:

DELIMITER $$
USE `hub_db`$$
DROP FUNCTION IF EXISTS `NEXTVAL`$$
CREATE DEFINER=`hub_db`@`%` FUNCTION `NEXTVAL`(V_SEQ_CODE VARCHAR(64)) 
    RETURNS BIGINT(16)
    DETERMINISTIC
BEGIN
UPDATE HUB_SEQUENCE
SET CURRENT_VAL = CURRENT_VAL + INCREMENT_VAL
WHERE SEQ_CODE = V_SEQ_CODE; 
RETURN currval(V_SEQ_CODE); 
END$$

4.设置序列值

函数名：SETVAL

入参：V_SEQ_CODE  类型 VARCHAR (64)

返回：BIGINT类型数值

设计逻辑：在函数中更新HUB_SEQUENCE表的CURRENT_VAL值，即设置序列值。

函数SQL:

DELIMITER $$
USE `hub_db`$$
DROP FUNCTION IF EXISTS `SETVAL`$$
CREATE DEFINER=`hub_db`@`%` FUNCTION `SETVAL`(V_SEQ_CODE VARCHAR(64), VALUE BIGINT) 
    RETURNS BIGINT(16)
    DETERMINISTIC
BEGIN
UPDATE HUB_SEQUENCE
SET CURRENT_VAL = VALUE 
WHERE SEQ_CODE = V_SEQ_CODE; 
RETURN CURRVAL(V_SEQ_CODE); 
END$$

5.报错处理

错误信息：

The user specified as a definer ('hub_db'@'%') does not exist

解决方式：

5.1登入MySQL后台

命令：mysql -u root -p 密码

5.2赋权

命令：

grant all privileges on *.* to hub_db@"%" identified by "密码";
flush privileges;

5.3注意

注意本例数据库名称：hub_db，在SQL中对应位置替换掉即可。

6.体验

6.1初始化数据

INSERT INTO HUB_SEQUENCE 
(SEQ_CODE,SEQ_NAME,CURRENT_VAL,INCREMENT_VAL) 
VALUE 
('SEQ_SYSTEM_LOG','系统日志',1,1);

6.2使用SQL

SELECT CURRVAL('SEQ_SYSTEM_LOG') FROM DUAL;
SELECT NEXTVAL('SEQ_SYSTEM_LOG') FROM DUAL;
SELECT SETVAL('SEQ_SYSTEM_LOG',20211215) FROM DUAL;

6.3函数列表截图

 以上，感谢。

2021年12月16日