创建 MySQL 表的时候都会遇到如何选择合适的字段类型的问题，不同的数据如何存储？虽然 MySQL 提供了丰富的数据类型，但正因为可选择的数据类型太多，才需要依据一些原则来 “挑选” 最适合的数据类型。下面将详细介绍字符、数值、日期数据类型的选择原则。

CHAR 与 VARCHAR

CHAR 和 VARCHAR 类似，都是用来存储字符串，但它们的保存和检索方式不同。CHAR 数据固定长度的字符类型，而 VARCHAR 是可变长度的字符类型。

下表显示了将各种字符串保存到 CHAR(4) 列和 VARCHAR(4) 列的差别：

值 CHAR(4) 存储 VARCHAR(4) 存储
'' ' ' 4 个字节 '' 1 个字节
'ab' 'ab ' 4 个字节 'ab' 3 个字节
'abcd' 'abcd' 4 个字节 'abcd' 5 个字节
'abcdefgh' 'abcd' 4 个字节 'abcd' 5 个字节

最后一行，插入 'abcdefgh' 仅限在 MySQL 的 “非严格模式”时，这时插入数据只会截断超出部分，而使用 “严格模式”，除了截断超出部分外，还会提示错误。

VARCHAR 实际存储比实际字符长度多一个字节是因为 VARCHAR 需要一到两个字节来记录字符长度，如果数据字符长度小于255时，则 数据长度+1，如果大于255时，则需要用两个字节来记录，即：数据长度+2。

由于 CHAR 是 固定长度，所以处理起来比 VARCHAR 快，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于长度变化不大且对查询速度有较高要求的数据可以考虑使用 CHAR 类型。

在使用 VARCHAR 类型的时候，不能因为 VARCHAR 是可变长度就设置一个很大的长度，仍然需要按需定义长度，定义一个远超实际需求长度的 VARCHAR 字段可能会影响应用程序的效率，并且又机率触发 MySQL 在VARCHAR 上的一些BUG。

在 MySQL 中，不同的存储引擎对 CHAR 和 VARCHAR 使用的原则有所不同，简单概括如下：

MyISAM 存储引擎

建议使用固定长度的数据列代替可变长度的数据列。

MEMORY 存储引擎

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

InnoDB 存储引擎

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

TEXT 与 BLOB

保存少量字符串的时候，会选择 CHAR 和 VARCHAR ；而保存大文本的时候通常会选择 TEXT 或 BLOB 。两个的区别是 BLOB 能保存二进制数据，比如照片，而 TEXT 只能保存字符数据，比如文章或日记。具体有分为：TEXT： TINYTEXT 、TEXT、MEDIUMTEXT、LONGTEXT BLOB： TINYBLOB 、BLOB 、MEDIUMBLOB 、LONGTEXT 等不同类型，区别主要是存储文本长度不同和存储字节不同，应该根据实际需求选择能满足需求的最小存储类型。

下面主要介绍 TEXT 和 BLOB 存在的常见问题：

BLOB 和 TEXT 的值会引起一些性能问题，尤其是执行大量的删除操作时。

删除操作会在数据表中留下很大的 “空洞”，以后填入这些空洞，的记录在插入的性能上会有影响。为了提高性能，建议定期执行 OPTIMIZE TABLE 功能对这类表进行碎片整理。

可以使用合成的（ Synthetic ）索引来提高大文本字段的查询性能。

简单地说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值保存在独立的数据列中，然后通过检索散列值找到数据进行了。需要注意的是，这种技术只能用于精确匹配的查询。

可以使用 MD5() 函数生成散列值，也可以使用 SHA1() 、 CRC32() 或使用自己的应用程序逻辑来计算散列值，数值型的散列值可以高效的存储。

合成的散列值索引对于那些 BLOB 或 TEXT 数据列特别有用，使用散列值查找的速度，比搜索 BLOB 本身快很多。

上面这个方法只能用于精确匹配，在一定程度上，减少了 I/O 开销，从而提升了查询效率。如果需要对 BLOB 或 TEXT 进行模糊查询，MySQL 提供了前缀索引，也就是只为字段的前几列创建索引：

  1. mysql> CREATE TABLE t(context BLOB);
  2. Query OK, 0 rows affected (0.06 sec)
  4. mysql> CREATE INDEX idx_blob ON t(context(100));
  5. Query OK, 0 rows affected (0.03 sec)
  6. Records: 0  Duplicates: 0  Warnings: 0
  8. mysql> DESC SELECT * FROM t WHERE context like 'beijing%' \G;
  9. *************************** 1. row ***************************
  10.            id: 1
  11.   select_type: SIMPLE
  12.         table: t
  13.    partitions: NULL
  14.          type: range
  15. possible_keys: idx_blob
  16.           key: idx_blob
  17.       key_len: 103
  18.           ref: NULL
  19.          rows: 1
  20.      filtered: 100.00
  21.         Extra: Using where
  22. 1 row in set, 1 warning (0.01 sec)

可以发现，对 context 的前 100 个字符进行模糊查询，就可能用到前缀索引。由于这里数据只有一行，虽然索引可用，但优化器最终没有选择使用索引。另外，这里都模糊查询条件中，“%” 不能放在最前面，否则索引不会被使用。

在非必要的时候避免检索大型的 BLOB 或 TEXT 值

比如 SELECT * 查询就不是很好的想法，除非能确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则可能会毫无目的地在网络上传输大量的值。这也是 BLOB 或 TEXT 标识符信息存储在合成的索引列中对用户有所帮助的例子。用户可以检索索引列，决定选需要那些数据行，然后从符合条件的数据行中检索 BLOB 或TEXT 的值。

（大概意思就是，查询这条数据 只需要其中一两个字段，但使用 * 会返回所有的字段 包括长文本的字段，但实际上是用不到的，并且因为是长字段，会特别消耗网络）

把 BLOB 或 TEXT 分离到单独的表中

有时候确实有使用 BLOB 或 TEXT 的需求，这时候建议将 BLOB 或 TEXT 类型的字段分离到单独的表中存储。这样会减少主表中的碎片，显著减少主表数据量从而获得性能优势，主表运行在 SELECT * 查询的时候也不需要通过网络传输大量的 BLOB 或 TEXT 的值。

例如：user_info 表需要一个 BLOB 字段来存储用户身份证图片信息，更好的做法是新建一个user_id_pic 的表，包含 user_id 和 id_pic 两个字段，大多数查询只要user_info 表就能完成，只有在需要身份证图片的时候才回去关联 user_id_pic 表。

尽可能在 OLTP 环境避免使用 BLOB 或 TEXT类型，优先使用VARCHAR，VARCHAR 最长支持 65535 字节长度的字符串，可以满足绝大多数的需求。

浮点数与定点数

浮点数一般表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入的数据精度超过该列实际精度，则插入的值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错，在 MySQL 中， float 和 double （或 real） 用来表示浮点数。

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

了解定点数和浮点数的区别后，我们用例子来验证一下浮点数精确性的问题：

mysql> CREATE TABLE t (f1 FLOAT(8,1),f2 FLOAT(10,2),d1 decimal(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> DESC t;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| f1    | float(8,1)    | YES  |     | NULL    |       |
| f2    | float(10,2)   | YES  |     | NULL    |       |
| d1    | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO t VALUES(1.2345,131072.32,131072.32);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+-----------+-----------+
| f1   | f2        | d1        |
+------+-----------+-----------+
|  1.2 | 131072.31 | 131072.32 |
+------+-----------+-----------+
1 row in set (0.00 sec)

mysql> INSERT INTO t VALUES(1.2545,131072.32,131072.32);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+-----------+-----------+
| f1   | f2        | d1        |
+------+-----------+-----------+
|  1.2 | 131072.31 | 131072.32 |
|  1.3 | 131072.31 | 131072.32 |
+------+-----------+-----------+
2 rows in set (0.00 sec)

f1 列 第一次插入时，1.2345 被截断成 1.2；第二次插入时，1.2535 被四舍五入后截断成1.3。所以在使用浮点型保存小数时要注意四舍五入的问题。

f2 列 在上面的例子中，c1 列的值由 131072.32 变为 131072.31，这是数值在使用单精度浮点数表示时，产生了误差。这是浮点数特有的问题。因此在存储精度要求比较高的应用中（比如金额）要使用定点数而不是浮点数来存储数据。

注意： 在今后关于浮点数和定点数的应用中，要考虑以下几个原则：

  • 浮点数存在误差
  • 对货币等精度敏感的数据，应该用定点数来表示或存储
  • 在编码过程中，如果遇到浮点数，要特别注意误差问题，并尽量避免做浮点数的比较
  • 要注意浮点数中一些特殊值的处理

日期类型选择

MySQL 提供的常用日期类型由 DATE、TIME、DATETIME、TIMESTAMP，区别已经在 “数据类型” 中详细描述，这里主要总结选择日期类型的原则。

  • 根据实际需求选择能够满足应用的最小存储类型。如果应用只需要记录 “年份”，那使用一个字节的 TEAR 类型完全可以满足，而不需要用四个字节来存储的 DATE 类型。不仅能节约存储，还能提高操作效率。
  • 如果要记录年月日时分秒，并且记录的年份时间较久远，那么最好使用 DATETIME 而不是使用 TIMESTAMP 。因为 TIMESTAMP 表示的时间范围比 DATETIME 要短得多。
  • 如果记录的日期要让不同时区的用户使用，那么最好使用 TIMESTAMP，日期类型中只有它能够和实际时区相对应。