MySQL基础学习笔记之——选择合适的数据类型

选择合适的数据类型

1、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 个字节

VARCHAR(4) 列显示的存储需求比实际字符长度多 1 是因为 VARCHAR 类型要用一到两个字节来记录字节长度，如果数据位占用字节数小于 255 时，用一个字节记录；大于 255 时，用两个字节记录。

检索时，CHAR 列删除了尾部空格。

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+           |
+---------------+---------------+
2 rows in set (0.00 sec)

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

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

不同存储引擎的区别：

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

---

2、TEXT 和 BLOB

保存比较大的文本时，通常会选择使用 TEXT 和 BLOB。二者的主要差别在于 BLOB 能用来保存二进制数据，比如照片；而 TEXT 只能保存字符数据。TEXT 和 BLOB 存在的一些问题：

1. BLOB 和 TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时；

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

   mysql> CREATE TABLE text(id VARCHAR(100), context text);
   Query OK, 0 rows affected (0.00 sec)
   

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

   mysql> INSERT INTO text VALUES(1, REPEAT('haha',100));
   Query OK, 1 row affected (0.00 sec)
   
   mysql> INSERT INTO text VALUES(2, REPEAT('haha',110));
   Query OK, 1 row affected (0.00 sec)
   
   mysql> INSERT INTO text VALUES(3, REPEAT('haha',120));
   Query OK, 1 row affected (0.00 sec)
   
   mysql> INSERT INTO text SELECT * FROM text;
   Query OK, 3 rows affected (0.00 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   ...
   
   mysql> INSERT INTO text SELECT * FROM text;
   Query OK, 196608 rows affected (2.45 sec)
   Records: 196608  Duplicates: 0  Warnings: 0
   

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

   [root@localhost test1]# du -sh text.*
   12K     text.frm
   204M    text.ibd
   

   从表中删除 id 为 “1” 的数据，这些数据占总数据的大约 1/3：

   mysql> DELETE FROM text WHERE id=1;
   Query OK, 131072 rows affected (1.16 sec)
   

   再次查看文件大小：

   [root@localhost test1]# du -sh text.*
   12K     text.frm
   204M    text.ibd
   

   文件大小并没有变，我们使用 OPTIMIZE（优化）操作：

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

   再次查看文件大小：

   [root@localhost test1]# du -sh text.*
   12K     text.frm
   160M    text.ibd
   

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

2. 可以使用合成的（Synthetic）索引来提高大文本字段（BLOB 和 TEXT）的查询性能；

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

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

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

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

   mysql> CREATE TABLE text2(id VARCHAR(100), context BLOB, hash_value VARCHAR(40));
   Query OK, 0 rows affected (0.14 sec)
   
   mysql> CREATE INDEX idx_hash_value ON text2(hash_value);
   Query OK, 0 rows affected (0.00 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   

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

   mysql> INSERT INTO text2 VALUES(1, REPEAT('beijing',2), MD5(context));
   Query OK, 1 row affected (0.00 sec)
   
   mysql> INSERT INTO text2 VALUES(2, REPEAT('beijing2007',2), MD5(context));
   Query OK, 1 row affected (0.00 sec)
   
   mysql> INSERT INTO text2 VALUES(3, REPEAT('beijing2008',2), MD5(context));
   Query OK, 1 row affected (0.00 sec)
   
   mysql> SELECT * FROM text2;
   +------+------------------------+----------------------------------+
   | id   | context                | hash_value                       |
   +------+------------------------+----------------------------------+
   | 1    | beijingbeijing         | 09746eef633dbbccb7997dfd795cff17 |
   | 2    | beijing2007beijing2007 | 152aef89e23078708d2a4a648cd3192f |
   | 3    | beijing2008beijing2008 | 0fe88accc8741a9d1bc323bd286866bb |
   +------+------------------------+----------------------------------+
   3 rows in set (0.00 sec)
   

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

   mysql> SELECT * FROM text2 WHERE hash_value=MD5(REPEAT('beijing2008',2));
   +------+------------------------+----------------------------------+
   | id   | context                | hash_value                       |
   +------+------------------------+----------------------------------+
   | 3    | beijing2008beijing2008 | 0fe88accc8741a9d1bc323bd286866bb |
   +------+------------------------+----------------------------------+
   1 row in set (0.00 sec)
   

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

   mysql> CREATE INDEX idx_blob ON text2(context(100));
   Query OK, 0 rows affected (0.13 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   
   mysql> DESC SELECT * FROM text2 WHERE context LIKE 'beijing%' \G
   *************************** 1. row ***************************
              id: 1
     select_type: SIMPLE
           table: text2
      partitions: NULL
            type: ALL
   possible_keys: idx_blob
             key: NULL
         key_len: NULL
             ref: NULL
            rows: 3
        filtered: 100.00
           Extra: Using where
   1 row in set, 1 warning (0.00 sec)
   

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 表进行查询。

---

3、浮点数与定点数

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

定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更精确地保存数据。如果实际插入的数值精度大于实际定义的精度，则会警告，但是实际精度按照四舍五入插入；如果是在传统模式下，会报错，不能插入。decimal（或 numberic）用来表示定点数。

mysql> CREATE TABLE num_f(f float(8, 1));
Query OK, 0 rows affected (0.11 sec)

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

mysql> INSERT INTO num_f VALUES(1.23456);
Query OK, 1 row affected (0.18 sec)

mysql> SELECT * FROM num_f;
+------+
| f    |
+------+
|  1.2 |
+------+
1 row in set (0.00 sec)

mysql> INSERT INTO num_f VALUE(1.25456);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM num_f;
+------+
| f    |
+------+
|  1.2 |
|  1.3 |
+------+
2 rows in set (0.00 sec)

浮点数与定点数的区别：

mysql> CREATE TABLE float_and_decimal(c1 FLOAT(10, 2), c2 DECIMAL(10, 2));
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT * FROM float_and_decimal;
+-----------+-----------+
| 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) {
        System.out.println("7.22 - 7.0 =" + (7.22f - 7.0f));
    }
}
# 7.22 - 7.0 =0.21999979

在编程中尽量避免浮点数的比较。下面使用定点数来实现上面的例子：

import java.math.BigDecimal;

public class Test {
    public static void main(String[] args) throws Exception{
        System.out.println("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();
    }
}
# 7.22 - 7.0 =0.22

Java 的 BigDecimal 类实现了定点数的精确计算。

在考虑定点数和浮点数的应用中，应考虑以下几个原则：

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

---

4、日期类型选择

见《MySQL 支持的数据类型》。总结一下选择日期类型的原则：

• 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录 “年份”，那么用 1 个字节来存储的 YEAR 类型完全可以满足，而不需要用 4 个字节来存储的 DATE 类型。
• 如果要记录 “年月日时分秒”，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用 TIMESTAMP。
• 如果记录的日期需要让不同时区的用户使用，那么最好使用 DATEDSTAMP，因为日期类型中只有它能够和实际时区相对应。