mysql 添加自增长ID（序列方式）

参考地址：https://www.cnblogs.com/xunyi/p/7499389.html

-- 新建序列表

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');

-- 创建函数 获取序列信息

-- 获取当前序列

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');

-- 下个数值

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');

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');

SELECT * FROM test1;