MySQL 5.1 の varchar カラムの怪しい動き-その2
2009-12-2追記：
またしても神コメントを頂く。本記事末尾に、追記。
※ テストに使用した MySQL5.1 は最新ではないので、既に修正済みかもしれない。
※ Server version: 5.1.31 を使用してテストした。
MySQL5.1 の varchar カラムの怪しい動き - kameidの備忘録 - Sharpen the Saw!
の記事に対してコメントを頂いた。
MySQL のリファレンスである
MySQL :: MySQL 8.0 Reference Manual :: C.10.4 Limits on Table Column Count and Row Size
へのポインタだ。
ちょっと上記記事の指摘がわかりにくかったかもしれないというのと、追加で判ったことがあるので、補記する。
なぜか、特定のサイズでだけ、「Row size too large.」というエラーが出て、テーブルを作れないよ、というのが本記事での指摘点だった。UTF-8 であることが関係しているのではないか？という想像で当時はとどまっていた。結局、UTF-8 は影響*1はあるものの本質的な原因ではない。
まず、（MyISAM だけでなく、全てのストレージエンジンで）MySQL では、1行に 65535 byte がサイズ上限となる。(ただし、blob と text は除く)。CHARSET が UTF-8 の場合は、3倍扱いする*2。計算式も上記リファレンスに記載されている。んで、上記リファレンスにもあるとおり、それを超えると、「ERROR 1118 (42000): Row size too large.」エラーが出る。が、ここの動きがちょっと不思議、という話である。さらにカラム長を 1 ずつ増やしていくと、何故か varchar で作ったカラムが、mediumtext に変換して生成されるようになり、エラーが出なくなるのだ。
例えば、MyISAM で、CHARSET はわかりやすいように ascii を指定、varchar 一個だけのテーブルを作成した場合、長さ指定は、VARCHAR(65532) までは OK。行の長さの計算式は以下、
1 + 2(長さ情報) + 65532 = 65535
で、これを超えると、VARCHAR(65535) までは上記エラーが出るが、 VARCHAR(65536) を指定するとエラーが出なくなり、生成されたテーブルの型を見ると、mediumtext となっていることが確認できる。なんで、65533 から 65535 の間だけエラーにすんの？この谷間要らなくない？ということだ。
これで困る人も居ないような気がするが、この問題のワークアラウンドは、自動 mediumtext 化などに頼らず、インデックスの必要ないところは、自分で mediumtext 等を指定する、ということに尽きると思う。varchar等、インデックスが付けられるタイプの型のサイズは1行の合計で、65535 byte 以内でないといけないという制限があるので、それを超えないように自分で気をつける、ということだ。
以下、検証。
mysql> CREATE TABLE t3 (c1 VARCHAR(65532) NULL) charset=ascii; Query OK, 0 rows affected (0.00 sec) mysql> show create table t3; +-------+----------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------+ | t3 | CREATE TABLE `t3` ( `c1` varchar(65532) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=ascii | +-------+----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -- 指定通り、varchar(65532) で生成される。 mysql> CREATE TABLE t3 (c1 VARCHAR(65533) NULL) charset=ascii; ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs -- エラーになる。 mysql> CREATE TABLE t3 (c1 VARCHAR(65534) NULL) charset=ascii; ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs -- エラーになる。 mysql> CREATE TABLE t3 (c1 VARCHAR(65535) NULL) charset=ascii; ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs -- エラーになる。 mysql> CREATE TABLE t3 (c1 VARCHAR(65536) NULL) charset=ascii; Query OK, 0 rows affected, 1 warning (0.00 sec) -- なぜかまた成功するようになる。 mysql> show create table t3; +-------+-----------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------+ | t3 | CREATE TABLE `t3` ( `c1` mediumtext ) ENGINE=MyISAM DEFAULT CHARSET=ascii | +-------+-----------------------------------------------------------------------------+ 1 row in set -- 成功するようになるのは、自動的に、mediumtext で生成されるようになるため。
自動で mediumtext に変換がかかっていることがわかる。
2009-12-2 追記：
以下のようなコメントを頂く。
バグ報告は以下の2件が関連しているようです。
http://bugs.mysql.com/bug.php?id=7417
http://bugs.mysql.com/bug.php?id=8295
・65535きっちり入らないのはストレージエンジンの内部仕様
・65536以上でTEXTに変換されるのは本体の機能(設定変更可)
http://dev.mysql.com/doc/refman/5.1/en/silent-column-changes.html
ちぐはぐな印象はありますね。
silent-column-changes の仕様とあわせ、よくよく考えると、これは仕方ないのかもしれない・・・。
