mysql根据种子生成流水号
需求
根据传入的种子生成流水号,例如以日期做种子,
第一天以20200907 + 1 开始
第二天则换成20200908重新从1开始
解决方案
CREATE TABLE tb_sequence
(
seed_key VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
_increment INT NOT NULL DEFAULT 1,
PRIMARY KEY (seed)
);
DELIMITER //
CREATE FUNCTION _nextval(n VARCHAR(50)) RETURNS INTEGER
BEGIN
DECLARE
_cur INT;
SET _cur = (SELECT current_value FROM tb_sequence WHERE NAME = n);
IF
_cur IS NULL THEN
INSERT INTO tb_sequence
VALUES (n, 1, 1);
SET _cur = 1;
END IF;
UPDATE tb_sequence
SET current_value = _cur + _increment
WHERE seed_key = n;
RETURN _cur;
END;
//
-- 获取今日当前流水号
SELECT _nextval (DATE(NOW()));