울면서개발하기MySQL information_schema 로 MyBatis CRUD SQL 자동 생성하기
MyBatis 매퍼를 만들 때마다 열 수십 개를 손으로 옮겨 적는 일이 반복된다. information_schema 에서 열 정보를 읽어 기본 CRUD 문을 만들어 주는 쿼리를 쓰고 있다. MySQL 8.0 이상(창 함수·CTE 사용)에서 동작하고, MySQL 8.4 LTS 기준으로 정리했다.
예제 테이블
CREATE TABLE APP_ROLE (
ROLE_CD VARCHAR(20) NOT NULL COMMENT '권한코드',
PARENT_ROLE_CD VARCHAR(20) NULL COMMENT '상위권한코드',
ROLE_NM VARCHAR(100) NOT NULL COMMENT '권한명',
NOTE VARCHAR(500) NULL COMMENT '비고',
CREATED_BY VARCHAR(50) NOT NULL COMMENT '입력자',
CREATED_AT DATETIME NOT NULL COMMENT '입력일시',
UPDATED_BY VARCHAR(50) NOT NULL COMMENT '수정자',
UPDATED_AT DATETIME NOT NULL COMMENT '수정일시',
PRIMARY KEY (ROLE_CD)
);CREATED_*, UPDATED_* 는 감사(audit) 열이다. 화면에서 받는 값이 아니라 로그인 사용자 ID 와 현재 시각으로 채운다.
생성 쿼리
SET SESSION group_concat_max_len = 1000000; -- 기본값 1024 바이트면 결과가 잘린다
WITH COLS AS (
SELECT C.TABLE_NAME
, C.COLUMN_NAME
, C.ORDINAL_POSITION AS POS
, C.COLUMN_COMMENT AS CMT
, CASE WHEN K.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS IS_PK
, CASE WHEN C.EXTRA LIKE '%auto_increment%' THEN 1 ELSE 0 END AS IS_AUTO
, CASE WHEN IFNULL(C.GENERATION_EXPRESSION, '') <> '' THEN 1 ELSE 0 END AS IS_GEN
, CASE WHEN C.COLUMN_NAME IN ('CREATED_BY', 'CREATED_AT') THEN 1 ELSE 0 END AS IS_CREATED
-- 바인딩할 값: 감사 열은 고정값, 나머지는 #{열이름}
, CASE WHEN C.COLUMN_NAME IN ('CREATED_AT', 'UPDATED_AT') THEN 'NOW()'
WHEN C.COLUMN_NAME IN ('CREATED_BY', 'UPDATED_BY') THEN CONCAT('#', '{loginId}')
ELSE CONCAT('#', '{', C.COLUMN_NAME, '}')
END AS VAL
-- 줄 맞춤용 최대 열 이름 길이
, MAX(CHAR_LENGTH(C.COLUMN_NAME)) OVER () AS W
FROM information_schema.COLUMNS C
LEFT JOIN information_schema.KEY_COLUMN_USAGE K
ON K.TABLE_SCHEMA = C.TABLE_SCHEMA
AND K.TABLE_NAME = C.TABLE_NAME
AND K.COLUMN_NAME = C.COLUMN_NAME
AND K.CONSTRAINT_NAME = 'PRIMARY'
WHERE C.TABLE_SCHEMA = 'mydb' -- 스키마(데이터베이스) 이름
AND C.TABLE_NAME = 'APP_ROLE' -- 테이블 이름 (실제 대소문자 그대로)
)
SELECT
-- SELECT
CONCAT('SELECT ',
GROUP_CONCAT(CONCAT('A.', RPAD(COLUMN_NAME, W, ' '), ' AS ',
IF(CMT = '', COLUMN_NAME, CONCAT(RPAD(COLUMN_NAME, W, ' '), ' -- ', CMT)))
ORDER BY POS SEPARATOR '\n , '),
'\n FROM ', TABLE_NAME, ' A',
'\n WHERE ',
GROUP_CONCAT(IF(IS_PK = 1, CONCAT('A.', COLUMN_NAME, ' = #', '{', COLUMN_NAME, '}'), NULL)
ORDER BY POS SEPARATOR '\n AND ')
) AS SELECT_SQL
-- INSERT: 자동 증가 열과 생성 열은 뺀다
, CONCAT('INSERT INTO ', TABLE_NAME, ' (\n ',
GROUP_CONCAT(IF(IS_AUTO = 1 OR IS_GEN = 1, NULL, COLUMN_NAME)
ORDER BY POS SEPARATOR '\n , '),
'\n) VALUES (\n ',
GROUP_CONCAT(IF(IS_AUTO = 1 OR IS_GEN = 1, NULL, VAL)
ORDER BY POS SEPARATOR '\n , '),
'\n)'
) AS INSERT_SQL
-- UPDATE: PK, 생성 열, CREATED_* 는 바꾸지 않는다
, CONCAT('UPDATE ', TABLE_NAME,
'\n SET ',
GROUP_CONCAT(IF(IS_PK = 1 OR IS_GEN = 1 OR IS_CREATED = 1, NULL,
CONCAT(RPAD(COLUMN_NAME, W, ' '), ' = ', VAL))
ORDER BY POS SEPARATOR '\n , '),
'\n WHERE ',
GROUP_CONCAT(IF(IS_PK = 1, CONCAT(COLUMN_NAME, ' = #', '{', COLUMN_NAME, '}'), NULL)
ORDER BY POS SEPARATOR '\n AND ')
) AS UPDATE_SQL
-- DELETE
, CONCAT('DELETE\n FROM ', TABLE_NAME,
'\n WHERE ',
GROUP_CONCAT(IF(IS_PK = 1, CONCAT(COLUMN_NAME, ' = #', '{', COLUMN_NAME, '}'), NULL)
ORDER BY POS SEPARATOR '\n AND ')
) AS DELETE_SQL
FROM COLS
GROUP BY TABLE_NAME;쿼리를 읽을 때 알아 둘 것:
GROUP_CONCAT은NULL을 건너뛴다. 그래서IF(조건, NULL, 값)으로 넣을 열과 뺄 열을 고른다'\n'은 MySQL 문자열 안에서 줄바꿈이다 (sql_mode에NO_BACKSLASH_ESCAPES가 있으면 그대로\n두 글자가 된다)'#', '{'로 쪼갠 이유: 이 생성 쿼리 자체를 MyBatis 매퍼에 넣어 실행하면 MyBatis 가 문자열 안의#{...}도 파라미터로 바꿔 버린다. DB 클라이언트에서만 돌린다면'#{'로 써도 된다- PK 는 MySQL 에서 제약 이름이 항상
PRIMARY라서KEY_COLUMN_USAGE로 찾았다. PK 가 없는 테이블은WHERE뒤에 붙을 값이NULL이라CONCAT전체가NULL이 되어, SELECT·UPDATE·DELETE 문이NULL로 나온다 - 감사 열 이름과
#{loginId}는 프로젝트 규칙에 맞게 바꾼다
결과
APP_ROLE 로 돌리면 이렇게 나온다.
SELECT A.ROLE_CD AS ROLE_CD -- 권한코드
, A.PARENT_ROLE_CD AS PARENT_ROLE_CD -- 상위권한코드
, A.ROLE_NM AS ROLE_NM -- 권한명
, A.NOTE AS NOTE -- 비고
, A.CREATED_BY AS CREATED_BY -- 입력자
, A.CREATED_AT AS CREATED_AT -- 입력일시
, A.UPDATED_BY AS UPDATED_BY -- 수정자
, A.UPDATED_AT AS UPDATED_AT -- 수정일시
FROM APP_ROLE A
WHERE A.ROLE_CD = #{ROLE_CD}INSERT INTO APP_ROLE (
ROLE_CD
, PARENT_ROLE_CD
, ROLE_NM
, NOTE
, CREATED_BY
, CREATED_AT
, UPDATED_BY
, UPDATED_AT
) VALUES (
#{ROLE_CD}
, #{PARENT_ROLE_CD}
, #{ROLE_NM}
, #{NOTE}
, #{loginId}
, NOW()
, #{loginId}
, NOW()
)UPDATE APP_ROLE
SET PARENT_ROLE_CD = #{PARENT_ROLE_CD}
, ROLE_NM = #{ROLE_NM}
, NOTE = #{NOTE}
, UPDATED_BY = #{loginId}
, UPDATED_AT = NOW()
WHERE ROLE_CD = #{ROLE_CD}DELETE
FROM APP_ROLE
WHERE ROLE_CD = #{ROLE_CD}원래 쿼리에서 고친 것
처음 쓴 쿼리는 Oracle 용 생성 쿼리를 옮긴 것이었는데, 쓰다 보니 버그가 몇 개 있었다.
GROUP_CONCAT(DISTINCT …) 때문에 INSERT 값이 빠졌다
원래 쿼리는 모든 GROUP_CONCAT 에 DISTINCT 를 붙였다. DISTINCT 는 만들어진 문자열의 중복을 없앤다. 열 이름은 겹치지 않으니 열 목록은 멀쩡하지만, 값 목록에서는 입력자와 수정자가 둘 다 #{loginId}, 입력일시와 수정일시가 둘 다 NOW() 라서 하나씩 사라진다. 실제로 원래 글의 예시 결과도 INSERT 열은 18개인데 값은 14개였다. 이 상태로 실행하면 Column count doesn't match value count 오류가 난다. 열 이름이 겹칠 일이 없으니 DISTINCT 는 필요 없다.
테이블 이름 대소문자
원래 쿼리는 SELECT·INSERT 에는 UPPER(테이블명) 을, UPDATE·DELETE 에는 입력값을 그대로 써서 결과가 제각각이었다. MySQL 에서 테이블 이름의 대소문자 구분은 lower_case_table_names 설정에 따라 다르다. 리눅스 기본값(0)은 구분하므로, 소문자로 만든 테이블을 UPPER() 로 바꿔 쓰면 테이블을 못 찾는다. information_schema 에 저장된 실제 이름(TABLE_NAME)을 그대로 쓰도록 고쳤다.
줄 맞춤 길이
원래는 감사 열을 빼고 최대 길이를 구한 뒤 RPAD 로 맞췄다. RPAD 는 지정한 길이보다 긴 문자열을 잘라 버리기 때문에, 감사 열 이름이 다른 열보다 길면 열 이름이 잘린 SQL 이 나온다. 모든 열을 넣어 최대 길이를 구하고, 바이트 수인 LENGTH 대신 글자 수인 CHAR_LENGTH 를 쓴다.
자동 증가 열과 생성 열
AUTO_INCREMENT열은 INSERT 에서 뺐다. 만들어진 키가 필요하면 MyBatis<insert useGeneratedKeys="true" keyProperty="...">를 쓴다- 생성 열(
GENERATED ALWAYS AS)은 값을 넣거나 바꿀 수 없으므로 INSERT·UPDATE 에서 뺐다.information_schema.COLUMNS.GENERATION_EXPRESSION이 비어 있지 않으면 생성 열이다.EXTRA로 판단하면DEFAULT CURRENT_TIMESTAMP열에 붙는DEFAULT_GENERATED까지 걸리므로 주의한다
다시 쓰며 바로잡은 점
GROUP_CONCAT(DISTINCT ...)때문에 INSERT 의 값 개수가 열 개수보다 적어지는 버그를 고쳤다- 테이블 이름을
UPPER()로 바꾸지 않고information_schema의 실제 이름을 쓰도록 했다 (lower_case_table_names에 따라 대소문자 구분) RPAD가 긴 열 이름을 자르는 문제,LENGTH(바이트)와CHAR_LENGTH(글자) 차이를 고쳤다- 자동 증가 열·생성 열 처리를 추가하고, MyBatis 파라미터(
#{OWNER},#{TABLE_NAME})로 받던 스키마·테이블 이름을 문자열 값으로 바꿔 DB 클라이언트에서 바로 돌릴 수 있게 했다 - 업무 테이블과 열 이름은 일반적인 예시(
APP_ROLE)로 바꿨다