1创建表
CREATE TABLE `tb_sequence` (
`seq_name` varchar(50) NOT NULL COMMENT '字段名称',
`min` int(11) NOT NULL COMMENT '最小值',
`max` int(11) NOT NULL COMMENT '最大值',
`current_val` int(11) NOT NULL COMMENT '当前',
`increment_val` int(11) NOT NULL DEFAULT '1' COMMENT '步长',
`remark` varchar(50) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`seq_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2创建自增函数
CREATE DEFINER=`root`@`%` FUNCTION `_nextval`(name varchar(50)) RETURNS int(11)
begin
declare _cur int;
declare _maxvalue int;
declare _increment int;
set _increment = (select increment_val from tb_sequence where seq_name = name);
set _maxvalue = (select max from tb_sequence where seq_name = name);
set _cur = (select current_val from tb_sequence where seq_name = name);
update tb_sequence
set current_val = _cur + increment_val
where seq_name = name ;
return _cur;
end
3.取值
SELECT right(1000000+_nextval(seq_name),6) FROM DUAL
可以补全6位
建表语句中修改为MyISAM改为了InnoDB,因为5.6中MyISAM不支持事务