mysql的char,varchar,text,blob的理解

mysql的char,varchar,text,blob是几个有联系但是有有很大区别的字段类型，这算是mysql的基础吧，可是基础没有学好，恶补一下。

先简单的总结一下：

char：定长，最大255个字符

varchar：变长，最大65535个字符（既是单列的限制，又是整行的限制）

text：变长，有字符集的大对象，并根据字符集进行排序和校验，大小写不敏感

blob：变长，无字符集的二进制大对象，大小写敏感

以下只是我个人的理解，才疏学浅，望各路高人指点。

我使用的引擎是myisam，所以以下的探讨是集中在myisam上的。

首先解释char，char是项目中常用的字段类型之一，它代表的含义是采用固定长度存储数据，换句话说，数据初始化的是就为该类型的字段分配固定长度的存储空间，即使没有达到存储空间的长度，实际占用的存储空间也是定义时的长度。举个例子来说，比如某字段 a char(50)，指定的长度是50个字符的存储空间，那么当你存入一个字符：“abc”的时候，实际上字符长度是3个字符，但是占用的硬盘空间还是50个字符。很显然，char的缺点就出来了：浪费存储空间！但是同时char的优点也显示出来了：固定长度，（索引）效率极高，不存在碎片。

这里我们再探讨一下char的存储方式，虽然char会浪费极大的存储空间，但是你想过对于字符串的前后空格char是如何处理的吗？当存储的字符串没有达到char的最大长度时，字符串后面是不会以空格来填充的，而且char会过滤字符串末端的空格然后存储，而在比较字符串的时候又会自动空格填充到字符串的末端。

好了，对char有了了解以后，对varchar的理解也就容易了。

varchar是存储可变长度的字符串，简单的说我们定义表机构的时候指定的字段长度是最大长度，当字符串没有达到最大长度的时候以字符串的实际长度来存储的，不占用多余的存储空间。因此，一般情况下，varchar比char节省存储空间，但是也经常有例外，后面接着探讨这个问题。

一个特殊的情况是创建表的时候采用ROW_FORMAT=FIXED选项（默认的是ROW_FORMAT=DYNAMIC），那么mysql就会为每行数据分配固定长度的存储空间，当然这是特例。不知道你有没有想过：为什么有管理员愿意这样做呢？对varchar分配固定长度的存储空间是有道理的。举一个常见的例子，假设采用DYNAMIC默认选项，那么我们创建一个字段b varchar(100)，现在我们插入一个只有10个字符的数据：abcdefghij，很好，只占用了10个字符的空间，相比char节省了不少存储空间。但是你想到问题了吗？比如某天以后你发现这个字段需要更新一下，更新为20个字符的数据：abcdefghijklmnopqrst，你知道数据库该如何存储吗？原先的存储位置分配的只有10个字符的空间，现在要存储20个字符，小于长度限制（最大长度是100个字符），问题就来了，mysql会如何处理呢？这里接下来可能要探讨mysql存储层面的分页机制或者拆分机制，就不再继续深入了。总之无论mysql采取什么方式，肯定会在磁盘上形成碎片，久而久之形成的磁盘碎片对系统效率是一个致命的打击，所以我们经常看到有管理员要把mysql导出然后导入，就是为了解决这个问题，提高效率。

上面我们提出来了一个问题：varchar比char节省存储空间，但是也经常有例外！对于这个疑问我们如何理解呢？要深入分析这个问题，我们需要再次深入了解varchar的存储机制。常用的中文存储一般采用gbk或者utf-8两种字符集，gbk每个字符占2个字节，utf-8每个字符占3个字节，所以：gbk字符集的最大存储长度是

(65535-1-1)/2= 32766或者(65535-1-2)/2= 32766，这个算法的含义是：65535是varchar的最大长度，第一个-1表示实际存储位置是从第二位开始的，第二个-1或者-2代表的含义是：varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度小于255时用1个字节，长度超过255时需要2个字节），除以2的原因是一个gbk字符集的字符占2个字节长度，所以根据情况可以得出gbk字符集的最大存储长度是32766。

那么UTF-8字符集呢？算法如下：

(65535-1-1)/3= 21844或者(65535-1-2)/3= 21844减1减2的含义同上，除以3的含义是一个utf-8的字符集字符占用实际长度是3个字节。

看完了这段你就能理解char(1)和varchar(1)占用的存储空间了吧？在这个极端情况下，很显然char更节省存储空间，因为char没有管理数据的额外开销。

接下来，我们重点探讨一下varchar的65535存储长度代表的真正含义是什么？因为前面说过一句话：既是单列的限制，又是整行的限制！我们详细来解释一下。对于单列的限制，我们举一个例子：create table tablename(c varchar(N)) charset=gbk;那么N的最大值是（65535-1-2）=32766。同时，如果同一个有其他字段的话，那么所有字段的长度和不能超过65535，举个例子：create table tablename(c1 int(4), c2 char(30), c3 varchar(N) ) charset=utf8，那么N的最大值是（65535-1-2-4-303）/3=21812，也许你很理解int(4)需要4个字符的位置，这里可能需要更深入的了解int的存储机制，我不太了解，书还没有看呢。总之int类型占用4个字符的长度，换句话说create table tablename(c1 int(32), c2 char(30), c3 varchar(N) ) charset=utf8，N的最大值也是（65535-1-2-4-303）/3=21812。我做了一个简单的实验，看截图。

第一张varchar的长度大于21812，于是失败了

大小: 89.39 K 尺寸: 550 x 264 浏览: 17 次 点击打开新窗口浏览全图

第二张varchar长度等于21812，保存成功

大小: 58.9 K 尺寸: 550 x 334 浏览: 20 次 点击打开新窗口浏览全图

转载请注明来源：www.blogguy.cn

废话了这么多，总之一句话：char在浪费存储空间的劣势下，获得了较高的效率，varchar相反。接下来我们也要总结一下什么情况下使用char，什么情况下使用varchar。

原则一：根据字符串长度确定，凡是固定长度的字符串或者类似固定长度的字符串一律用char。比如身份证号码，手机号码，银行卡号，MD5，哈希值等这是字符串是固定长度的，毫无疑问用char，还有一类是基本固定长度但是略有出入的，比如中国人的姓名等，一般长度可能是2~5个汉字，这类信息也非常适合用char来存储，只要分配一些略大于通常长度即可。

原则二：数据是否经常更改导致碎片，可能经常变动而产生存储碎片的小字符串一律用char。我们知道char类型的数据是一次性分配存储空间的，无论以后你怎么修改，数据始终在该存储空间内的，不会产生碎片。而varchar则不同，varchar的数据长度是可变的，当修改后的数据大于当前存储长度时，就会产生碎片，如果该应用是反复修改数据的应用，那么久而久之就是产生无数碎片，效率可想而知。

原则三：理解varchar的存储空间和内存空间的区别，合理指定varchar的长度。我们知道varchar的存储长度是根据字符串的长度而定的，但是运行时占用的内存空间却是按照定义的长度分配内存空间的（我的理解，不知道是否正确）。这个现象导致存储一个字符串，比如通信地址，通畅在100个字符内就能存储完成，于是varchar(100)是一个合理的选择，但是由于之前讲的，可能有人图方便使用varchar(500)，反正用的存储空间是一样的，但是效果确实不一样的。在内存模型中varchar(100)与varchar(500)是两码事，后者比前者占用多5倍的内存空间，在临时表和排序的时候这个差别几乎可能差一个数量级，于是效率可想而知。

转载注明来源：www.blogguy.cn

基本上解释完char和varchar，这里顺带看看Nchar和Nvarchar是什么？

nvarchar表示可变长度 Unicode 数据，其最大长度为 4,000 字符；nchar表示固定长度的 Unicode 数据，最大长度为 4,000 个字符。

那Nchar和Nvarchar在什么情况下使用呢？我们知道字符 中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这 种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。于是支持多语言的站点应考虑使用 Unicode nchar 或 nvarchar 数据类型以尽量减少字符转换问题。同样的解释还有下面我们要研讨的NText。

下面我们看看text和blob

text分为4种类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，分别对应不同的长度。text是非二进制字符串，并且需要指定字符集，并按照该字符集进行校验和排序。只能存储纯文本，可以看作是VARCHAR在长度不足时的扩展。

blob也分为4种类型：TINYBLOB,BLOB,mediumblob和LongBlob，分别对应不同的长度，blob存储的是二进制数据，因此无需字符集校验，blob除了存储文本信息外，由于二进制存储格式，所以还可以保存图片等信息，blob可以看作是VARBINARY在长度不足时的扩展。

text和blob的各种类型存储长度，我们用如下的表格表示：

TinyBlob 最大长度255个字元(2^8-1)
TinyText 最大长度255个字元(2^8-1)
Blob 最大长度65535个字元(2^16-1)
Text 最大长度65535个字元(2^16-1)
MediumBlob 最大长度 16777215 个字元(2^24-1)
MediumText 最大长度 16777215 个字元(2^24-1
LongBlob 最大长度4294967295个字元 (2^32-1)
LongText 最大长度4294967295个字元 (2^32-1)

好了，到此char,varchar,text,blob内容探讨基本完成了，下面是我再次复习一下有关int型数据的内容，列在这里方便对比。

XML/HTML代码

· TINYINT——一个微小的整数，支持 -128到127(SIGNED)，0到255(UNSIGNED)，需要1个字节存储
· BIT——同TINYINT(1)
· BOOL——同TINYINT(1)
· SMALLINT——一个小整数，支持 -32768到32767(SIGNED)，0到65535(UNSIGNED)，需要2个字节存储 MEDIUMINT——一个中等整数，支持 -8388608到8388607(SIGNED)，0到16777215(UNSIGNED)，需要3个字节存储
· INT——一个整数，支持 -2147493648到2147493647(SIGNED)，0到4294967295(UNSIGNED)，需要4个字节存储
· INTEGER——同INT
· BIGINT——一个大整数，支持 -9223372036854775808到9223372036854775807(SIGNED)，0到18446744073709551615(UNSIGNED)，需要8个字节存储
· FLOAT(precision)——一个浮点数。precision<=24用于单精度浮点数；precision在25和53之间，用于又精度浮点数。FLOAT(X)与相诮的FLOAT和DOUBLE类型有差相同的范围，但是没有定义显示尺寸和小数位数。在MySQL3.23之前，这不是一个真的浮点值，且总是有两位小数。MySQL中的所有计算都用双精度，所以这会带来一些意想不到的问题。
· FLOAT——一个小的菜单精度浮点数。支持 -3.402823466E+38到-1.175494351E-38，0和1.175494351E-38 to 3.402823466E+38，需要4个字节存储。如果是UNSIGNED，正数的范围保持不变，但负数是不允许的。
· DOUBLE——一个双精度浮点数。支持 -1.7976931348623157E+308到-2.2250738585072014E-308，0和2.2250738585072014E-308到1.7976931348623157E+308。如果是FLOAT，UNSIGNED不会改变正数范围，但负数是不允许的。
· DOUBLE PRECISION——同DOUBLE
· REAL——同DOUBLE
· DECIMAL——将一个数像字符串那样存储，每个字符占一个字节
· DEC——同DECIMAL
· NUMERIC——同DECIMAL