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に
