mysql本身不提供序列机制,mysql使用auto_increment设置初始值,也能通过修改系统变量auto_increment_increment设置步长,但这是个全局设置,所有表增长的步长都是一样的。mysql一个表只能有一个自增长字段。自增长只能分配给固定表的固定字段,不能倍多个表共用,并且只能是数字类型。
我们在什么情况下可能使用序列呢
1.业务复杂,需要定制和控制主键时(自增主键只能按数字递增的,但是序列可以随心所欲的变化,比如我们按照年、月、日生成主键)
2.希望手工维护自增长,方便数据迁移
3.当事务跨多表,期望事务可靠性时
4.需要业务上有意义的主键时,比如流水号
5.主键很明确地需要和其他表关联
6.期望主键是唯一,但不需要重复列用时
当然也有缺点,主要时程序处理麻烦。而且mysql只能通过触发器模拟,会有一些性能损失。
比较自增主键和序列之间的异同以及优缺点,在mysql中使用自增主键更方便。但这里仍然推荐使用序列,
因为其有更好的定制性和可控性。对于InnoDB,虽然序列的可定制性强,
但是如果使用序列,则不推荐使用char等和非number型数据坐主键,因为这样会影响InnoDB的插入性能
我们要创建–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;
创建–取当前值的函数
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 ;
创建–取下一个值的函数
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 ;
创建–更新当前值的函数
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 ;
然后我们就需要进行去管理
INSERT INTO sequence VALUES ('item_id', 0, 1); #插入一条数据,添加一个sequence名称和初始值,以及自增幅度
SELECT SETVAL('item_id', 1); #设置指定sequence的初始值
SELECT CURRVAL('item_id'); #查询指定sequence的当前值
SELECT NEXTVAL('item_id'); #查询指定sequence的下一个值
#使用自增
inset into user (id,username,email,password) values (NEXTVAL('item_id'),'张三','123@qq.com','123')
作者:ZzyYe
来源:CSDN
原文:https://blog.csdn.net/zzy_bug/article/details/87980553
版权声明:本文为博主原创文章,转载请附上博文链接!