Oracleの教科書でSQLを学んだ方なら、文字列を扱うならvarchar型を使うのはないでしょうか。しかし、MySQLでは、文字数を気にする必要のないtext型が利用できます。とはいえ、MySQLのtextには文字数の制限があり、長い文字列を扱う場合は注意が必要です。今回は、このようなMySQLのtext型の特徴について解説します。
文字列を格納する基本
データベースを利用する情報システムはたくさんありますが、そういったデータベースでは必ずのように文字列を格納しています。そして、文字列を扱うデータベースを作るには、MySQLならvarchar型とtext型が利用できます。まずは、この2つの文字列を扱うデータ型について解説します。
文字列を扱うならvarcharが基本
国際標準のSQLの規格では、文字列を扱うデータ型として、固定長のchar型、または、可変長varchar型が定義されています。そのため、Oracleの教科書でSQLを学んだ方なら、一般的な文字列を扱う場合、可変長のvarchar型を使うのが一般的です。そのため、MySQLで使えるtext型は標準ではありません。
なお、char型は、charの後に数字を括弧で囲んで記述することで、その数字の文字列を扱う格納領域を生成します。例えば、char(10)は、アルファベットなどの半角文字が10文字の格納領域という意味で、10文字の文字列しか扱えません。
一方varchar型は、同じように数字を括弧で囲んで記述することで、最大でその数字の文字列を扱う格納領域を生成します。例えば、varchar(10)なら、10文字以下なら何文字の文字列でも扱えます。そして、一般的なシステムが扱う文字列は、文字数を固定できないことが多いことから、通常はchar型ではなくvarchar型が使われます。
text型が使えるデータベース
先ほど紹介した文字列を扱うvarchar型は、OracleなどのSQLが使えるデータベース管理ソフトなら必ず利用できます。ただし、varchar型は、扱える最大の文字数を指定しなければならず、場合によっては領域が足りなくなることもあるでしょう。
しかし、MySQLとPostgreSQLなど一部のデータベース管理ソフトでは、文字数の制限の無いtext型が利用できます。特に、PostgreSQLでは、varchar型よりもtext型の利用が推奨されており、PosygreSQLに慣れた方はMySQLでもtext型をよく利用します。
MySQLのtext型には制限がある
先ほど紹介した便利なtext型ですが、MySQLで利用する場合は注意が必要です。それは、PostgreSQLのtext型の最大1Gバイトまで扱えるのに対し、MySQLでは64kバイトしか扱えません。そのため、MySQLで文章などの長い文字列を格納する場合、text型が扱えるサイズを超えてしまい、システムが誤動作することもあります。
なお、MySQLでは、text型と同じように扱えて、テキスト型よりも大きな文字列を格納できるMEDIUMTEXT、さらに大きなLONGTEXTが利用できます。もし、極端に長い文字列を扱う場合には、LONGTEXT型を利用しましょう。
text型を含むテーブルの作り方
MySQLで文字列を扱うためには、以前のバージョンではvarcharを使うのが常識でした。しかし、MySQLの最新版、およびMySQLと互換性のあるmariadbの最新版ではtextも使えます。次から、text型のカラムの作り方について解説します。
textの文法
text型のカラムを作るSQLは、varcharを作るSQLと基本的に同じです。そして構文は次のとおりです。
TEXT [(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
なお、charset_nameやcollation_nameは、データベースのデフォルトの文字コードを使う場合は省略できます。また、TEXT(32)のように括弧で数字を指定とすると、varcharと同じように指定した文字数の文字列を扱うカラムを作成します。
文字列を扱うテーブルを作る例
create table txlist ( id int primary key, st text )
大きなサイズの文字列を扱うには
MySQLのtext型には最大長が設定されており、半角で65,535文字を超える文字列は扱えません。個人のブログのような短い文章なら問題ありませんが、全角で3万文字を超える長い文章を格納しようとすると、最大長をオーバーしてしまいます。
これは、text型は文字の長さを16ビットの数字で管理しているためで、2の16乗、すなわち65,535文字までしか扱えません。
なお、MySQLでは、text型よりも大きな文字列が扱える、mediumtext型やlongtext型も使えます。text型が扱える最大長は16bitですが、mediumtext型は24ビット、longtext型は32ビットで文字数を管理します。そのためlongtextを使えば最大で4Gバイトの文字列を扱えるので、長い文章を格納する場合は、これを利用してください。
- text型が扱える文字数は、2の16乗の65,535文字（約64Kバイト）
- mediumtext型が扱える文字数は、2の24乗の16,777,215文字（約16Mバイト）
- longtext型が扱える文字数は、2の32乗の4,294,967,295文字(約4Gバイト）
最新のmariadbではデフォルトを指定できる
MySQLのバージョン5では、文字列を格納するテーブルを作成しても、レコードを新規に作成した際にデフォルトで入る文字を指定できませんでした。しかし、MySQLと互換性のあるmariadbの10.2以降のバージョンでは、デフォルトを指定できます。
UbuntuやCentOSなどのLinuxの最新版には、標準でmariadbが採用されています。もし、Linuxの最新版でMySQLを使う場合、そのMySQLはmariadbかもしれません。そういった環境でMySQLを活用する場合は、文字列のデフォルトの機能を活用も検討してください。
text型にデフォルトを指定したテーブルの例
create table txlist2 ( id int primary key, st text default 'No Data' );
textとvarcharの違い
MySQLで文字列を扱う場合、text型かvarchar型の2種類のデータ型から選べます。しかし、この2つは同じものではありません。機能に違いがあるので、それを理解して使用しましょう。次から、textとvarcharの違いについて解説します。
最大の違いはデータ格納方法
MySQLにおけるtext型とvarchar型は、SQLで処理する側からすれば違いはありません。括弧で最大文字数を指定するかしないかの違いだけです。しかし、データベースエンジンから見れば、この2つは全く別ものです。
まず、varchar型の文字列はデータベースに直接保存されます。しかし、text型の文字列は、データベースとは別に保存され、データベースにはそのポインターのみ保存されます。そのため、短い文字列であればvarcharを使った方が、効率良く処理できます。また、longtextなども外部ファイルを使うので、1Gバイトを超えるかなり大きな文字列を扱えます。
indexを使う場合はvarcharを
大規模なデータベースを検索する場合、インデックスを作っておくと処理時間を短縮できます。しかし、文字列を含むインデックスを作る場合、varchar型は完全なインデックスが作れますが、text型を使っていると完全なインデックスが作れません。そのため、文字列を含むテーブルでインデックスを活用する場合は、varchar型を利用してください。
文字列を扱うなら改行の扱いに注意を
MySQLに限らず、改行を含む文字列をデータベースでを扱う際に注意が必要です。それは、改行と認識される文字コードは、システムによって違っているためです。そのため、改行を含む文字列を登録した側では正しく見えていても、別の機種で見ると改行されていない、といったこともあります。
なお、OS毎の改行コードは、下記のとおりです。
Windowsの改行コード CR+LF (\r\n)
macOSの改行コード CR(\r)
Linuxの改行コード LF(\n)
Webブラウザのtextareaタグにコピペされた文字列をそのままデータベースに登録すると、検索に失敗したり、正しく表示されないこともあるので注意してください。
また、HTMLでは改行タグが使われるので、文字列に改行コードが含まれていても、それでは改行されません。そのため、データベースに格納された文字列をそのままHTMLで表示してしまうと、改行されないのでHTMLの改行タグに変換するなどの対策が必要です。
まとめ
WebシステムなどでMySQLに格納した文字列を使うケースはたくさんあります。しかし、MySQLで文字列を登録する際に用いるvarchar型やtext型の仕様を理解しないで使用すると、途中で文字が消えたり、システムが誤動作することもあります。ぜひ、正しく理解して使用しましょう。
