[MySQL] SEQUENCE NEXTVAL 구현 프로시져

Table Scheme

CREATE TABLE sequences ( name varchar(32), currval BIGINT UNSIGNED ) ENGINE=InnoDB;

DELIMITER $$
CREATE PROCEDURE `drop_sequence` (IN the_name text)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DELETE FROM sequences WHERE name=the_name;
END

DELIMITER $$
CREATE PROCEDURE `create_sequence`(IN the_name text)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DELETE FROM sequences WHERE name=the_name;
    INSERT INTO sequences VALUES (the_name, 0);
END

DELIMITER $$
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

DELIMITER $$
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

DELIMITER $$
CREATE FUNCTION `market_sms`.`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

사용 예)

call create_sequence('bar');

select nextval('bar'); #1
select currval('bar'); #1
select nextval('bar'); #2
select currval('bar'); #2

저도 사실 프로시져 모르는데 그냥 여기 나온것들 복붙해서 하나씩 실행하니 그냥 되네요

유용한듯!