mysql 函数生成序列号 mysql生成序列号的自增函数

转载

mob6454cc70642f 2023-05-22 11:51:59

MySQL实现序列效果

新建序列表

drop table if exists sequence;  
create table sequence (      
seq_name        VARCHAR(50) NOT NULL, -- 序列名称      
current_val     INT         NOT NULL, -- 当前值      
increment_val   INT         NOT NULL    DEFAULT 1, -- 步长(跨度)      
PRIMARY KEY (seq_name)   );

新增一个序列

INSERT INTO sequence VALUES ('seq_test1_num1', '0', '1');
INSERT INTO sequence VALUES ('seq_test1_num2', '0', '2');

创建函数用于获取序列当前值(v_seq_name 参数值代表序列名称)

create function currval(v_seq_name VARCHAR(50))  
returns integer 
begin     
    declare value integer;      
    set value = 0;      
    select current_val into value  from sequence where seq_name = v_seq_name;
   return value;
end;

查询当前值

select currval('seq_test1_num1');

创建函数用于获取序列下一个值(v_seq_name 参数值代表序列名称)

create function nextval (v_seq_name VARCHAR(50))
    returns integer
begin
    update sequence set current_val = current_val + increment_val  where seq_name = v_seq_name;
    return currval(v_seq_name);
end;

查询下一个值

select nextval('seq_test1_num1');

新建表用于测试的表

DROP TABLE IF EXISTS `test1`;
	CREATE TABLE `test1` (
	  `name` varchar(255) NOT NULL,
	  `value` double(255,0) DEFAULT NULL,
	  `num1` int(11) DEFAULT NULL,
	  `num2` int(11) DEFAULT NULL,
	  PRIMARY KEY (`name`)
	);

新建触发器插入新纪录前给自增字段赋值实现字段自增效果

CREATE TRIGGER `TRI_test1_num1` BEFORE INSERT ON `test1` FOR EACH ROW BEGIN
set NEW.num1 = nextval('seq_test1_num1');
set NEW.num2 = nextval('seq_test1_num2');
END

最后测试自增效果

INSERT INTO test1 (name, value) VALUES ('1', '111');
INSERT INTO test1 (name, value) VALUES ('2', '222');
INSERT INTO test1 (name, value) VALUES ('3', '333');
INSERT INTO test1 (name, value) VALUES ('4', '444');