Press "Enter" to skip to content

【数据挖掘】MySQL中的字符串数据类型

本站内容均来自兴趣收集,如不慎侵害的您的相关权益,请留言告知,我们将尽快删除.谢谢.

首先我们需要知道,MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串类型。

 

01

 

数值类型

 

包含所有标准SQL支持的数值数据类型。

 

 

02

 

日期和时间类型

 

 

03

 

字符串类型

 

 

binary 和 varbinary 类似于 常用的char 和 varchar,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。

 

blob 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 blob :tinyblob、blob、mediumblob 和 longblob。它们区别在于可容纳存储范围不同。

 

有 4 种 text 类型:tinytext、text、mediumtext 和 longtext。对应的这 4 种 blob 类型,可存储的最大长度不同,可根据实际情况选择。

 

04

 

类型后的括号

 

在MySQL中创建表的时候经常能够看见在类型之后用括号的表示形式,例如int(M), 这里需要注意一个点,在MySQL中使用int(M)时的这个M并不是指存储在数据库中的具体的长度,不管我们的M设什幺值,在数据库里面存储都是占4个字节,所以其能表示的最大最小值也都是一致的。唯一的区别在于当你设置zerofill才能显现,他会用0补足显示宽度。同理BIGINT也是一样的。

 

但是对于varchar和char来说,括号中的M代表了其长度,对于char来说,存储空间类似INT型一样都是提前分配好的,但是varchar这类可变长度类型中的M只是指代了其最大存储长度。这里也有一点需要注意,在4.0版本以下,varchar(20),指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字节),同时其最大长度上限是255,因而要存储更大长度的字符串时只能使用固定的TEXT或BLOB格式。而在5.0版本以上,varchar(20),指的是20,无论存放的是数字、字母还是UTF8汉字(每个汉字3字节),都可以存放20个,最大大小是65532字节 ,这也是由于MySQL中的行的定义长度不能超过65535,若定义的表长度超过这个值也将报错。

 

05

 

常用字符串类型的特点

 

在字符串类型中,每一种类型的存储方式和数据的检索方式都不一样。

 

常用的几种字符串数据类型的检索效率是:char > varchar > text

 

在空间占用方面:

 

char:存储定长数据很方便,char字段上的索引效率级高,必须在括号里定义长度,可以有默认值,比如定义char(10),那幺不论你存储的数据是否达到了10个字符,都要占去10个字符的空间(自动用空格填充),且在检索的时候后面的空格会隐藏掉,所以检索出来的数据需要记得用什幺trim之类的函数去过滤空格。

 

varchar:存储变长数据,但存储效率没有char高,必须在括号里定义长度,可以有默认值。保存数据的时候,不进行空格自动填充,而且如果数据存在空格时,当值保存和检索时尾部的空格仍会保留。另外,varchar类型的实际长度是它的值的实际长度+1(如果长度大于255将+2),这个字节用于保存实际使用了多大的长度。

 

text:存储可变长度的非Unicode数据,最大长度为2^31-1个字符。text列不能有默认值,存储或检索过程中,不存在大小写转换,后面如果指定长度,不会报错误,但是这个长度是不起作用的,意思就是你插入数据的时候,超过你指定的长度还是可以正常插入。

 

从碎片角度进行考虑,使用char字符型时,由于存储空间都是一次性分配的。为此某个字段的内容,其都是存储在一起的。单从这个角度来讲,其不存在碎片的困扰。而可变长度的字符数据类型,其存储的长度是可变的。当其更改前后数据长度不一致时,就不可避免的会出现碎片的问题。故使用可变长度的字符型数据时,数据库管理员要时不时的对碎片进行整理。如执行数据库导出导入作业,来消除碎片。

 

另一点是考虑其长度的是否相近,如果某个字段其长度虽然比较长,但是其长度总是近似的,如一般在90个到100个字符之间,甚至是相同的长度。此时比较适合采用char字符类型。比较典型的应用就是MD5哈希值。当利用MD5哈希值来存储用户密码时,就非常使用采用char字符类型。因为其长度是相同的。另外,像用来存储用户的身份证号码等等,一般也建议使用char类型的数据。

 

06

 

实际业务中的应用

 

在最近处理的一个业务需求中,需要减小大数据量级(千万行)下的MySQL存储空间,需求需要整合一个大宽表,表中只用包含一个用户id作为主键来加速查询,并需要将对应用户的100余个特征属性按列存储下来。对于短留存用户来说,其大部分属性都为空,并且数据中的短留存数据占比很大。

 

如果使用int,bigint,float,char等类型来存储对应的属性,我们一般会将其对应空值用一个无意义的数字来替代,就算对应用户的属性为null,其占用的空间也还是固定的,在实际测试中将大大占用存储空间。

 

这时我们就可以使用varchar可变存储长度的特点来缩小表所占用的空间。在存储过程中,由于除role_id列以外的其他列没有索引要求,所以将除主键外的其他列都建为varchar型。如果对应用户的属性为空,我们就以一个内容为空的字符串来赋值,这样varchar类型只用其实际长度来占用空间,这将比使用int型这些节省很大的存储资源。在测试后最终使用这种方法减少了60%的存储资源浪费。

 

07

 

结论

 

 

频繁更新的字段用char

 

能够确认固定长度或者长度在固定区间内的用char

 

InnoDB中尽量用varchar

 

超过255字节的只能用varchar或者text

 

能用varchar的地方不用text

 

能够用数字类型的字段尽量选择数字类型而不用字符串类型的(电话号码),这会降低查询和连接的性能,并会增加存储开销。但是如果对应需求对索引要求不高并包含大量空值,可以使用变长的varchar存储来节省存储空间。

 

Be First to Comment

发表评论

邮箱地址不会被公开。 必填项已用*标注