1.創(chuàng)建sequence表

CREATE TABLE `sequence` ( `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT ’序列的名字’, `current_value` int(11) NOT NULL COMMENT ’序列的當(dāng)前值’, `increment` int(11) NOT NULL DEFAULT ’1’ COMMENT ’序列的自增值’, PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2.創(chuàng)建?取當(dāng)前值的函數(shù)

DROP FUNCTION IF EXISTS currval; DELIMITER $ CREATE FUNCTION currval (seq_name VARCHAR(50)) 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 ;

3.創(chuàng)建?取下一個值的函數(shù)

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 ;

4.創(chuàng)建?更新當(dāng)前值的函數(shù)

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 ;

5.測試添加實例 執(zhí)行sql

INSERT INTO sequence VALUES (’testSeq’, 0, 1);-- 添加一個sequence名稱和初始值，以及自增幅度 SELECT SETVAL(’testSeq’, 10);-- 設(shè)置指定sequence的初始值 SELECT CURRVAL(’testSeq’);-- 查詢指定sequence的當(dāng)前值 SELECT NEXTVAL(’testSeq’);-- 查詢指定sequence的下一個值

到此這篇關(guān)于mysql實現(xiàn)自增序列的示例代碼的文章就介紹到這了,更多相關(guān)mysql 自增序列內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！