mysql数据库sequence使用

来源：互联网发布：阿里云输入法编辑：程序博客网时间：2024/05/18 13:11

为了早点测试数据方便些，就在测试库里搞了个sequence里用用看了，具体实践步骤如下：

1、创建sequence表

Create TableCREATE TABLE `test_sequence` (  `seq_name` varchar(50) NOT NULL DEFAULT '' COMMENT '业务模块字段名称 自定义',  `minvalue` int(11) NOT NULL DEFAULT '1' COMMENT '最小值',  `maxvalue` int(11) NOT NULL DEFAULT '999999999' COMMENT '最大值',  `current_val` int(11) NOT NULL DEFAULT '1' COMMENT '当前值',  `increment_val` int(11) NOT NULL DEFAULT '1' COMMENT '增长步长',  PRIMARY KEY (`seq_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='叶测试数据sequence生成表'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='叶测试数据sequence生成表'

2、为该表创建function方法

CREATE FUNCTION `_nextval`(NAME VARCHAR(50))RETURNS INTEGERBEGIN  DECLARE `_cur` INT;  DECLARE `_maxvalue` INT;  DECLARE `_minvalue` INT;  DECLARE `_increment` INT;    SET `_increment` = (SELECT `increment_val` FROM `test_sequence` WHERE `seq_name` = NAME);  SET `_maxvalue` = (SELECT `maxvalue` FROM `test_sequence` WHERE `seq_name` = NAME );  SET `_cur` = (SELECT `current_val` FROM `test_sequence` WHERE `seq_name` = NAME);    UPDATE `test_sequence` SET `current_val` = `_cur` + `increment_val`   WHERE `seq_name` = NAME;  IF(`_cur` + `_increment` >= `_maxvalue`) THEN -- 判断都达到最大值      UPDATE `test_sequence` SET `current_val` = `minvalue` WHERE `seq_name` = NAME;  END IF;  RETURN `_cur`;  END;

3、使用举例

INSERT INTO `log_rtx_send_records` (`prd_id`, `status`, `send_time`, `person_id`) VALUES('testdd'+_nextval('商品号自增值'),'0','2016-12-05 21:11:54',NULL);

阅读全文

0 0