lxyuma BLOG

開発関係のメモ

varcharとtextの違い(mysql innodb)

mysqlの可変長文字列を扱う、varchar型とtext型の違いの話。

古い情報が混在していたので、ちょっと整理してメモ。

myisamの頃の話

  • sizeが違う
  • 行の中身がdataか(varchar)、dataへのポインタか(text)
    • 参照挟むので、performanceの違いがあった(varcharが早い)

今 net でぐぐって、ひっかかる情報の大半がこの話。

最近のinnodbの話

  • 最大sizeは一緒。64kb(但し、TINYTEXT型、MEDIUMTEXT型、LONGTEXT型は名前の通り違う)
  • varcharもtextも、中身は同じ仕組み(BLOB field / off page column)
    • 行にdata入れるのも、外部(overflow page)への参照にするのも、行フォーマット次第(row format)
    • 5.6で行formatのdefault は COMPACT
    • COMPACT : カラムの値が小さいとB treeにそのままデータ格納、768byte以上でoverflow page作る。5.0以降のdefaultで互換性維持の為、今もdefault
  • 仕組み一緒なので、performanceも変わらないらしい

違い

type 初期値 最大文字長 最大sizeの数の意味
varchar 指定可 指定可 文字数で指定(日本語でも、最大65535文字まで入る)
text 指定不可 指定不可 byteで指定(1文字3byteのutf8で日本語入れたら65535 の 1/3文字 入る)

※他、sql標準規格に含まれるか?(=varchar)含まれず独自拡張か?(=text)という違いもある

結論

  • ほぼ、varchar一択で良いのでは。
  • text型のメリットは、varchar以上のsizeを入れる型もある事(MEDIUMTEXT/LONGTEXT)と、型で最大文字長が明示できる点?(TINY/MEDIUM...etc)。普通に使う分には要らない。
  • ちなみに、どちらにせよ、innodbは8kbの壁と言われる行サイズの制限にひっかかる可能性がある。その時は、まずは行formatをcompressかdynamicに

参考