【数据挖掘】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

结论

1. 频繁更新的字段用char

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

3. InnoDB中尽量用varchar

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

5. 能用varchar的地方不用text

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