[MySQL] MySQL 시퀀스 테이블과 프로시저 정의 및 호출

𐂂 MySQL에서 PRIMARY KEY가 아니면 AUTO_INCREMENT를 사용할 수 없다.

더불어, MySQL은 시퀀스 기능을 지원하지 않는다.
따라서, 시퀀스 기능을 하는 시퀀스 테이블을 만들어 시퀀스 기능을 구현해야 한다.
이 시퀀스 테이블을 만드는 과정에서 프로시저를 정의하고 호출해야한다.

𐂂 프로시저 (Procedure) 란

일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합을 말한다.

🎯 목적:
- 반복적 작업: 프로시저를 호출하여 코드의 🧩 재사용성 증가
- 복잡한 작업: 여러 단계로 구성된 프로시저를 작성하여, 코드를 관리가능한 단위로 분리⭕
- 트랜잭션 관리: 여러개의 SQL문을 하나의 트랜잭션으로 묶어 데이터 🧩 일관성 유지
- 보안상 이점: 사용자에게 직접적인 데이터베이스 접근 허용❌

𐁍 프로시저 언어: 정의하고 호출

  CREATE TABLE sequences (
      name VARCHAR(32), # 시퀀스 이름
      currval BIGINT UNSIGNED # 시퀀스 현재값
  ) ENGINE = InnoDB

🦉 스토리지 엔진 (Storage ENGINE)
: DBMS 내에서 데이터를 저장/인덱싱/트랜잭션 처리/캐싱/암호화/접근방식 등을 정의하는 핵심 컴포넌트를 말한다.
(InnoDB는 트랜잭션 지원과 관계형 데이터베이스 기능을 강화한 스토리지 엔진을 말한다)

📜 시퀀스 생성하는 프로시저 정의.sql

  DELEMETER $$ # 구분기호 세미콜론(;)을 $$로 변경
      CREATE PROCEDURE `create_sequence` (IN the_name text) # 프로시저 정의, 입력 매개변수 the_name
      MODIFIES SQL DATA
      DETERMINISTIC
      BEGIN # 프로시저 본문의 시작
          DELETE FROM sequences WHERE name = the_name; # 해당 이름의 시퀀스 초기화작업1
          INSERT INTO sequences VALUES(the_name, 0); # 해당 이름의 시퀀스 초기화작업2
      END $$ # 프로시저 끝
  DELEMETER ; # 구분기호 $$을 세미콜론(;)으로 변경

🦉 SQL문 해석
MODIFIES SQL DATA: 해당 프로시저가 데이터를 수정할 수 있다는 선언
DETERMINISTIC: 해당 프로시저가 동일한 입력에 대해 동일한 결과를 항상 생성하는 함수처럼 동작
(쿼리최적화나 캐싱 작업 수행가능)

🦉 구분기호(DELEMETER)를 왜 변경할까
: 프로시저 본문 내부에서 세미콜론(;)을 사용하면 프로시저 정의 자체가 끝났다고 인식한다.
따라서, 본문내에서 사용하는 세미콜론(;)을 $$로 바꿔줌으로써 본문 내 구분자로 사용한다.

📜 시퀀스 기능을 하는 함수 생성.sql

  DELEMETER $$
    CREATE FUNCTION `nextval` (the_name VARCHAR(32))
      RETURNS BIGINT UNSIGNED
      MODIFIES SQL DATA
      DETERMINISTIC
      BEGIN
          DECLARE ret BIGINT UNSIGNED; # 변수 ret 선언
          UPDATE sequences SET currval = currval + 1 WHERE name = the_name; # currval 값을 1씩 증가
          SELECT currval INTO ret FROM sequences WHERE name = the_name LIMIT 1; # 업데이트된 시퀀스 값을 변수 ret에 저장
          RETURN ret; # ret 반환
      END $$
  DELEMETER ;

📜 시퀀스 생성 프로시저 호출.sql

  CALL create_sequence('Cart'); # Cart라는 이름의 시퀀스 생성

📜 시퀀스 기능 사용.sql

  SELECT nextval('Cart') FROM DUAL;