--这是为 MySQL 编写的简易序列存储程序（一个函数）。
--在使用序列之前，需要通过往序列记录表中加入数值的方式初始化序列：
--INSERT INTO Sequence ('name...', 0);
--之后，就可以通过 nextval('name...') 函数来取得名为 name... 的序列新值。
--考虑到 update 自身的行锁能力。所以本序列程序具有很强的并发提供能力。
DROP TABLE IF EXISTS Sequence;
CREATE TABLE Sequence
(
name VARCHAR(32),
maxIndex INTEGER,
CONSTRAINT cst_Sequence_pk PRIMARY KEY (name, maxIndex)
) DEFAULT CHARACTER SET utf8
ENGINE InnoDB;
SET GLOBAL log_bin_trust_function_creators = 1;
DROP FUNCTION IF EXISTS nextval;
DELIMITER //
CREATE FUNCTION nextval(sequenceName VARCHAR(32))
RETURNS INTEGER
NOT DETERMINISTIC
CONTAINS SQL
/* ********** ********** ********** **********
This is a common sequence function. It is based on table Sequence.
This function will return new integer by given sequence name.
author: Shane Loo Li
version: 1.1.1, 2012-7-17 Tuesday Modified edition.
history:
1.1.0, 2012-7-14 Saturday Shane Loo Li New
1.1.1, 2012-7-17 Tuesday Shane Loo Li Modified wrong column name.
********** ********** ********** ********** */
BEGIN
DECLARE iMaxIndex INTEGER;
UPDATE Sequence SET maxIndex = maxIndex + 1 WHERE name = sequenceName;
SELECT maxIndex INTO iMaxIndex
FROM Sequence WHERE name = sequenceName;
RETURN iMaxIndex;
END
//
DELIMITER ;
|
