MySQLで文字列のユニーク制約を掛ける時に気をつけること
はじめに
MySQLで文字列に制約をかけた際、想定していた挙動と異なる挙動を
発見しました。
調査時に得た知見を含めて、MySQLで文字列ユニーク制約を掛けたときに
注意しないといけない挙動に関して、記載していきます。
MySQL 8.0.27 (MySQL Community Server - GPL) で動作を確認しています。
大文字や小文字が区別されない
MySQLで文字列を扱うときに注意する事かもしれないですが、
char, varchar は大文字と小文字を区別しないです。
CREATE TABLE tbl1 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
unique(name)
) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;
INSERT INTO `tbl1` (`name`) VALUES ('IT太郎'), ('It太郎');
Error Code: 1062. Duplicate entry 'It太郎' for key 'tbl1.name'
解決策としてはカラムにBinary属性をつけて、照合順序を変更します。
MySQLのデフォルト照合順序は binary以外は文字セット名_ci になっています。
ci は大文字小文字を区別しません。
BINARY 属性で定義された文字カラムには、カラム文字セットのバイナリ照合順序が割り当てられます。
CREATE TABLE tbl2 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) BINARY NOT NULL,
unique(name)
) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;
INSERT INTO `tbl2` (`name`) VALUES ('IT太郎'), ('It太郎');
2 row(s) affected Records: 2 Duplicates: 0 Warnings: 0
でエラーになりません。
文字列の後のスペースが無視される
MySQLでは文字列の後ろのスペースは比較対象にはならない仕様です。
基本的にアプリケーションコードで保存時にトリムする事が多いと思いますが、
文字列の後のスペースは無視されるので、ご注意ください。
(generate column で解決できるか確認中)
INSERT INTO `tbl1` (`name`) VALUES ('IT次郎'), ('IT次郎 ');
Error Code: 1062. Duplicate entry 'IT次郎 ' for key 'tbl1.name'
INSERT INTO `tbl2` (`name`) VALUES ('IT次郎'), ('IT次郎 ');
Error Code: 1062. Duplicate entry 'IT次郎 ' for key 'tbl2.name'
3072bytes(utf8mb4で768文字)より長い文字列のカラムはユニーク制約を掛ける事ができない
エラーに書かれているとおり、keyは最大で767byteまでしか使えないらしい。
ちなみにkeyはPRIMARY KEYとUNIQUE KEYがダメ、ただのKEYならOK。
MySQLのUNIQUEなINDEXには長さ767byteまでしか使えない件と対策 - tanamonの稀に良く書く日記
MySQLのPrimary Key, Unique Keyは767byteまでしか使えないようで、
以下のクエリを実行するとエラーになってしまいます。
CREATE TABLE tbl3 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(769) NOT NULL,
unique(name)
) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;
Error Code: 1071. Specified key was too long; max key length is 3072 bytes
CREATE TABLE tbl4 (
id INT PRIMARY KEY AUTO_INCREMENT,
section_id INT NOT NULL,
name VARCHAR(768) NOT NULL,
unique(section_id, name)
) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;
Error Code: 1071. Specified key was too long; max key length is 3072 bytes
解決策としては
- 文字コードを変える
- 制約に使う文字の長さを変える
ということが必要になります
むすびに
使用しているRDB毎に、細かい挙動が異なるので、
少しづつカバーしていく必要があるなと感じました。
メンテナンスビリティ向上と複数データソースの利用を考慮し
基本的にロジックはアプリケーション側に持たせつつも、
ドライバー毎の挙動を考慮してソースコードを書く必要があるなと感じました。
sweeepでは一緒に働くエンジニアを募集しています！
自動化大好き!インフラ効率化したい！技術大好き！
なエンジニアの方一緒に働きましょう！
参考
MySQLの複合UNIQUEインデックスと文字列型のはなし - 十番目のムーサ
MySQLのchar,varcharは大文字小文字を区別しない - Qiita
Discussion