MySQLのInnoDBでUniqueキーは最大767バイト

MySQLで Uniqueキーは最大 767バイト

Uniqueキーを設定する最大サイズを知った経緯

　
「Insert On Duplicate Key Update構文の使い方」で、Uniqueキーの設定をする必要があったため、いろいろ実験をしましたが、その際、MySQLの Uniqueキーには最大サイズが存在することを知りました。
　
通常は、あまり大きなカラムを Uniqueキーに設定したりしないものですし、ましてや varcharの項目などを Uniqueキーにしようとは思わないものですが、他のシステムから送られてくるデータに IDがなく、他に Uniqueキーに指定する項目がなかったために、仕方がなく URLが編集される varcharの項目を含めた複数の項目をまとめて Uniqueキーに設定しようとしたために、今回の最大サイズを知る状況に遭遇したのでした。
　
そして、この記事では、その後いろいろと実験をしてみて、少し状況を改善させる方法などもありましたので、経緯などを踏まえながら対処方法をお伝えします。
　
　

Uniqueキーを設定できる最大サイズ

　
MySQLには Uniqueキーを設定する際の最大サイズが存在します。
そして、その最大サイズは、ストレートエンジンによってサイズが違います。
　
　最大サイズ　　1,000 Bytes
　　※MyISAMの場合
　
　最大サイズ　　767 Bytes
　　※InnoDBの場合
　
　
ストレートエンジンについては、MySQLのオフィシャルサイトを参照ください。
　https://dev.mysql.com/doc/refman/5.1/ja/storage-engine-overview.html
　
　

Uniqueキーを設定できる最大サイズについての実験考察

　
本来は、しっかりドキュメントを読めばいいのですが、いろいろと実験をしてみて結果を確認しました。
　
まず確認できたことは、上記の項目で書いたように、ストレートエンジンの MyISAMと InnoDBによって Uniqueキーとして設定できる最大サイズ（バイト数）が違うということでした。
　
そのため、ストレートエンジンが MyISAM、InnoDBのどちらでもいい場合は、ストレートエンジンを変更するだけでバイト数を増やすことができます。
　
　
また、「varchar(n)」で設定する「n」は「バイト数」ではなく「文字数」ということにも注意が必要です。
　
具体的には、全角文字 1文字は、Shift-JIS、EUCは 2バイトで、UTF-8では 3バイトとなっています。
PHPとの相性を考え、MySQLの文字コードはデフォルトで「utf8_general_ci」を設定している場合がほとんどだと思いますが、そうすると 1文字 3バイトで計算されていることになるのです。
　
そのため、例えば「varchar(10)」と設定した場合は、Shift-JISでは 20バイトで、UTF-8では 30バイトが必要になってきます。
　
　
MySQLにおいての文字コードの設定についてですが、Shift-JISの場合は、「cp932_japanese_ci」を選択します。
「cp932」は「Windows Code Page 932」の略です。
また、「sjis_japanese_ci」ではなく「cp932_japanese_ci」を選択する理由は、「http://www.scs.co.jp/mysql/docs/Interop2006MySQL_JP_handling.pdf」に書いてあったようですが、現在はページがなくなっています．．．
　
　
また、MySQLは、カラムごとに文字コードを設定することができます。
　
　
ここまで説明するとピンと来たと思いますが、Uniqueキーに設定する項目のバイト数を小さく、もしくは、文字数をより多く保存できるようにするには、そのカラムだけデフォルトの「utf8_general_ci」から「cp932_japanese_ci」に設定を変更するだけで Uniqueキーのバイト数制限を増やすことができる場合もあるでしょう。
　
　
さらに、今回私が処理をしようとした項目は、URLを編集する項目でしたので、全角文字文字は不要で ASCIIコード（半角英数字）が保存できれば足りましたので「ascii_general_ci」を設定するという方法も採ることができました。
　
　
そうすることで、それぞれ下記の設定まで Uniqueキーを設定することができました。
　
※MyISAMの場合
　　utf8_general_ci　　　varchar(333)
　　cp932_japanese_ci　　varchar(500)
　　ascii_general_ci　　varchar(1000)
　
※InnoDBの場合
　　utf8_general_ci　　　varchar(255)
　　cp932_japanese_ci　　varchar(383)
　　ascii_general_ci　　varchar(767)
　
　
私の場合は、これらを考慮し、テーブルの項目を「varchar(256)」とし、文字コードを「ascii_general_ci」に設定しました。（実際の処理としては、URLが編集してある varcharの項目の他にも複数の項目をまとめて Unique設定を行うため、「varchar(256)」で設定しています。）
　
そして、その項目に値を編集する際には、万一日本語 URLが入ってきた場合を想定し、全角文字があった場合はエンコードを行って保存することにしました。
　
　

参考文献のおまけ

　
今回の処理についてはいろいろ調べたこともありますが、MySQLの基本的な理解に含まれますので、あちこちに関連しますので、関連のサイトなどを紹介しておきます。
　
日本語のバイト数に関しては「日本語は2バイト文字？3バイト文字？」という記事も書いていますが、いまいち実験をして検証したわけではない部分もありますので、参考程度に読んでください。
　
ON DUPLICATE KEY構文の実験をした続きとして、CakePHPでそれを実装したかったわけですが、それについては「CakePHP 2.3 で On Duplicate Key構文を実装」に書いていますので、CakePHPで実装を検討している方は参照してください。
　
この記事を書くにあたって、MySQLのいろいろな限界値について言及されている方がいらっしゃいましたのでご紹介しておきます。
　http://nippondanji.blogspot.jp/2009/05/mysql.html