[MariaDB] 변수 사용해서 시퀀스 테이블 초기화하기

mariadb에서 시퀀스 테이블을 생성하거나 혹은 변경할 때 변수를 사용할 수 없는 제약이 있습니다. 이는 동적 쿼리를 이용하여 해결이 가능한데 간단하게 공유드립니다.

MariaDB 설치 및 실행

먼저 docker를 이용해서 maria db를 설치합니다. 

가장 간단하게 mariadb를 실행하는 방법은 terminal에서 docker 명령어로 실행시키는 방법입니다.

docker run -d \
  --name=db \
  -p 3307:3306 \
  -e MYSQL_ROOT_PASSWORD=password \
  -e MYSQL_DATABASE=mariadb_test \
  --restart always \
  mariadb:10.3.8 \
  mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci

 

단일 애플리케이션을 실행할 때 보통 위와 같이 사용하는데 이렇게 하면 매번 커맨드를 입력해야 하는 번거로움이 있습니다. 매번 커맨드를 입력하지 않도록 하는 방법도 있습니다. mariadb_start.sh 파일을 만들고 아래 내용을 입력하고 파일에 실행 권한을 주고 필요할 때마다 쉘스크립트 파일을 실행하면 됩니다. 

#!/bin/bash
docker run -d \
  --name=db \
  -p 3307:3306 \
  -e MYSQL_ROOT_PASSWORD=password \
  -e MYSQL_DATABASE=mariadb_test \
  --restart always \
  mariadb:10.3.8 \
  mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci

하지만 위 역시도 단점이 있습니다. 명령어를 해석하기 좀 불편하다는 점입니다. 한눈에 명령어의 구성이 어떻게 돼있는지 보고 싶습니다. 때문에 저는 docker-compose를 선호합니다. 여러 컨테이너를 이용할 건 아니지만 위의 두 가지 방식들의 단점을 커버하는 것 같습니다. docker-compose.yml 파일을 만들고 chmod를 이용해서 실행권한을 주고 아래 내용을 입력해 줍니다.

version: '3.1'

services:

  db:
    image: mariadb:10.3.8
    restart: always
    command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci
    ports:
      - 3307:3306
    environment:
      - MYSQL_ROOT_PASSWORD=password
      - MYSQL_DATABASE=mariadb_test

데이터 세팅하기

MariaDB 접속

mysql -u root -h 127.0.0.1 -P 3307 -p

패스워드는 "password"입니다.

TMI) MariaDB는 MySQL 프로젝트의 fork로 MySQL과 호환성이 높기에 클라이언트 프로그램도 MySQL 동일하게 mysql를 사용하는 것 같습니다. 

MariaDB의 특정 기능을 사용하기 위한 전용 클라이언트(mariadb-dump, mariadb-import, mariadb-secure-installation)도 있습니다.

use mariadb_test;

테이블 생성 및 데이터 초기화

create table test(id int primary key);

insert into test values (1),(2),(3),(4),(5);

select * from test;

 

시퀀스 테이블 생성 (= 실패 테스트)

set @max_id = (select max(id) + 1 from test);

select @max_id;

create sequence test_seq start with @max_id;

변수 max_id에 6을 저장하고 시퀀스 생성을 할 때 변수를 사용하도록 하면 아래와 같이 오류가 납니다.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@max_id' at line 1

동적 쿼리를 이용해서 시퀀스 테이블 생성

execute immediate CONCAT('create sequence test_seq start with ', @max_id);

select * from test_seq;

위의 경우 제대로 시퀀스 테이블이 생성됨을 알 수 있습니다.

두 개의 차이가 뭘까?

create sequence test_seq start with @max_id;

VS

execute immediate concat('create sequence test_seq start with ', @max_id);

MariaDB에서는 기본적으로 시퀀스 테이블을 생성할 때 변수를 사용하지 못하도록 제약을 걸어두었다고 합니다. 따라서 첫 번째 쿼리를 실행했을 때 SQL 파서가 구문을 분석하는 시점에 변수를 사용하는 것을 인지하고 오류를 내뱉은 것이 아닐까 싶습니다.

반면 동적 쿼리를 실행할 때는 concat('create sequence test_seq start with ', @max_id) 명령어가 먼저 실행이 될 텐데 이때 변수 사용은 문제가 되지 않습니다. 그럼 결론적으로 concat의 결과로 "create sequence test_seq start with 6"라는 문자열이 생성되었을 겁니다. 이때 변수를 사용한 것이 아니라 구체적인 숫자를 넣어주었으므로 실행하면 성공하는 것으로 보입니다.