MySQL选择合适的数据类型（八）

在使用 MySQL 创建数据表时都会遇到一个问题，如何为字段选择合适的数据类型。例 如，创建一张员工表用来记录员工的信息，这时对员工的各种属性如何来进行定义？也许大 家会想，这个问题很简单，每个字段可以使用很多种数据类型来定义，比如 int、float、double、 decimal 等。其实正因为可选择的数据类型太多，才需要依据一些原则来“挑选”适合的 数据类型。本章将详细介绍字符、数值、日期数据类型的一些选择原则。 

CHAR 与 VARCHAR 

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

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

下面通过一个例子来说明

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4)); 
Query OK, 0 rows affected (0.02 sec) 
  
mysql> INSERT INTO vc VALUES ('ab  ', 'ab  '); 
Query OK, 1 row affected (0.00 sec) 
  
mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc; 
+----------------+----------------+ 
| CONCAT(v, '+') | CONCAT(c, '+') | 
+----------------+----------------+ 
| ab  +          | ab+            | 
+----------------+----------------+ 
1 row in set (0.00 sec)

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

在 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 和 BLOB 中有分别包括 TEXT、MEDIUMTEXT、LONGTEXT 和 BLOB、MEDIUMBLOB、LONGBLOB3 种不同的类型，它们之间的主要区别是存储文本长度不同和存储字节不同，用户应该根据实际情况选择能够满足 需求的小存储类型。本节主要对 BLOB 和 TEXT 存在的一些常见问题进行介绍。 

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

下面的例子述了 OPTIMIZE TABLE 的碎片整理功能。 

1）创建测试表 t，字段 id 和context 的类型分别为 varchar(100)和 text： 

mysql> create table t (id varchar(100),context  text); 
Query OK, 0 rows affected (0.01 sec)

2）往 t 中插入大量记录，这里使用 repeat 函数插入大字符串： 

mysql> insert into t values(1,repeat('haha',100)); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> insert into t values(2,repeat('haha',100)); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> insert into t values(3,repeat('haha',100)); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> insert into t select * from t; 
      … 
     mysql> insert into t select * from t; 
Query OK, 196608 rows affected (4.86 sec) 
Records: 196608  Duplicates: 0  Warnings: 0 
 
     mysql> exit 
Bye 

退出到操作系统下，查看表 t 的物理文件大小： 

[bjguan@zzx test]$ du -sh t.* 
125 
 
16K     t.frm 
155M    t.MYD 
8.0K    t.MYI 

这里数据文件显示为 155MB。

删除数据

delete from t where id=1; 

再次退出到操作系统下，查看表 t 的物理文件大小： 

[bjguan@zzx test]$ du -sh t.* 
16K     t.frm 
155M    t.MYD 
8.0K    t.MYI 
 

可以发现，表 t 的数据文件仍然为 155MB，并没有因为数据删除而减少。 

接下来对表进行 OPTIMIZE（优化）操作： 

mysql> OPTIMIZE TABLE  t; 
+--------+----------+----------+----------+ 
| Table  | Op       | Msg_type | Msg_text | 
+--------+----------+----------+----------+ 
| test.t | optimize | status   | OK       | 
+--------+----------+----------+----------+ 
1 row in set (2.88 sec) 
 
mysql> exit 

再次查看表 t 的物理文件大小

[bjguan@localhost test]$ du -sh t.* 
16K     t.frm 
104M    t.MYD 
8.0K    t.MYI 

可以发现，表的数据文件大大缩小，“空洞”空间已经被回收。

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

下面通过实例介绍一下合成索引的使用方法。

创建测试表 t，字段 id、context、hash_value 字段类型分别为 varchar(100)、blob、 varchar(40)：

mysql>  create table t (id varchar(100),context  blob,hash_value varchar(40));
Query OK, 0 rows affected (2.03 sec)
mysql>  insert into t values(1,repeat('beijing',2),md5(context));
Query OK, 1 row affected (0.16 sec)

mysql> insert into t values(2,repeat('beijing',2),md5(context));
Query OK, 1 row affected (0.13 sec)

mysql> insert into t values(3,repeat('beijing 2008',2),md5(context));
Query OK, 1 row affected (0.13 sec)
mysql>  select * from t;
+------+--------------------------+----------------------------------+
| id   | context                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 1    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 3    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
3 rows in set (0.00 sec)

3）如果要查询 context 值为“beijing 2008beijing 2008”的记录，可以通过相应的散列值来 查询： 

mysql> desc select * from t where context like 'beijing%' \G; 
*************************** 1. row *************************** 
           id: 1 
  select_type: SIMPLE 
        table: t 
         type: range 
possible_keys: idx_blob 
          key: idx_blob 
      key_len: 103 
          ref: NULL 
         rows: 2 
        Extra: Using where 
1 row in set (0.00 sec) 

上面的例子展示了合成索引的用法，由于这种技术只能用于精确匹配，在一定程度上减 少 I/O，从而高查询效率。如果需要对 BLOB 或者 CLOB 字段进行模糊查询，MySQL 供了 前缀索引，也就是只为字段的前 n 列创建索引，举例如下:

  create index idx_blob on t(context(100));

可以发现，对context 前 100 个字符进行模糊查询，就可以用到前缀索引。请注意，这里的 查询条件中， “%”不能放在前面，否则索引将不会被使用。

desc select * from t where context like 'beijing%'

① 合成索引只适用于精确匹配，如果要用到对TEXT文本内容的模糊搜素，需要使用前缀索引 
② 建立散列值的方法不止一种，主要使用MD5函数 
③ 大文本字段被执行了大量的删除操作后，会在数据表中留下大量的空洞，可以定期执行OPTIMIZE TABLE对该类表进行整理

 浮点数与定点数 

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

在简单了解了浮点数和定点数的区别之后，来看一个例子，回顾一下前面讲到的浮点数 精确性问题。 

mysql> create table t (f float( 8,1));
Query OK, 0 rows affected (0.31 sec)

mysql> desc t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| f     | float(8,1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t values (1.23456);
Query OK, 1 row affected (0.13 sec)

mysql>  select * from t;
+------+
| f    |
+------+
|  1.2 |
+------+
1 row in set (0.00 sec)

mysql> insert into t values (1.25456);
Query OK, 1 row affected (0.14 sec)

mysql>  select * from t;
+------+
| f    |
+------+
|  1.2 |
|  1.3 |
+------+
2 rows in set (0.00 sec)

从上面的例子中，可以发现对于第一次插入值 1.23456 到 float（8,1）时，该值被截断，并保存为 1.2，而第二次插入值 1.25456 到 float（8,1）时，该值进行了四舍五入然后被截断，并保存为 1.3，所以在选择浮点型数据保存小数时，要注意四舍五入的问题，并尽量保留足够的小数位，避免存储的数据不准确。

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

mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (1.98 sec)

mysql> insert into test values(131072.32,131072.32);
Query OK, 1 row affected (0.03 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)    { 
      
130 
 
        BigDecimal b1 = new BigDecimal(Double.toString(v1)); 
         
        BigDecimal b2 = new BigDecimal(Double.toString(v2)); 
         
        return b1.subtract(b2).doubleValue(); 
    } 
 } 

上面的实例使用 Java 的 BigDecimal 类实现了定点数的精确计算，所以 7.22 减 7.0 的结果和 预想的相同，为 7.22-7.0=0.22。 

注意：在今后关于浮点数和定点数的应用中，用户要考虑到以下几个原则： 
  浮点数存在误差问题； 
  对货币等对精度敏感的数据，应该用定点数表示或存储； 
  在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较； 
  要注意浮点数中一些特殊值的处理。

日期类型选择 

这章就不多说了，在MySQL 数据类型篇（四）这篇中已经说明啦。

 

小结 

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