🎉

MySQLで文字列のユニーク制約を掛ける時に気をつけること

2022/08/31に公開約2,900字

はじめに

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 属性で定義された文字カラムには、カラム文字セットのバイナリ照合順序が割り当てられます。

https://dev.mysql.com/doc/refman/5.6/ja/charset-charsets.html
https://dev.mysql.com/doc/refman/5.6/ja/charset-binary-op.html
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の稀に良く書く日記

https://dev.mysql.com/doc/refman/8.0/ja/innodb-limits.html

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では一緒に働くエンジニアを募集しています!
自動化大好き!インフラ効率化したい!技術大好き!
なエンジニアの方一緒に働きましょう!

https://corp.sweeep.ai/recruit

参考

MySQLの複合UNIQUEインデックスと文字列型のはなし - 十番目のムーサ
MySQLのchar,varcharは大文字小文字を区別しない - Qiita

GitHubで編集を提案

Discussion

ログインするとコメントできます