第8章 选择合适的数据类型

8.1 CHAR 与 VARCHAR

随着 MySQL 版本的不断升级，VARCHAR 数据类型的性能也在不断改进并提高，所以在许多的应用中，VARCHAR 类型被更多地使用。

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

8.2 TEXT 与 BLOB

一般在保存少量字符串的时候，我们会选择 CHAR 或者 VARCHAR；而在保存较大文本时，通常会选择使用 TEXT 或者 BLOB，二者之间的主要差别是 BLOB 能用来保存二进制数据，比如照片；而 TEXT 只能保存字符数据，比如一篇文章或者日记。

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

可以使用合成的（Synthetic）索引来提高大文本字段（BLOB 或 TEXT）的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。可以使用 MD5()函数生成散列值，也可以使用 SHA1()或 CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在 CHAR 或 VARCHAR 列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些 BLOB 或 TEXT 数据列特别有用。用散列标识符值查找的速度比搜索BLOB 列本身的速度快很多。

8.3 浮点数与定点数

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

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

为了能够让大家了解浮点数与定点数的区别，再来看一个例子：

mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.29 sec)
mysql> insert into test values(131072.32,131072.32);
Query OK, 1 row affected (0.07 sec)
mysql> select * from test;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)

从上面的例子中可以看到，c1 列的值由 131072.32 变成了 131072.31，这是上面的数值在使用单精度浮点数表示时，产生了误差。这是浮点数特有的问题。因此在精度要求比较高的应用中（比如货币）要使用定点数而不是浮点数来保存数据。

另外，浮点数的比较也是一个普遍存在的问题，下面的程序片断中对两个浮点数做减法运算：

public class Test {
		public static void main(String[] args) throws Exception {
		System.out.print("7.22-7.0=" + (7.22f-7.0f));
}
}

对上面 Java 程序的输出结果可能会想当然的认为是 0.22，但是，实际结果却是7.22-7.0=0.21999979，因此，在编程中应尽量避免浮点数的比较，如果非要使用浮点数比较则最好使用范围比较而不要使用“==”比较。再看一下使用定点数来实现上面的例子：

import java.math.BigDecimal;
/*
* 提供精确的减法运算。
* @param v1
* @param v2
*/
public class Test {
public static void main(String[] args) throws Exception {
	System.out.print("7.22-7.0=" + subtract(7.22,7.0));
}
public static double subtract(double v1, double v2) {
    BigDecimal b1 = new BigDecimal(Double.toString(v1));
	BigDecimal b2 = new BigDecimal(Double.toString(v2));
	return b1.subtract(b2).doubleValue();
}
}

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

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

8.4 小结

本章中主要介绍了常见数据类型的选择原则，简单归纳如下。  对于字符类型，要根据存储引擎来进行相应的选择。  对精度要求较高的应用中，建议使用定点数来存储数值，以保证结果的准确性。  对含有 TEXT 和 BLOB 字段的表，如果经常做删除和修改记录的操作要定时执行 OPTIMIZE TABLE 功能对表进行碎片整理。