前几天朋友让我帮忙看下写的序列的函数。因为他们公司原来用的是postgresql 想换成mysql 但是程序里面用了很多序列生成器。
但是他用程序做了很慢，并发高了之后出现重复。

多以做了测试

创建序列直接谷歌找了靠谱的已经写好的程序

DROP TABLE IF EXISTS sequences;
CREATE TABLE sequences (name varchar(32), currval BIGINT UNSIGNED) ENGINE=InnoDB; #row-level locking

DELIMITER //
DROP PROCEDURE IF EXISTS drop_sequence//
CREATE PROCEDURE drop_sequence (IN the_name text)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DELETE FROM sequences WHERE name=the_name;
END;//

DROP PROCEDURE IF EXISTS create_sequence//
CREATE PROCEDURE create_sequence (IN the_name varchar(32))
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DELETE FROM sequences WHERE name=the_name;
INSERT INTO sequences VALUES(the_name, 0);
END; //

DROP FUNCTION IF EXISTS nextval//
CREATE FUNCTION nextval (the_name varchar(32))
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DECLARE ret BIGINT UNSIGNED;
UPDATE sequences SET currval=currval+1 WHERE name=the_name;
SELECT currval INTO ret FROM sequences WHERE name=the_name limit 1;
RETURN ret;
END; //

DROP FUNCTION IF EXISTS currval//
CREATE FUNCTION currval (the_name varchar(32))
RETURNS BIGINT UNSIGNED
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE ret BIGINT UNSIGNED;
SELECT currval INTO ret FROM sequences WHERE name=the_name limit 1;
RETURN ret;
END; //

DROP FUNCTION IF EXISTS setval//
CREATE FUNCTION setval (the_name varchar(32), the_val BIGINT UNSIGNED)
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
DECLARE ret BIGINT UNSIGNED;
UPDATE sequences SET currval=the_val WHERE name=the_name;
SELECT currval INTO ret FROM sequences WHERE name=the_name limit 1;
RETURN ret;
END; //

DELIMITER ;

# tests

# simple test
call create_sequence('bar'); # 0
select nextval('bar'); # 1
select currval('bar'); # 1
select nextval('bar'); # 2
select nextval('bar'); # 3

测试下还可以就是简单的东西了我就不多了。
由于下面资料比较多写在下一个博客里如果大家看到的话

ITPub博客

关于解释mysql 序列问题的测试一

最新文章