首先创建sequence临时表
然后创建两个自定义函数 next_trans_num
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
和get_trans_num函数
BEGIN
DECLARE getval VARCHAR(24);
SET getval = (SELECT CONCAT(DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD((SELECT next_trans_num('trans_no')), 6, '0')));
RETURN getval;
END
最终查询结果:
select get_trans_num()