[MariaDB] utf8mb4 에서 인덱스 길이 제한 처리하기

 

10.1.13-MariaDB - MariaDB Server

-- ----------------------------------------------------------------
-- 7. Import history (track which CSV files have been processed)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS import_history (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    filename       VARCHAR(500) NOT NULL,
    file_size      BIGINT,
    rows_total     INT          DEFAULT 0,
    rows_imported  INT          DEFAULT 0,
    rows_skipped   INT          DEFAULT 0,
    status         ENUM('running','completed','failed') DEFAULT 'running',
    started_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    finished_at    DATETIME,
    error_message  TEXT,
    UNIQUE KEY uq_filename (filename(400))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
MySQL 메시지: 문서

#1071 - 정의된 키가 너무 깁니다. 최대 키의 길이는 767입니다.


--> 에러가 발생했다.
MariaDB 10.1.13에서는 utf8mb4 사용 시 인덱스 길이 제한 때문에 발생하는 오류입니다.
MariaDB 10.1(InnoDB)의 최대 인덱스 길이:

767 bytes

utf8mb4 = 문자당 최대 4 bytes 가 되어 제한(767)을 초과합니다.
400 × 4 = 1600 bytes


[해결방법 1 (가장 간단)]

인덱스 길이를 191자로 줄이기
UNIQUE KEY uq_filename (filename(191))

UNIQUE KEY uq_filename (filename(400))  ----> UNIQUE KEY uq_filename (filename(191))  변경

-------------------------------

[해결방법 2 (추천)]

191 x 4 = 764 bytes

filename VARCHAR(191) NOT NULL,
UNIQUE KEY uq_filename (filename)


------------------------------

[해결방법 3 (실무 추천)]

파일명이 길 수 있다면 MD5 해시 저장
filename       VARCHAR(500) NOT NULL,
filename_md5   CHAR(32) NOT NULL,
UNIQUE KEY uq_filename_md5 (filename_md5)

[PHP]
$filename_md5 = md5($filename);

--------------------------------

MariaDB 10.1 기준 최종 추천

CREATE TABLE IF NOT EXISTS import_history (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    filename       VARCHAR(500) NOT NULL,
    filename_md5   CHAR(32) NOT NULL,
    file_size      BIGINT,
    rows_total     INT DEFAULT 0,
    rows_imported  INT DEFAULT 0,
    rows_skipped   INT DEFAULT 0,
    status         ENUM('running','completed','failed') DEFAULT 'running',
    started_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    finished_at    DATETIME,
    error_message  TEXT,
    UNIQUE KEY uq_filename_md5 (filename_md5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



MariaDB 10.1.13은 상당히 오래된 버전이라 utf8mb4 인덱스 관련 제약이 많습니다. 

가능하다면 향후 MariaDB 10.6 이상으로 업그레이드하는 것도 고려해 보시는 것이 좋습니다.