MySQL自增序列-亲试ok-20201020
#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_fphm;
DELIMITER $
CREATE FUNCTION currval_fphm (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_fphm;
DELIMITER $
CREATE FUNCTION nextval_fphm (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_fphm;
DELIMITER $
CREATE FUNCTION setval_fphm (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_fphm', 0, 1);
#更新序列的当前值
SELECT SETVAL_fphm('item_fphm', 10000000);
#使用示例
SELECT currval_fphm('item_fphm');
SELECT nextval_fphm('item_fphm');
日期增量值 为 内
总的数据量为 5000w，故外循环5000w/15=3333333
创建时间：2021-12-22 10:07
넶浏览量：0
