MySQL模拟序列的功能
-- MySQL模拟序列的功能
-- 创建存储每个序列的值的表
CREATE TABLE tzq_seq (
seqname VARCHAR ( 50 ) NOT NULL COMMENT '序列名称',
currentValue BIGINT ( 20 ) NOT NULL COMMENT '序列当前值',
increment BIGINT ( 20 ) NOT NULL DEFAULT 1 COMMENT '增长刻度',
PRIMARY KEY ( seqname )
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '序列表';
-- root 用户执行下面语句
-- 给 tzq 授予 创建函数权限
grant create routine on mysql_tzq_db.* to 'tzq'@'localhost';
grant alter routine on mysql_tzq_db.* to 'tzq'@'localhost';
grant execute on mysql_tzq_db.* to 'tzq'@'localhost';
-- 立即刷新权限
FLUSH PRIVILEGES;
-- 设置 log_bin_trust_function_creators 参数
SET GLOBAL log_bin_trust_function_creators = 1;
-- 创建序列相关的函数
-- 1、currval 函数
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS BIGINT(20) READS SQL DATA
BEGIN
DECLARE current bigint;
SET current = 0;
SELECT currentValue INTO current
FROM tzq_seq
WHERE seqname = upper(seq_name);
RETURN current;
END;
-- 2、nextval 函数
CREATE FUNCTION nextval (seq_name VARCHAR(50))
RETURNS BIGINT(20) DETERMINISTIC
BEGIN
UPDATE tzq_seq
SET currentValue = currentValue + increment
WHERE lower(seqname) = lower(seq_name);
RETURN currval(seq_name);
END;
-- 插入序列到序列表
insert into tzq_seq select 'tzq_log_s',0,1;
insert into tzq_seq select 'tzq_sys_s',0,1;
-- 查看序列当前值
select currval('tzq_log_s');
-- 查看序列下一个的值,并使序列当前值设置为下一个的值
select nextval('tzq_log_s');