UNIQUE または PRIMARY KEY として列を宣言するのであれば、暗黙的に生成されるインデックスは大文字小文字を区別します。
MySQLでUNIQUEインデックスは大文字と小文字を区別するか？
データベースのデフォルト設定
MySQL 8.0 以降では、デフォルトで lower_case_table_names 設定が有効になっています。これは、データベース名、テーブル名、カラム名などがすべて小文字に変換されることを意味します。この設定が有効な場合、UNIQUEインデックスも大文字と小文字を区別せず、'abc' と 'ABC' は同じ値として扱われます。
照合順序
UNIQUEインデックスを作成する際に、照合順序を指定することができます。照合順序は、文字列の比較方法を定義します。デフォルトの照合順序は utf8mb4_general_ci であり、これは大文字と小文字を区別しません。
大文字と小文字を区別するUNIQUEインデックスを作成するには、BINARY 属性を指定するか、大文字と小文字を区別する照合順序を指定する必要があります。 例えば、以下のコマンドは、name カラムに大文字と小文字を区別するUNIQUEインデックスを作成します。
CREATE UNIQUE INDEX idx_name ON users (name BINARY);
または、
CREATE UNIQUE INDEX idx_name ON users (name COLLATE utf8mb4_bin);
クライアントの設定
MySQLクライアントの設定によっては、大文字と小文字の区別が影響を受ける場合があります。例えば、--lower-case-table-names オプションを指定すると、すべてのテーブル名が小文字に変換されます。
UNIQUEインデックスで大文字と小文字を区別するかどうかは、以下の要素によって決まります。
- データベースのデフォルト設定
- 照合順序
- クライアントの設定
これらの要素を理解することで、意図したとおりにUNIQUEインデックスを作成することができます。
補足
- MySQL 5.7 以前では、デフォルトで
lower_case_table_names設定が無効でした。このため、UNIQUEインデックスは大文字と小文字を区別していました。 BINARY属性を指定したカラムは、照合順序の影響を受けません。- 大文字と小文字を区別するかどうかは、パフォーマンスやデータ整合性に影響を与える可能性があります。
-- デフォルト設定でUNIQUEインデックスを作成 (大文字と小文字を区別しない)
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE
);
-- BINARY属性を使って大文字と小文字を区別するUNIQUEインデックスを作成
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE BINARY
);
-- 照合順序を使って大文字と小文字を区別するUNIQUEインデックスを作成
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE COLLATE utf8mb4_bin
);
上記のコードを実行すると、以下の3つのUNIQUEインデックスが作成されます。
email: 大文字と小文字を区別しないemail_bin: 大文字と小文字を区別し、照合順序utf8mb4_binを使用する
実行方法
上記のコードは、MySQLクライアントを使用して実行することができます。
mysql -u root -p
パスワードを入力してログイン後、以下のコマンドを実行します。
USE your_database_name;
-- サンプルコードを実行
確認方法
以下のコマンドを実行して、作成されたUNIQUEインデックスを確認することができます。
SHOW INDEXES FROM users;
出力結果は以下のようになります。
| Table | Non_unique | Key_name | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
|---|---|---|---|---|---|---|---|---|---|---|
| users | 0 | PRIMARY | id | A | 1 | NULL | NULL | NO | BTREE | |
| users | 0 | email | email | A | NULL | NULL | NULL | NO | BTREE | |
| users | 0 | email_binary | email | bin | NULL | NULL | NULL | NO | BTREE | |
| users | 0 | email_bin | email | utf8mb4_bin | NULL | NULL | NULL | NO | BTREE | |
Key_name 列に、作成されたUNIQUEインデックスの名前が表示されます。
UNIQUEインデックスで大文字と小文字を区別するかどうかは、状況によって異なります。上記のサンプルコードを参考に、適切なUNIQUEインデックスを作成してください。
UNIQUEインデックスで大文字と小文字を区別する他の方法
照合順序を使用する
CREATE UNIQUE INDEX idx_name ON users (name COLLATE utf8mb4_bin);
BINARY 属性を指定したカラムは、照合順序の影響を受けません。つまり、BINARY 属性を指定したカラムのUNIQUEインデックスは、常に大文字と小文字を区別します。
以下のコマンドは、name カラムに大文字と小文字を区別するUNIQUEインデックスを作成します。
CREATE UNIQUE INDEX idx_name ON users (name BINARY);
CASE式を使用する
UNIQUEインデックスを作成する前に、文字列をすべて大文字または小文字に変換するCASE式を使用することができます。
CREATE UNIQUE INDEX idx_name ON users (LOWER(name));
トリガーを使用する
INSERTまたはUPDATEステートメントが実行されるたびに、文字列をすべて大文字または小文字に変換するトリガーを作成することができます。
以下のトリガーは、users テーブルの name カラムのすべての文字を小文字に変換します。
CREATE TRIGGER trg_name_lowercase
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
SET NEW.name = LOWER(NEW.name);
- 照合順序を使用する方法は、最もシンプルで汎用性の高い方法です。
BINARY属性を使用する方法は、最も効率的な方法です。- CASE式を使用する方法は、既存のテーブルにUNIQUEインデックスを追加する場合に便利です。
- トリガーを使用する方法は、常に大文字または小文字でデータを保存したい場合に便利です。
UNIQUEインデックスで大文字と小文字を区別するには、いくつかの方法があります。上記の情報を参考に、適切な方法を選択してください。
mysql indexing key
