08-如何选择数据类型

6.2 如何选择数据类型

本节视频教学录像：5分钟

MySQL提供了大量的数据类型，为了优化存储，提高数据库性能，在不同情况下应使用最精确的类型。在选择数据类型时，在可以表示该字段值的所有类型中，应当使用占用存储空间最少的数据类型。因为这样不仅可以减少存储（内存、磁盘）空间，从而节省I/O资源（检索相同数据情况下）；还可以在数据计算的时候减轻CPU负载。

1.整数和浮点数

如果插入的数据不需要小数部分，则使用整数类型存储数据；如果需要小数部分，则使用浮点数类型。例如，如果字段取值范围在1～50000，选择SMALLINT UNSIGNED是最好的；假如需要存储带有小数位的值如3.1415926，则需选择浮点数类型。

浮点类型包括FLOAT和DOUBLE类型，DOUBLE类型精度比FLOAT高。因此，需要存储精度较高时，需选择DOUBLE类型。

2.浮点数和定点数

浮点型FLOAT和DOUBLE与定点型DECIMAL的不同点是，在长度固定的情况下，浮点型能表示的数据范围更大。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。由于浮点型容易因四舍五入产生误差，因此对于精确度要求比较高时，要使用定点型DECIMAL来存储。

定点数实际上是以字符串形式存放的，所以定点数可以更精确地保存数据。如果实际插入的数值精度大于实际定义的精度，则MySQL会进行警告（默认的SQLMode下），但是数据按照实际精度四舍五入后插入；如果SQLMode是在TRADITIONAL（传统模式）下，则系统会直接报错，导致数据无法插入。

在数据迁移中，FLOAT(M,D)是非标准SQL定义，数据迁移可能出现问题，最好不要使用。另外，两个浮点型数据进行减法和比较运算时也容易出问题，因此在进行计算的时候，一定要注意，如果要进行数值比较，最好使用定点型DECIMAL。

3.日期与时间类型

MySQL中选择日期类型的原则如下。

⑴根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全能够满足，而不需要用4个字节来存储的DATE类型。这样不仅仅能节约存储，更能够提高表的操作效率。

⑵如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIEM，而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多。

⑶如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。而且当插入一条记录时没有指定TIMESTAMP这个字段值的话，MySQL会把TIMESTAMP字段设为当前的时间。因此当需要在插入记录的同时插入当前时间时，使用TIMESTAMP比较方便。

4.CHAR与VARCHAR

CHAR 和 VARCHAR 类型类似，都用来存储字符串，但它们保存和检索的方式不同。CHAR 属于固定长度的字符类型，而 VARCHAR 属于可变长度的字符类型。CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部的空格。

由于 CHAR 是固定长度的，所以它的处理速度比 VARCHAR 快得多，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大且对查询速度有较高要求的数据可以考虑用CHAR 类型来存储。另外，在MySQL中，不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同，概括如下。

⑴MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。

⑵MEMORY存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或 VARCHAR列都没有关系，两者都是作为 CHAR 类型处理。

⑶InnoDB存储引擎：建议使用 VARCHAR 类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 CHAR 列不一定比使用可变长度 VARCHAR 列性能要好。因此，主要的性能因素是数据行使用的存储总量。由于 CHAR 平均占用的空间多于 VARCHAR，因此使用 VARCHAR 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的。

5.ENUM和SET

ENUM只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。因此，在需要从多个值中选取一个时，可以使用ENUM。例如，性别字段适合定义为ENUM类型，每次只能从“男”和“女”中取一个值。

SET可以取多个值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。因此，在需要取多个值的时候，适合使用SET类型。例如，要存储一个人的特长，最好使用SET类型。

ENUM和SET的值是以字符串形式出现的，但在MySQL内部，实际是以数值索引的形式存储它们。

6.BLOB和TEXT

一般保存少量字符串的时候，可以选择 CHAR 或者 VARCHAR，而在保存大文本时，通常会选择使用 TEXT 或者 BLOB，二者之间的主要差别是 BLOB 能用来保存二进制数据，比如照片、音频信息等；而 TEXT 只能保存字符数据，比如一篇文章或者日记。以下是BLOB 与 TEXT 存在的一些常见的问题。

⑴BLOB 和 TEXT 值会引起一些性能问题，特别是执行了大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。

⑵使用合成的（Synthetic）索引来提高大文本字段（BLOB 或 TEXT）的查询性能。

⑶在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT *查询就不是很好的想法，除非能够确定作为约束条件的WHERE子句只会找到所需要的数据行，否则，很可能毫无目的地在网络上传输大量的值。

⑷把 BLOB 或 TEXT 列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行 SELECT *查询的时候不会通过网络传输大量的 BLOB 或 TEXT 值。