在MySQL中创建实现自增的序列（Sequence）

最新推荐文章于 2021-05-27 20:29:14 发布

原创最新推荐文章于 2021-05-27 20:29:14 发布 · 3.8k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysq #python #数据处理 #id

Python 专栏收录该内容

89 篇文章

订阅专栏

本文介绍了一种在MySQL中模拟自增序列(sequence)的方法，通过创建管理表和自定义函数，实现了类似Oracle序列的功能，包括获取当前值、下一个值及更新当前值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

由于我们业务的需要，我我们以前数据的使用item_id [MD5+time] 的生成方式太过冗余，我们考虑使用数字来表示，如果使用时间戳，我们觉得还不是那么的好，所以想到了利用一个独立的自增的sequence来解决该问题。

由于mysql和oracle不太一样，不支持直接的sequence，所以需要创建一张table来模拟sequence的功能：

首先：

我们要创建--Sequence 管理表

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 创建--取当前值的函数

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 ;

2 创建--取下一个值的函数

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 ;

3创建--更新当前值的函数

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 ;

（注：关于DELIMITER;可以点击这里）

最后：

我们要用以下数据设置创建的sequence名称以及设置初始值和获取当前值和下一个值。

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的下一个值

最最后就是我们使用python 去调用

import pymysql

# 打开数据库连接（ip/数据库用户名/登录密码/数据库名）
db = pymysql.connect("localhost", "root", "xxxx", "xxxxx")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT NEXTVAL('item_id');")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
db.commit()
print("Database version : %s " % data)
print(type(data))                             #  这里返回的是一个tuple 类型  
num = list(data)[0]                           #  如果我们想需要 其他类型可以直接转换一下  或者l  ist 或者  str
print(num)
print(type(num))                              #  这里取了一个int类型

# 关闭数据库连接
db.close()

感谢大家