MySQL自增序列之最佳实践-亲试ok
#1.创建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;
#2.创建取当前值的函数
DROP FUNCTION IF EXISTS currval_zbmxid;
DELIMITER $
CREATE FUNCTION currval_zbmxid (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 ;
#3.创建取下一值的函数
DROP FUNCTION IF EXISTS nextval_zbmxid;
DELIMITER $
CREATE FUNCTION nextval_zbmxid (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_fphm(seq_name);
END ;
#4.创建更新值的函数
DROP FUNCTION IF EXISTS setval_zbmxid;
DELIMITER $
CREATE FUNCTION setval_zbmxid (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_fphm(seq_name);
END;
#插入基本数据
INSERT INTO sequence VALUES ('item_zbmxid', 0, 1);
#更新序列的当前值
SELECT SETVAL_zbmxid('item_zbmxid', 10000000);
#使用示例
SELECT currval_fphm('item_zbmxid');
SELECT nextval_fphm('item_zbmxid');
创建时间：2021-09-26 17:22
넶浏览量：0
