MySQLでユニークインデックスを貼る際のDuplicate entry fuga for key hoge エラーについてです。
まず、ユニークインデックスを貼る際の構文については以下の通りです。
alter table {テーブル名} add unique({インデックスを貼るカラム});インデックスを貼るカラムはカンマ「,」区切りで複数指定できます。
ユニークインデックスを貼る際のDuplicateエラーがどんな場合に発生するか記載していきます。
MySQL8系とMySQL5.7系で若干挙動が異なります。具体的には以下の挙動となります。
Duplicateエラーの発生条件
MySQL8系とMySQL5.7系ともに複数レコードに同じ値があるカラムにはユニークインデックスは貼れない(Duplicateエラーになる)
MySQL8系とMySQL5.7系ともに複数レコードにNULLがあるカラムにはユニークインデックスは貼れる
MySQL5.7系の場合、末尾の半角スペースのみの違いしかない値が複数レコードあるカラムにはユニークインデックスは貼れない(Duplicateエラーになる)(MySQL8系では貼れる)
検証時のコマンドと結果を記載していきます。
MySQL8系
まずバージョン確認
select version();
+-----------+
| version() |
+-----------+
| 8.0.37 |
+-----------+
複数レコードに同じ値があるカラム場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, 'test_user'),(2, 'test_user');
# ユニークインデックスを貼る
alter table user add unique(name);
# 結果：エラー
ERROR 1062 (23000): Duplicate entry 'test_user' for key 'user.name'
# テーブル削除
drop table user;
複数レコードにNULLがあるカラム場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, null),(2, null);
# ユニークインデックスを貼る
alter table user add unique(name);
# 結果：成功
Query OK, 0 rows affected (0.02 sec)
# テーブル削除
drop table user;末尾にスペースがある場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, 'test_user'),(2, 'test_user ');
# ユニークインデックスを貼る
alter table user add unique(name);
# 結果：成功
Query OK, 0 rows affected (0.02 sec)
# テーブル削除
drop table user;途中にスペースがある場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, 'testuser'),(1, 'test user');
# ユニークインデックスを貼る
alter table user add unique(id,name);
# 結果：成功
Query OK, 0 rows affected (0.02 sec)
# テーブル削除
drop table user;先頭にスペースがある場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, 'test_user'),(1, ' test_user');
# ユニークインデックスを貼る
alter table user add unique(id,name);
# 結果：成功
Query OK, 0 rows affected (0.01 sec)
# テーブル削除
drop table user;MySQL5.7系
まずバージョン確認
select version();
+-----------+
| version() |
+-----------+
| 5.7.44 |
+-----------+
複数レコードに同じ値があるカラム場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, 'test_user'),(2, 'test_user');
# ユニークインデックスを貼る
alter table user add unique(name);
# 結果：エラー
ERROR 1062 (23000): Duplicate entry 'test_user' for key 'name'
# テーブル削除
drop table user;複数レコードにNULLがあるカラム場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, null),(2, null);
# ユニークインデックスを貼る
alter table user add unique(name);
# 結果：成功
Query OK, 0 rows affected (0.02 sec)
# テーブル削除
drop table user;末尾にスペースがある場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, 'test_user'),(2, 'test_user ');
# ユニークインデックスを貼る
alter table user add unique(name);
# 結果：エラー
ERROR 1062 (23000): Duplicate entry 'test_user' for key 'name'
# テーブル削除
drop table user;途中にスペースがある場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, 'testuser'),(1, 'test user');
# ユニークインデックスを貼る
alter table user add unique(id,name);
# 結果：成功
Query OK, 0 rows affected (0.02 sec)
# テーブル削除
drop table user;先頭にスペースがある場合
# テーブル作成
create table user (id int, name varchar(10));
# データ登録
insert into user values (1, 'test_user'),(1, ' test_user');
# ユニークインデックスを貼る
alter table user add unique(id,name);
# 結果：成功
Query OK, 0 rows affected (0.01 sec)
# テーブル削除
drop table user;まとめ
概ね想定通りでしたが、「MySQL5.7系の場合、末尾の半角スペースのみの違いしかない値が複数レコードあるカラムにはユニークインデックスは貼れない」のは意外でした！！
Duplicateエラーの発生条件まとめ
MySQL8系とMySQL5.7系ともに複数レコードに同じ値があるカラムにはユニークインデックスは貼れない
MySQL8系とMySQL5.7系ともに複数レコードにNULLがあるカラムにはユニークインデックスは貼れる
MySQL5.7系の場合、末尾の半角スペースのみの違いしかない値が複数レコードあるカラムにはユニークインデックスは貼れない(MySQL8系では貼れる)
