选择合适的数据类型

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

CHAR 与 VARCHAR

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

下表显示了将各种字符串值保存到CHAR(4) 和 VARCHAR(4) 列后的结果，说明了 CHAR 和 VARCHAR 之间的差别。

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

注意上表中最后一行的值只适用 MySQL 运行在非 “严格模式” 时，如果MySQL 运行在严格模式，超过列长度的值将不会保存，并且会出现错误提示。VARCHAR(4) 列显示的存储需求比实际字符长度多1 是因为 VARCHAR 类型要用一到两个字节来记录字节长度，如果数据位占用字节数小于255时，用一个字节记录；大于 255 时，用两个字节记录。

从CHAR(4) 和 VARCHAR(4)列检索的值并不总是相同，因为检索时从CHAR列删除了尾部的空格。下面通过一个例子来说明差别：

mysql> create table vc (v varchar(4), c char(4));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into vc values('ab  ', 'ab  ');
Query OK, 1 row affected (0.01 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 类型来存储。

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

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

MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。
MEMORy 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或 VARCHAR 列都没有关系，两者都是作为 CHAR类型处理。
InnoDN 存储引擎：建议使用VARCHAR 类型。对于 InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度 (所有数据行都使用指向数据列值的头指针)，因此在本质上，使用固定长度的CHAR列不一定比使用可变长度 VARCHAR列性能要好。因为，主要性能因素是数据行使用的存储总量。由于CHAR 平均占用的空间多余 VARCHAR，因此使用VARCHAR来最下化需要处理的数据行的存储总量和磁盘I/O是比较好的。

TEXT 与 BLOB

一般在保存少量字符串的时候，我们会选择 CHAR 或者 VARCHAR；而在保存较大文本时，通常会选择使用 TEXT 或者 BLOB。二者之间的主要差别是 BLOB能用来保存二进制数据，比如照片；而TEXT只能保存字符数据，比如一篇文章或者日记。TEXT和BLOB 中又分别包括 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT 和 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 等不同的类型，它们之间的主要区别是存储文本长度不同和存储字节不同，用户应该根据实际情况选择能够满足需求的最下存储类型。本节主要对 BLOB 和 TEXT存在的一些常见问题进行介绍。

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

下面的例子描述了 OPTIMIZE TABLE 的碎片整理功能。首先创建测试表t,字段 id 和 context 的类型分别为 varchar(100) 和 text

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

mysql> insert into t values (1, repeat('haha',100));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (2, repeat('haha',100));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (3, repeat('haha',100));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t select * from t;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t select * from t;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
....
mysql> insert into t select * from t;
Query OK, 196608 rows affected (3.13 sec)
Records: 196608  Duplicates: 0  Warnings: 0

查看磁盘中，表t 的物理文件大小:

2020/08/26  20:51       197,132,288 t.ibd

这里的数据文件显示为197MB。从表t中删除id 为“1”的数据，这些数据占用总数据量的1/3。

mysql> delete from t where id=1;
Query OK, 131072 rows affected (3.51 sec)

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

2020/08/26  21:01       197,132,288 t.ibd

可以发现，表t的数据文件仍然为197MB，并没有因为数据删除而减少。接下来对表进行OPTIMIZE（优化）操作：

mysql> OPTIMIZE TABLE t\G;
*************************** 1. row ***************************
   Table: test.t
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: test.t
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (12.75 sec)

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

2020/08/26  21:04       150,994,944 t.ibd

可以发现，表的数据文件大大缩小，“空洞”空间已经被回收。另外，注意到，对于 InnoDB引擎，OPTIMIZE 语句被自动转换为 recreate+analyze语句。

（2）可以使用合成的（Synthetic）索引来提高大文本字段（BLOB或TEXT）的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似“<” 或“>=” 等范围搜索操作符是没有用处的）。

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

同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在 CHAR 字段中，而是使用 VARCAHR字段，因为 CHAR 字段会受到尾部空格去除的影响。合成的散列索引对于那些BLOB 或TEXT数据列特别有用。用散列标识符值查找的速度比搜索 BLOB 列本身的速度快很多

下面通过实例介绍一下合成索引的使用方法。首先创建测试表t，字段 id、context、hash_value 字段类型分别为 varchar(100)、blob、varchar(40)，并且在hash_value列上创建索引：

mysql> create table t (id varchar(100),context blob, hash_value varchar(40));
Query OK, 0 rows affected (0.05 sec)
mysql> create index idx_hash_value on t (hash_value);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后往 t 中插入测试数据，其中 hash_value 用来存放 context 列的MD5 散列值：

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

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

mysql> insert into t values (3, repeat('beijing 2008',2),md5(context));
Query OK, 1 row affected (0.01 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)

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

mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));
+------+--------------------------+----------------------------------+
| id   | context                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 3    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
1 row in set (0.00 sec)

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

mysql> create index idx_blob on t(context(100));
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

ERROR:
No query specified

可以发现，对 context 前100个字符进行模糊查询，就可能用到前缀索引。注意，由于这个表只有3行数据，虽然索引可用，但优化器最终并没有选择使用索引，关于索引是否被用的更多内容，将会在后面的章节中介绍。另外，这里的查询条件中，“%” 不能放在最前面，否则索引将不会被使用。

（3）在不必要的时候避免检索大型的BLOB 或 TEXT 值

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

（4）把 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 类型最长可以支持 65533 字节，已经可以满足绝大多数的需求。

浮点数与定点数

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

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

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

mysql> create table t (f float(8,1));
Query OK, 0 rows affected (0.06 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.01 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.01 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 (0.06 sec)

mysql> insert into test values (131072.32, 131072.32);
Query OK, 1 row affected (0.01 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(Stringp[] args) {
        System.out.print("7.22-7.0=" + (7.22f - 7.0f));
    }
    
}

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

下面使用定点数来实现上面地例子：

public class Test {
    
    public static void main(Stringp[] args) {
        System.out.print("7.22-7.0=" + (7.22f - 7.0f));
    }
    
    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(); 
    }
    
}

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

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

浮点数存在误差问题

对货币等对精度敏感的数据，应该用定点数表示或存储

在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较

要注意浮点数中一些特殊值得处理

日期类型选择

MySQL 提供的常用日期类型有 DATE、TIME、DATETIME 和 TIMESTAMP，它们之间的区别我们已经详细论述了，这里就不再赘述。下面主要总结一下选择日期类型得原则：

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