前几天朋友让我帮忙看下 写的序列的函数。 因为他们公司原来用的是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
测试下还可以 就是简单的东西了 我就不多了。
由于下面资料比较多 写在下一个博客里 如果大家看到的话
但是他用程序做了很慢,并发高了之后出现重复。
多以做了测试
创建序列 直接谷歌 找了靠谱的已经写好的程序
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博客 ” ,链接:http://blog.itpub.net/29896444/viewspace-1836222/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29896444/viewspace-1836222/