Mysql序列解决方案

mlw2000

已于 2022-05-24 08:32:08 修改

阅读量189

点赞数

分类专栏： Database 文章标签： MySQL 序列函数自增数据库

于 2022-05-24 08:30:05 首次发布

本文链接：https://blog.youkuaiyun.com/mlw2000/article/details/124939695

版权

Database 专栏收录该内容

4 篇文章

订阅专栏

## 创建表

CREATE TABLE `t_sequence` (
	`seq_name` VARCHAR(200) NOT NULL COLLATE 'utf8_general_ci',
	`val` BIGINT(20) NOT NULL DEFAULT '0',
	`increment` INT(11) NOT NULL DEFAULT '1',
	`update_time` DATETIME(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
	PRIMARY KEY (`seq_name`) USING BTREE
) ENGINE=InnoDB;

## 创建函数

DROP FUNCTION IF EXISTS f_nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (p_seq_name VARCHAR(50)) 
RETURNS INTEGER
LANGUAGE SQL 
DETERMINISTIC 
CONTAINS SQL 
SQL SECURITY DEFINER 
COMMENT ''
BEGIN

DECLARE i_val INTEGER;

UPDATE t_sequence
SET `val` = LAST_INSERT_ID(`val` + increment) 
WHERE `seq_name` = p_seq_name; 
 
SET i_val = 0; 
SELECT LAST_INSERT_ID() INTO i_val FROM DUAL;

RETURN i_val; 

END
$ 
DELIMITER ;

## 初始化数据

INSERT INTO `t_sequence` (`seq_name`, `val`, `increment`, `update_time`) 
VALUES ('TestSeq', 0, 100, null);

## 使用例子

mysql> SELECT F_NEXTVAL('TestSeq');
+----------------------+
| F_NEXTVAL('TestSeq') |
+----------------------+
|                  200 |
+----------------------+
1 row in set (0.11 sec)