エス技研

WordPress、CakePHP、PHP、baserCMSなどの Web系システムを中心に情報を提供します!


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

      2017/04/29

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

 - MySQL

GoogleAdwords

GoogleAdwords

最後までお読みいただきましてありがとうございます。
この記事が参考になったと思いましたらソーシャルメディアで共有していただけると嬉しいです!

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

下記の空欄を埋めてください。 * Time limit is exhausted. Please reload CAPTCHA.

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)

※入力いただいたコメントは管理者の承認後に掲載されます。

  関連記事

別サーバの MySQLにアクセスする設定・ファイアウォール設定、ポート設定、MySQL設定、テーブルへ権限付与の方法

外部サーバからアクセス方法解説。GRANTで権限付与。my.cnfのbind-addressで制限解除。3306ポートの開放、ファイアウォールの制限解除。確認方法と設定方法を詳細解説。

MySQL、CakePHP 2.3で「tinyint(1)」の Boolean型の動作を再確認

MySQL+CakePHPの環境で「tinyint(1)」を利用する際の動作を検証。「tinyint(1)」の Boolean型について CakePHPでは自動処理が実施されていることを確認しました。

MySQLの「tinyint(1)」の悲劇 Boolean型になるとは...

MySQLで tinyint(1)を指定すると Boolean型を指定したことになります。意外な誤解があります。

Insert On Duplicate Key Update構文の使い方

レコードの有無で Insert、Updateを切り替える On Duplicate Key構文の使い方の説明です。

MySQLの設定ファイル my.cnfがある場所と読み込み順序

MySQLの設定はmy.cnfファイルに記述されています。my.cnfを設置する場所は複数あり予め決められた順番で読み込み、その順番通りに設定内容が反映されます。

MySQLのタイムゾーン(mysql_tzinfo_to_sql)の設定方法・XAMPP環境の解説も
MySQLのタイムゾーン(mysql_tzinfo_to_sql)の設定方法・XAMPP環境の解説も

MySQLのタイムゾーンテーブル(mysql.time_zone)の中身を確認し、からの場合は追加する方法を解説。LinuxとWindows・XAMPPとで異なる対応方法についてそれぞれ解説。