#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'); select 99999999-nextval_fphm('item_fphm')create table pj_zzspdz_fpmx01 as select * from pj_zzspdz_fpmx where 1=2;日期增量值 为 内总的数据量为 5000w，故外循环5000w/15=3333333
转载于:https://www.cnblogs.com/iyoume2008/p/10096515.html
