mysql 스쿼스

mysql에서는 스쿼스가 없다..ㅜ.ㅜ

아래와 같이 테이블을 만들고 함수 및 프로시저를 만들어서 사용한다.

--테이블생성
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

사용 예)

call create_sequence('bar');

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