MySQL

 

MySQLでユニークキーとフィールド長制限を拡張する

ユニークキーのフィールド長制限についての注意。MySQLのユニークキーは以外とフィールド長の制限が厳しく、要件が満たせない場合が多いので、対応策を検討します。暗号化した個人情報等を保存したい場合等に効果を発揮します。

ブログも書いてます！　文系プログラマによるTIPSブログ：最新記事

treeからのお知らせ

新サイト、tree-mapsを公開しました！！

tree-maps: 地図のWEB TOOLの事ならtree-mapsにお任せ！

地図に関するWEB TOOL専門サイトです！！

大画面で大量の緯度経度を一気にプロット、ジオコーディング、DMS<->DEGの相互変換等ができます！

◯ 広告

ユニークキーの制限は？

ユニークキーには最大フィールド長が決まっています。

それぞれの文字コードでの最大フィールド長は以下の通りです。

ascii UTF8 UTF8MB4
767 ÷ 1byte = 767byte 767 ÷ 3byte = 255byte 767 ÷ 4byte = 191byte

よく以下のようなエラーを目にする事があるかと思います。

mysql> create table s0(c1 varchar(256) unique) charset=utf8mb4;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

よくある問題となるケースは？

メールアドレスを暗号化してユニークキーを貼る、という要件です

他にも、単純にUTF8MB4の場合は最大191文字までしか入力できなくなるので、256文字入れたい！というのは標準ではできません。

解決策は？

対策1：テーブルの文字コードをシングルバイトにする。 
mysql> create table s1(c1 varchar(767) unique) charset=ascii;
Query OK, 0 rows affected (0.02 sec)
おすすめしません。ER図上ではこのテーブルだけ文字コードが違う事に気付けないし、処理的に面倒になる可能性が高いです。
対策2：カラムの文字コードをシングルバイトにする。 
mysql> create table s2(c1 varchar(767) charset ascii unique) charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
おすすめしません。ER図上ではこのカラムだけ文字コードが違う事に気付けないし、ER図のメンテが難しくなります。
対策3：カラムを分割してそれら全てにユニークキーを貼る。 
mysql> create table s3(c1 varchar(191),c2 varchar(191),unique key (c1, c2)) charset=utf8mb4;
Query OK, 0 rows affected (0.02 sec)

おすすめです！ER図上で非常に解りやすく、ユニークキーの意図が伝わり易く、ER図のメンテも容易です。

例えばメールアドレスの aaa@bbb で、aaaとbbbをカラム分割してユニークキーを貼ればいいかと思います。
treeおすすめの書籍です！
【送料無料】実践ハイパフォ-マンスMySQL第2版

【送料無料】実践ハイパフォ-マンスMySQL第2版
価格：4,830円（税込、送料別）
【送料無料】エキスパ-トのためのMySQL運用＋管理トラブルシュ-ティングガイド

【送料無料】エキスパ-トのためのMySQL運用＋管理トラブルシュ-ティングガイド
価格：3,465円（税込、送料別）
【送料無料】基礎からのMySQL改訂版

【送料無料】基礎からのMySQL改訂版
価格：3,129円（税込、送料別）
◯ 広告
tree-tipsへのお問い合わせはこちら