Mysql实现自增长编号，日期+序列，序列定时归零

修改MySQL配置文件

vi /etc/my.cnf

在[mysqld]下添加一行event_scheduler=ON

执行定义的SQL

DROP TABLE  IF EXISTS  sequence;
#sequence，存放序列规则，方便以后设置多个不同规则编号，包括编号名、编号值、递增值
CREATE TABLE `sequence` (
	`name` VARCHAR (30) NOT NULL,
	`value` INT (11) DEFAULT NULL,
	`next` INT (11) DEFAULT NULL,
	PRIMARY KEY (`name`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO  `sequence` (`name`, `value`, `next`) VALUES ('sp', '0', '1');

DROP EVENT  IF EXISTS  clearnsequence;
#新建一个事件，定时清空表sequence中的value值
CREATE   EVENT `clearnsequence` ON SCHEDULE EVERY 1 DAY STARTS '2022-12-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT '定时清空表sequence中的value值' DO
	UPDATE sequence
SET
VALUE
	= 0
WHERE
	NAME = 'sp';#trans_no为上面建的表中的name有多少个写多少个清除
#新建一个函数next_trans_num()，每次递增获取sequence中value值
DROP FUNCTION IF EXISTS `next_trans_num`;

CREATE  FUNCTION `next_trans_num` (`seq_name` VARCHAR(30)) RETURNS INT (11)
BEGIN
	UPDATE sequence
SET
VALUE
	= last_insert_id(VALUE + next)
WHERE
	NAME = `seq_name`;

RETURN last_insert_id();


END;

#新建一个函数get_trans_num()，获取日期+指定长度序列的编号
drop FUNCTION  IF EXISTS   get_trans_num;
CREATE  FUNCTION `get_trans_num` (`seq_name` VARCHAR(30)) RETURNS VARCHAR (24) CHARSET utf8
BEGIN

DECLARE getval VARCHAR (24);


SET getval = (
	SELECT
		CONCAT(
			DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
			LPAD(
				(
					SELECT
						next_trans_num (seq_name)
				),
				5,
				'0'
			)
		)
);

RETURN getval;


END;

调用

SELECT get_trans_num(‘sp’);