Mysql生成序列---拼接字符串用于业务主键

检查数据库是否开启自定义函数开关

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