MySQLで大文字小文字区別せず重複しないようにデータを保存する #MySQL

やりたかったこと

case insensitiveの (最後に_ciがつく) Collation(照合順序)を選択し、UNIQUE制約を貼ったときに、本当に重複しないかを試してみました。

Character setとCollationについて

他の記事で詳しく説明されているので今回は割愛します。

utf8mb4_general_ciなど、最後が_ciとなっているものは、大文字小文字を区別しないため、'example' = 'EXAMPLE'と判定されます。

実験

DockerでMySQLを立ち上げ、複数の照合順序のテーブルを作り、重複しないかデータを投入してみます。

MySQL 5.7のDocker準備

各種ファイルを作っておきます。

docker-compose.yml

version: '3'
services:
  db:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test
      MYSQL_USER: user1
      MYSQL_PASSWORD: password
    ports:
      - 3306:3306
    volumes:
      - ./db/my.cnf:/etc/mysql/conf.d/my.cnf
      - ./db/init:/docker-entrypoint-initdb.d
      - ./db/data:/var/lib/mysql

デフォルトのCharacter SetとCollationを定義。

./db/my.cnf

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

[client]
default-character-set=utf8mb4

collationの異なる3つのテーブルを作成します。

./db/init/init.sql

DROP TABLE IF EXISTS `utf8mb4_bin`;
CREATE TABLE `utf8mb4_bin` (
  `id` INT(11) AUTO_INCREMENT,
  `email` VARCHAR(255),
  PRIMARY KEY (`Id`),
  UNIQUE `idx_email` (`email`)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TAble IF EXISTS `utf8mb4_general_ci`;
CREATE TABLE `utf8mb4_general_ci` (
  `id` INT(11) AUTO_INCREMENT,
  `email` VARCHAR(255),
  PRIMARY KEY (`Id`),
  UNIQUE `idx_email` (`email`)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

DROP TABLE IF EXISTS `utf8mb4_unicode_ci`;
CREATE TABLE `utf8mb4_unicode_ci` (
  `id` INT(11) AUTO_INCREMENT,
  `email` VARCHAR(255),
  PRIMARY KEY (`Id`),
  UNIQUE `idx_email` (`email`)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

docker-compose upをするとMySQLが立ち上がります。

データを投入してみる

MySQLが立ち上がったら接続し、それぞれのテーブルにメールアドレスを入れていきます。

INSERT INTO utf8mb4_bin (email) VALUES ('sample@example.com');
INSERT INTO utf8mb4_general_ci (email) VALUES ('sample@example.com');
INSERT INTO utf8mb4_unicode_ci (email) VALUES ('sample@example.com');

次に重複判定されるかテストしてみると、utf8mb4_binのテーブル以外は重複判定されました。

INSERT INTO utf8mb4_bin (email) VALUES ('SAMPLE@example.com');

INSERT INTO utf8mb4_general_ci (email) VALUES ('SAMPLE@example.com');
  -- Duplicate entry 'SAMPLE@example.com' for key 'idx_email'

INSERT INTO utf8mb4_unicode_ci (email) VALUES ('SAMPLE@example.com');
  -- Duplicate entry 'SAMPLE@example.com' for key 'idx_email'

insertではなく、updateのタイミングでも重複判定されました。

INSERT INTO utf8mb4_general_ci (email) VALUES ('hoge@example.com');
UPDATE utf8mb4_general_ci SET email = 'SAMPLE@example.com' WHERE email = 'hoge@example.com';
  -- Duplicate entry 'SAMPLE@example.com' for key 'idx_email'

最後にalter tableでも重複判定してくれました。

ALTER TABLE utf8mb4_bin MODIFY email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  -- Duplicate entry 'SAMPLE@example.com' for key 'idx_email'

おわりに

collationはあまり意識せずデフォルト値のままだと大文字小文字区別してくれないので、逆に区別したいときは注意が必要です。
普段あまり意識せずに使ってきていたので、色々実験してみて理解が深まりました。