检查数据库是否开启自定义函数开关
show VARIABLES like ‘%fun%’
如果value为OFF
执行: set GLOBAL log_bin_trust_function_creators=1
1.创建表
CREATE TABLE `sequence` (
`name` varchar(32) NOT NULL,
`value` int(6) DEFAULT NULL,
`next` int(6) DEFAULT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `sequence` VALUES ('trans_no', '1', '1');
2.创建函数(next_trans_num)获取序列号
CREATE FUNCTION next_trans_num(seq_name varchar(20)) RETURNS int(50)
BEGIN
UPDATE sequence SET value=IF(last_insert_id(value+next)>= 999998,0,last_insert_id(value+next)) WHERE name=seq_name;
RETURN last_insert_id();
END
3.拼接业务前缀+日期+填充字符— 创建函数(get_trans_num)
CREATE FUNCTION get_trans_num() RETURNS varchar(20) CHARSET utf8
BEGIN
DECLARE getval VARCHAR(24);
SET getval = (SELECT CONCAT('01',DATE_FORMAT(NOW(), '%Y%m%d'), LPAD((SELECT next_trans_num('trans_no')), 4, '0')));
RETURN getval;
END
//年月日时分表%Y%m%d%H%i%s’
4.查询即可
select next_trans_num('trans_no')
select get_trans_num();
//删除函数
drop function get_trans_num