1.场景一:一直增长到最大值,达到最大值后又从最小值开始
a.首先得建一张用于存放Sequence的表:tbl_sequence
-- ----------------------------
-- Table structure for tbl_sequence
-- ----------------------------
DROP TABLE IF EXISTS `tbl_sequence`;
CREATE TABLE `tbl_sequence` (
`seq_name` varchar(50) NOT NULL COMMENT '序列名称',
`min_value` int(11) NOT NULL COMMENT '最小值',
`max_value` int(11) NOT NULL COMMENT '最大值',
`current_val` int(11) NOT NULL COMMENT '当前值',
`increment_val` int(11) NOT NULL DEFAULT '1' COMMENT '步长',
PRIMARY KEY (`seq_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
b.在表里插入一条Sequence数据
-- ----------------------------
-- Records of tbl_sequence
-- ----------------------------
INSERT INTO `tbl_sequence` VALUES ('t_user_seq', '1', '999999999', '1', '1');
c.创建一个 nextval 函数:
CREATE FUNCTION `nextval` (NAME VARCHAR(50)) RETURNS INT (11)
BEGIN
DECLARE _cur INT;
DECLARE _maxvalue INT; -- 接收最大值
DECLARE _increment INT; -- 接收增长步数
SELECT
current_val,
max_value,
increment_val
INTO _cur,
_maxvalue,
_increment
FROM
tbl_sequence
WHERE
seq_name = NAME;
UPDATE tbl_sequence -- 更新当前值
SET current_val = _cur + increment_val
WHERE
seq_name = NAME;
IF (_cur + _increment > _maxvalue) THEN -- 判断是否达到最大值
UPDATE tbl_sequence
SET current_val = min_value
WHERE
seq_name = NAME;
END
IF;
RETURN _cur;
END;
d.测试
SELECT nextval ('t_user_seq');
2.场景二:以时间为单位回归,例如每天或达到最大值之后也从最小值开始
a.首先得建一张和场景一略有不同用于存放Sequence的表:tbl_sequence
-- ----------------------------
-- Table structure for tbl_sequence
-- ----------------------------
DROP TABLE IF EXISTS `tbl_sequence`;
CREATE TABLE `tbl_sequence` (
`seq_name` varchar(50) NOT NULL COMMENT '序列名称',
`min_value` int(11) NOT NULL COMMENT '最小值',
`max_value` int(11) NOT NULL COMMENT '最大值',
`current_val` int(11) NOT NULL COMMENT '当前值',
`increment_val` int(11) NOT NULL DEFAULT '1' COMMENT '步长',
`return_type` varchar(64) DEFAULT NULL COMMENT '回归类型',
`return_date` varchar(64) DEFAULT NULL COMMENT '回归时间',
PRIMARY KEY (`seq_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
b.插入一条Sequence数据
-- ----------------------------
-- Records of tbl_sequence
-- ----------------------------
INSERT INTO `tbl_sequence` VALUES ('t_user_seq', '1', '9', '2', '1', '%Y%m%d', NULL);
c.创建一个函数:nextval_2
CREATE FUNCTION `nextval_2`(name varchar(50)) RETURNS int(11)
begin
DECLARE _cur INT;
DECLARE _minvalue INT; -- 接收最小值
DECLARE _maxvalue INT; -- 接受最大值
DECLARE _increment INT; -- 接收增长步长
DECLARE _type VARCHAR(64); -- 回归类型
DECLARE _date VARCHAR(64); -- 回归时间
DECLARE _current_date VARCHAR(64); -- 当前时间
SELECT current_val, min_value, max_value, increment_val, return_type, return_date
INTO _cur,_minvalue , _maxvalue, _increment, _type, _date
FROM tbl_sequence WHERE seq_name = NAME;
------ 此段为增加的部分 -----
IF(_type IS NOT NULL) THEN
SET _current_date = DATE_FORMAT(NOW(),_type);
IF(_current_date != _date OR _date IS NULL) THEN
SET _cur = _minvalue;
SET _date = _current_date;
END IF;
END IF;
-----------------------------
update tbl_sequence -- 更新当前值
set current_val = _cur + increment_val,
return_date = _date
where seq_name = name ;
if(_cur + _increment > _maxvalue) then -- 判断是否达到最大值
update tbl_sequence
set current_val = min_value
where seq_name = name ;
end if;
return _cur;
end
d.测试
SELECT nextval_2('t_user_seq')