mysql建立序列，并根据表名获取

—建立索引管理表
DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
name VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 1,
PRIMARY KEY (name)
) ENGINE=InnoDB;
—-第二步：建立–取当前值的函数
DROP FUNCTION IF EXISTS currval;
DELIMITER $CREATE FUNCTION currval (seq_name VARCHAR(50)) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ” BEGIN DECLARE value INTEGER; SET value = 0; SELECT current_value INTO value FROM sequence WHERE name = seq_name; RETURN value; END$
DELIMITER ;
—–第三步：建立–取下一个值的函数
DROP FUNCTION IF EXISTS nextval;
DELIMITER $CREATE FUNCTION nextval (seq_name VARCHAR(50)) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ” BEGIN UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name; IF ROW_COUNT()=0 THEN INSERT INTO sequence VALUES (seq_name, 1000, 1); END IF; RETURN currval(seq_name); END$
DELIMITER ;
—第四部
DROP FUNCTION IF EXISTS setval;
DELIMITER $CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ” BEGIN UPDATE sequence SET current_value = value WHERE name = seq_name; RETURN currval(seq_name); END$ web

DELIMITER ;

—-测试
INSERT INTO sequence VALUES (‘TestSeq’, 0, 1);—-添加一个sequence名称和初始值，以及自增幅度
SELECT SETVAL(‘TestSeq’, 10);—设置指定sequence的初始值
SELECT CURRVAL(‘TestSeq’);–查询指定sequence的当前值
SELECT NEXTVAL(‘TestSeq1’);–查询指定sequence的下一个值svg

—-最终的函数结果
SELECT NEXTVAL(‘TABLE_NAME’);–TABLE_NAME是表名函数

insert into car_info values(NEXTVAL(‘car_info’),’xxxxx’,’xxxx’);
自测是能够经过的，这里的id个人是numeric(19,0)，用这个函数也是能够的。测试