mysql字段类型char,varchar,text,blob用法解析

发布时间:2020-03-07编辑:脚本学堂
有关mysql字段类型char,varchar,text,blob的用法,通过实例来理解这些字段内容的应用场景,char,varchar,text,blob字段类型的区别等,需要的朋友参考下。

mysql/ target=_blank class=infotextkey>mysql数据库中char,varchar,text,blob字段类型用法

总结:

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-30*3)/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-30*3)/3=21812。我做了一个简单的实验,看截图。

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

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

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

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

总之: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倍的内存空间,在临时表和排序的时候这个差别几乎可能差一个数量级,于是效率可想而知。

基本上解释完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)

复习一下有关int型数据的内容,列在这里方便对比。
 

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