知识库

# 创建序列列表

DROP TABLE IF EXISTS sequence; 
CREATE TABLE sequence ( 
     name VARCHAR(30) NOT NULL,                   
     current_value INT NOT NULL,  
     increment INT NOT NULL DEFAULT 1, 
     PRIMARY KEY (name) 
) ENGINE=InnoDB;
1
2
3
4
5
6
7

# 创建读取当前值的函数

DROP FUNCTION IF EXISTS currval; 
DELIMITER $ 
CREATE FUNCTION currval (seq_name VARCHAR(30))            
     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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 创建读取下一个值的函数

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; 
     RETURN currval(seq_name); 
END
$ 
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 更新当前值的函数

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
$ 
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 添加数据

INSERT INTO sequence VALUES ('item_id', 0, 1);   #插入一条数据，添加一个sequence名称和初始值，以及自增幅度
SELECT SETVAL('item_id', 10);                    #设置指定sequence的初始值
SELECT CURRVAL('item_id');                       #查询指定sequence的当前值
SELECT NEXTVAL('item_id');                       #查询指定sequence的下一个值
1
2
3
4

# 使用方法

SELECT NEXTVAL('item_id')
1

←