본문 바로가기

웹개발/mysql

[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


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


유용한듯!

'웹개발 > mysql' 카테고리의 다른 글

mysql errcode 28  (0) 2018.04.24
mysql 재귀(recursive) 쿼리, heirarchy 구조  (3) 2018.01.08
MariaDb 설치 - 윈도우, 리눅스  (0) 2017.07.14