MySQL/MariaDBでutf8mb4文字コードを使用する際のユニークキー制約と特殊文字の取り扱い

2024-05-11

MySQL/MariaDBにおけるutf8mb4文字コードとユニークキーの取り扱い

MySQL/MariaDBでutf8mb4文字コードを使用する場合、ユニークキー制約において特殊な文字が重複する可能性があり、意図しないデータ重複が発生するケースがあります。

問題点

utf8mb4は、utf8よりも幅広い文字表現を可能にする文字コードです。しかし、utf8mb4では、同じ文字が複数の表現方法を持つ場合があります。例えば、"é"という文字は、NFD（正規化分解形式）とNFC（正規化合成形式）で表現できます。

NFD:   e\x{301}\x{332}
NFC:   \x{E1}

MySQL/MariaDBは、デフォルトでNFD形式で文字を格納します。そのため、utf8mb4を使用する場合、éのような文字は、NFDとNFCの両方の形式でユニークキーとして登録されてしまいます。

解決策

この問題を解決するには、以下の方法があります。

utf8mb4_bin文字コードを使用する

utf8mb4_binは、utf8mb4と同様の文字表現範囲を持つ文字コードですが、文字の正規化を行いません。そのため、éのような文字は、常に1つの形式で格納されます。

COLLATE句を使用する

COLLATE句を使用すると、文字の照合方法を指定できます。utf8mb4_unicode_ciなどの照合順序を使用すると、文字の正規化が行われ、éのような文字は常に1つの形式で比較されます。

ユニークキーの比較方法を明示的に指定する

BINARYキーワードを使用すると、文字列をバイナリデータとして比較できます。NFDやNFCなどの正規化形式を明示的に指定することもできます。

例

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) COLLATE utf8mb4_unicode_ci UNIQUE
);

-- utf8mb4_binを使用する場合
CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin UNIQUE
);

-- バイナリ比較を使用する場合
CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin UNIQUE KEY BINARY(name)
);

-- NFD形式で比較する場合
CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NFD UNIQUE
);

-- NFC形式で比較する場合
CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NFC UNIQUE
);

注意事項

utf8mb4_binを使用すると、文字の正規化が行われないため、検索やソートの際に問題が発生する可能性があります。
COLLATE句を使用する場合は、データベース全体で統一的に使用する必要があります。
ユニークキーの比較方法を明示的に指定する場合は、パフォーマンスが低下する可能性があります。

MySQL/MariaDBにおけるutf8mb4文字コードとユニークキーの取り扱い：サンプルコード

NFD:

e\x{301}\x{332}

\x{E1}

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin UNIQUE
);

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) COLLATE utf8mb4_unicode_ci UNIQUE
);

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin UNIQUE KEY BINARY(name)
);

NFD形式で比較する

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NFD UNIQUE
);

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NFC UNIQUE
);

説明

上記のサンプルコードは、それぞれ異なる方法でutf8mb4文字コードとユニークキーを扱う例です。

この例では、COLLATE句を使用してutf8mb4_unicode_ci照合順序を指定しています。utf8mb4_unicode_ci照合順序は、文字の正規化を行い、éのような文字は常に1つの形式で比較されます。

この例では、NFD照合順序を使用して、文字列をNFD形式で比較しています。

上記以外にも、utf8mb4文字コードとユニークキーを扱う方法はいくつかあります。詳細は、MySQL/MariaDBの公式ドキュメントを参照してください。

MySQL/MariaDBにおけるutf8mb4文字コードとユニークキーの取り扱い：他の方法

上記で紹介した方法以外にも、utf8mb4文字コードとユニークキーを扱う方法はいくつかあります。

サブ文字列を使用する

特殊文字を含まない部分文字列をユニークキーとして使用することができます。例えば、éという文字であれば、eという部分文字列をユニークキーとして使用できます。

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci SUBSTRING(name FROM 1 FOR 1) UNIQUE
);

ハッシュ関数を使用する

特殊文字を含む文字列をハッシュ関数で変換し、ハッシュ値をユニークキーとして使用することができます。

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  name_hash BINARY(16) UNIQUE KEY USING HASH(name)
);

正規表現を使用して、特殊文字を除去した文字列をユニークキーとして使用することができます。

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci REGEXP_REPLACE(name, '[^a-zA-Z0-9_]', '') UNIQUE
);