[MySQL] 이모지가 포함된 컬럼에 UQ 제약조건 걸기

문제

작업 중에 임의의 리소스에 할당된 리액션들을 이모지 형태로 관리할 필요가 있어서 아래와 같은 테이블을 설계했다. 

CREATE TABLE `Reactions`
(
    `Id`                INT(11)                         NOT NULL AUTO_INCREMENT,
    `ResourceId`        INT(11)                         NOT NULL,
    `Value`             VARCHAR(20)                     NOT NULL,
    `CreatedAt`         DATETIME                        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `CreatedById`       VARCHAR(255) CHARACTER SET utf8 NOT NULL,
    PRIMARY KEY (`Id`),
    UNIQUE `Reactions_uq_1` (`ResourceId` ASC, `Value` ASC, `CreatedById` ASC)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

해당 테이블은 어떠한 리소스(=ResourceId)에 어떤 이모지(=Value, 👍와 같은 형태의 데이터를 그대로 삽입)이 할당되었는지를 관리한다. 동일한 사용자가 동일한 리소스에 같은 리액션을 남길 수 있도록 UQ 제약 조건을 걸어주었고, 일반적으로 MySQL 테이블에 이모지를 삽입하는 경우 utf8mb4 인코딩을 사용하므로 나도 이를 따랐다.

그런데 ResourceId / Value / CreatedById가 중복되지 않는 데이터를 삽입하더라도 아래와 같은 예외가 발생한다.

[23000][1062] Duplicate entry '3-?-ingnoh' for key 'Reactions_uq_1'

예외가 뜬다니 어쩔 수 없지. 유니코드나 HEX로 변환하여 DB에 삽입할까 했지만, 뭔가 기분이 나빠서 원인을 찾아봤다.

 

원인과 해결

문자열의 정렬에 사용되는 컬레이션 중 utf8mb4_unicode_ci는 모든 이모지를 동일한 문자로 취급한다고 한다.

나는 MySQL 5.7을 기반으로 작업하고 있었는데, 요기서 주로 사용되는 utf8mb4_general_ci나 utf8mb4_unicode_ci와 같이 case insensitive한 컬레이션에는 위와 같은 문제가 있다고 한다. 사실상 MySQL의 버그처럼 취급되는 듯 한데, 어찌됐건 간에 컬레이션을 utf8mb4_unicode_520_ci이나 utf8mb4_bin으로 바꿔주자.

---

참고

이번 문제도 인터넷 상의 귀인들의 도움을 받았다. 어찌보면 MySQL 5.7을 안쓰면 되는 문젠데, 현실은 녹록치 않았다.

MySQL UTF8MB4 Charset의 Collation 선택 (대소문자, 전각/반각, emoji 문자 구분)

MySQL 8.0 - 기본 CharacterSet 의 변경

MySQL Bugs: #87700: Strict comparisons of UTF8MB4 Values Fail and resolve to the same 'placeholder'