ちょっと間が空いてしまいました。タイトルのテーマでサクッと書こうと思って調べ始めると、迷路に入ってしまいました。真面目に、全ての情報を書くのは多分、この話題はかなり辛いでしょう。特にMySQLの問題がありますから。ですので、いい感じのストーリにするのにちょっと時間がかかりました。マニュアルやブログを大量に読みましたが、なるべくシンプルにまとめたいと思っています。
文字列をフィールドに保存するとき、VARCHARかTEXTのいずれかの型を使うことになるでしょう。VARCHARはCHARACTER VARYINGの短縮形なのですが、VARCHARの方がお馴染みなので、こちらの表記にします。標準SQLではTEXTは定義されていないのですが、MySQL、PostgreSQLではこの2つの型のどちらかを使います。CHARという型もあるのですが、文字数を指定して定義し、足りない場合にわざわざ空白で埋めるようなことをするので結果的に固定長の文字列になりますが、現状のアプリケーションではそんなことは不要なことが多く、可変長の文字列型しかほぼ使わないでしょう。
そこで、２種類の型があるのですが、大きな違いはVARCHARは、VARCHAR(10)として、10文字（バイトではない）までの文字列を保存できるようなフィールドを用意するのに対して、TEXTは長さは指定せず、データベースの動作上の制限の長さまで入力できます。ここでいきなり例外が出ますが、PostgreSQLについては、VARCHARだけを書いて( )なしにすることで、長さの制限を無しにすることができるのですが、今回の記事では、VARCHAR(n)かTEXTかという表現で通します。
まず、２種類の型の大きな違いは、文字数の制限ができるかどうかです。文字数を制限したいかどうか？ 実は制限しなくてもいいような場面が多いと思われます。逆に制限してしまった方が、意図せず制限より長い文字列を保存できなくなってしまうという問題に遭遇します。しかしながら、都道府県を保存するのにTEXTを使うのかという、かなりどうでもいいような問題に頭を悩ませてしまいます。つまり、せっかく制限が可能なのに利用しないのかというジレンマにつきまとわれてしまいますが、論理的に考えれば制限が必要になることはあまりないと思われます。
ただ、処理速度が違うだろうということは気になります。先に結論を言えば、ほぼ同じだと言えるくらいでしょう。PostgreSQLはマニュアルに、この2つの型は目立った違いがないと書いてあります。MySQLについては、１レコードのブロック内に、実際のデータを並べるのが基本動作ですが、TEXTについては原則としてそのブロック外にデータを保存するので、要するにVARCHARよりもTEXTの方が処理に時間がかかる傾向があるはずです。しかしながら、実際の運用等でもそんなに大きな差があるようには思えませんし、ベンチマークを公開しているブログ記事などを見る限りは僅かな差です。長大なデータ処理をするような場合には違いが出るかもしれませんが、一般的なUIを絡むようなアプリケーションでは多分違いはほぼ顕在化しません。ネットワーク等の他の要因の方がよほど大きく効いてくると考えられます。パフォーマンスに違いがないのなら、制約の少ないTEXTの方がいいじゃないかと思うところです。
しかしながら、MySQLに微妙な違いがあって、VARCHARはDEFAULTによる既定値の指定が可能なのに対して、TEXTはそれができません。PostgreSQLはTEXTでもDEFAULTの指定は可能です。そして、なんと、MySQL互換のMariaDBも、TEXT型でのDEFAULT指定はできます。互換を謳いながらもより高機能になっています。
この既定値については確かにデータベース定義上で行っておけば、プログラムコードは一切なく、フィールドの初期値が決められて便利です。しかしながら、都度、初期値が違うようなことも多く、結果的にプログラムで初期値を決める必要がある場合もそこそこあると思われます。長さの制限ができるという意味では、確かに不要な長いデータを保存してしまう危険性は排除できますが、本当に長さの制限をしたい場合は、おそらくUIで確認をして、長さが超えていればそもそもデータベースに命令を送ることはしないようなシステムの動作をすることができるわけです。ということで、初期値も、長さ制限も、システム内部でのケアがどっちにしても必要だとすれば、２つの型をチョイスする積極的な理由にはなりにくいでしょう。
ちなみに、SQLiteではTEXTのみです。FileMakerでも「文字型」としてフィールド定義できますが、長さの指定はできません。そんなわけで、PostgreSQLだとTEXTでいいだろうということになりそうです。MySQLではDEFAULTを設定したいかどうかによって変わるけど、長さ制限する意図がない場合にはTEXTで良いという結論になりそうです。
ただし、TEXTにも制限があるということを理解しておく必要はあります。PostgreSQLは1GBまでなので、ある意味、滅多にそこまでは行かないでしょうけど、MySQLは64KBの制限があります。しかしながら、これはバイト数なので、4バイト表現のUTF-8が基本となっているMySQLだと、最大で15,000文字くらいになり、書籍1冊分のテキストは入らないかもしれないぞというサイズです。MySQLは最大長がもっと多い、MEDIUMTEXTやLONGTEXTという型があるので、場合によってはそちらを使うことを検討する必要があります。
MySQLについては、文字列の全てがインデックスに利用されるわけではなく、Ver.５.7以降では3072バイトまでになります。また、インデックスを作成するときに、最初から何文字まで取り出すのかということを、VARCHARでもTEXTでも指定は可能です。さらに、LIKE演算子での検索では、LIKE ‘A%’のような前方一致の検索の場合しかインデックスは使用しません。長い文字列を扱う場合はインデックス対象になる範囲があることや、LIKE ‘%A%’のような〜を含むの検索をしたときにインデックスが使われないということにも注意が必要です。長い文字列に対しての検索の効率を上げるには、FULLTEXTインデックスという機能を使う必要があります。クエリーのSQLも若干違うものを使う必要がありますが、データが大きくて検索が遅い場合には検討する必要が出てくるでしょう。
MySQLのことを調べていたら、懐かしい「8KBの壁」の話が出てきました。MySQLは、8KBの領域を使って1レコードを管理する方法が取られていますが、Ver.5.6までの標準のセットアップでは、フィールドを増やせば、「思ったよりも早く」この8KBを埋め尽くしていたのでした。その時は、1つTEXTのフィールドは8KBのブロックのうち768バイトを使っていたので、10個ほどフィールドを作ればアウトだったのです。Ver.5.7からはTEXTはブロック内の20バイト程度しか使わないので、まずまずたくさんのフィールドが定義できたのでした。MySQLにはこうした「意外な頭打ち」がいろんなところにあるので、使いこなしたい方は、まずはマニュアルを熟読することが必要になるでしょう。