數據庫用varchar和text的差別

發布時間：2018-05-09 20:41,

瀏覽次數：1268

, 標簽：

varchar

text

最近有幾個同學問我varchar和text有啥別嗎，這個問題，以前說真的也沒太多的整理，以前遇到text在設計中就是盡可能的拆到另一個表中，保持主表盡量的瘦小，可以讓innodb

bp緩存更多的數據。

今天借次機會系統整理一下，主要從存儲上，最大值，默認值幾個方面進行比較。

BTW： 從ISO SQL:2003上講VARCHAR是一個標準型，但TEXT不是(包括tinytext).varchar在MySQL

5.0.3之前只支持0-255byte, 在5.0.3之后才支持到0-65535byte.

從存儲上講：

- text 是要要進overflow存儲。 也是對于text字段，不會和行數據存在一起。但原則上不會全部overflow ,

會有768字節和原始的行存儲在一塊，多于768的行會存在和行相同的Page或是其它Page上。 - varchar

在MySQL內部屬于從blob發展出來的一個結構，在早期版本中innobase中，也是768字節以后進行overfolw存儲。 -

對于Innodb-plugin后： 對于變長字段處理都是20Byte后進行overflow存儲 (在新的row_format下：dynimic

compress)

說完存儲后，說一下使用這些大的變長字段的缺點：

- 在Innobase中,變長字段，是盡可能的存儲到一個Page里，這樣，如果使用到這些大的變長字段，會造成一個Page里能容納的行

數很少，在查詢時，雖然沒查詢這些大的字段，但也會加載到innodb buffer pool中，等于浪費的內存。 (buffer pool

的緩存是按page為單位)(不在一個page了會增加隨機的IO) -

在innodb-plugin中為了減少這種大的變長字段對內存的浪費，引入了大于20個字節的，都進行overflow存儲，

而且希望不要存到相同的page中，為了增加一個page里能存儲更多的行，提高buffer pool的利用率。 這也要求我們，

如果不是特別需要就不要讀取那些變長的字段。

那問題來了？ 為什么varchar(255+)存儲上和text很相似了，但為什么還要有varchar, mediumtext, text這些類型？

(從存儲上來講大于255的varchar可以說是轉換成了text.這也是為什么varchar大于65535了會轉成mediumtext)

我理解：這塊是一方面的兼容，另一方面在非空的默認值上varchar和text有區別。從整體上看功能上還是差別的。

這里還涉及到字段額外開銷的：

- varchar 小于255byte 1byte overhead - varchar 大于255byte 2byte overhead -

tinytext 0-255 1 byte overhead - text 0-65535 byte 2 byte overhead - mediumtext

0-16M 3 byte overhead - longtext 0-4Gb 4byte overhead

備注?overhead是指需要幾個字節用于記錄該字段的實際長度。

從處理形態上來講varchar 大于768字節后，實質上存儲和text差別不是太大了。 基本認為是一樣的。

另外從8000byte這個點說明一下： 對于varcahr, text如果行不超過8000byte(大約的數，innodb data page的一半)

,overflow不會存到別的page中。基于上面的特性可以總結為text只是一個MySQL擴展出來的特殊語法有兼容的感覺。

默認值問題：

- 對于text字段，MySQL不允許有默認值。 - varchar允許有默認值

總結：

根據存儲的實現： 可以考慮用varchar替代tinytext 如果需要非空的默認值，就必須使用varchar

如果存儲的數據大于64K，就必須使用到mediumtext , longtext varchar(255+)和text在存儲機制是一樣的

需要特別注意varchar(255)不只是255byte ,實質上有可能占用的更多。

特別注意，varchar大字段一樣的會降低性能，所以在設計中還是一個原則大字段要拆出去，主表還是要盡量的瘦小

源碼中類型：

+--Field_str (abstract) | +--Field_longstr | | +--Field_string | |

+--Field_varstring | | +--Field_blob | | +--Field_geom | | | +--Field_null |

+--Field_enum | +--Field_set

(末完待續，也希望大家一塊討論一下)

參考：

http://yoshinorimatsunobu.blogspot.com/2010/11/handling-long-textsblobs-in-innodb-1-to.html

http://nicj.net/mysql-text-vs-varchar-performance/

http://www.pythian.com/blog/text-vs-varchar/

測試SQL及方法

create table tb_01( c1 varchar(255), c2 varchar(255), c3 varchar(255), c4

varchar(255), c5 varchar(255), c6 varchar(255), c7 varchar(255), c8

varchar(255), c9 varchar(255), c10 varchar(255), c11 varchar(255)

)engine=Innodb; insert into tb_01(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11)

values(repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255));

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to

TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In

current row format, BLOB prefix of 768 bytes is stored inline.

(testing)root@localhost [wubx]> set global innodb_file_format=BARRACUDA;

Query OK, 0 rows affected (0.00 sec) (testing)root@localhost [wubx]> alter

table tb_01 row_format=dynamic; Query OK, 0 rows affected (0.19 sec) Records: 0

Duplicates: 0 Warnings: 0 (testing)root@localhost [wubx]> insert into

tb_01(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11)

values(repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255));

Query OK, 1 row affected (0.00 sec) set global innodb_file_format=Antelope;

create table tb_02( c1 varchar(2000), c2 varchar(2000), c3 varchar(2000), c4

varchar(2000), c5 varchar(2000), c6 varchar(2000), c7 varchar(2000), c8

varchar(2000) )engine=Innodb; insert into tb_02(c1, c2, c3,c4,c5,c6,c7,c8)

values(repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000),repeat('吳',2000)

); create table tb_03( c1 text, c2 text, c3 text, c4 text, c5 text, c6 text, c7

text, c8 text, c9 text, c10 text, c11 text )engine=Innodb; insert into

tb_03(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11)

values(repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255));

(testing)root@localhost [wubx]> insert into

tb_03(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11)

values(repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255),repeat('吳',255));

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to

TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In

current row format, BLOB prefix of 768 bytes is stored inline. set global

innodb_file_format=BARRACUDA; alter table tb_03 row_format=dynamic;

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的mediumtext和string转换_数据库用varchar和text的差别的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。