set global log_bin_trust_function_creators=TRUE;
drop table if exists sequence;
create table sequence
(
seq_name VARCHAR(50) NOT NULL, -- 序列名称
current_year int default 0, -- 当前年
init_val BIGINT NOT NULL,
current_val BIGINT NOT NULL, -- 当前值
increment_val INT NOT NULL DEFAULT 1, -- 步长(跨度)
PRIMARY KEY (seq_name)
);
drop function currval;
DELIMITER ;;
create function currval(v_seq_name VARCHAR(50))
returns varchar(64)
begin
update sequence set current_year = YEAR(NOW()), current_val=init_val
where seq_name = v_seq_name and current_year != YEAR(NOW());
select CONCAT(current_year, current_val) into @value from sequence where seq_name = v_seq_name;
return @value ;
end;;
drop function nextval;
DELIMITER ;;
create function nextval (v_seq_name VARCHAR(50))
returns bigint
begin
update sequence set current_val = current_val + increment_val where seq_name = v_seq_name;
return currval(v_seq_name);
end;;
-- current_year一定要有初始值,year()与null不能直接做!=
INSERT INTO sequence VALUES ('generatorNumber', 0,1000000, 1000000, 1);