12. MySQL数据类型的选择

1. CHAR 与 VARCHAR
2. TEXT 与 BLOB
3. 浮点数与定点数

1. CHAR 与 VARCHAR⚓

VARCHAR在存储数据的时候会占用比字符串的长度多一个字节的空间。
CHAR 在检索时会删掉尾部的空格。
CHAR 的处理速度比VARCHAR 快得多。

不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同：

MyISAM 存储引擎：建议使用 CHAR 数据列代替 VARCHAR 的数据列。
MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或 VARCHAR 列都没有关系。两者都是作为 CHAR 类型处理。
InnoDB 存储引擎：建议使用 VARCHAR 类型。对于 InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），并且 CHAR 平均占用的空间多于 VARCHAR。

2. TEXT 与 BLOB⚓

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

2.可以使用合成的（Synthetic）索引来提高大文本字段（BLOB 或 TEXT）的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值（MD5()、SHA1()或 CRC32()），并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。 但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。 如果散列算法生成的字符串带有尾部空格，就不要把它们存储在 CHAR 或 VARCHAR 列中，它们会受到尾部空格去除的影响。

MariaDB [MYISAM_TEST]> DESC HASH ;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| ID         | int(11)     | NO   | PRI | NULL    | auto_increment |
| STRING     | varchar(5)  | YES  |     | NULL    |                |
| HASH_VALUE | varchar(40) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

MariaDB [MYISAM_TEST]> INSERT INTO HASH VALUES (0, 'SHIHR', MD5('SHIHR'));

MariaDB [MYISAM_TEST]> SELECT * FROM HASH WHERE HASH_VALUE=MD5('SHIHR');

如果需要对 BLOB 或者 TEXT 字段进行模糊查询， MySQL 提供了前缀索引（注意，并不是这两种类型特有的），也就是只为字段的前 n 列创建索引：

MariaDB [MYISAM_TEST]> SHOW CREATE TABLE BIGCONTENT \G;
Create Table: CREATE TABLE `BIGCONTENT` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `CONTENT` text,
  PRIMARY KEY (`ID`),
  KEY `CONTENT` (`CONTENT`(5))
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

# 查询
MariaDB [MYISAM_TEST]> SELECT * FROM BIGCONTENT WHERE CONTENT LIKE 'SHIHR%';

注意：只有后缀%，如果带有前缀%，则不会使用索引进行查询。

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

4.把 BLOB 或 TEXT 列分离到单独的表中。这会减少主表中的碎片，可以得到固定长度数据行的性能优势。

3. 浮点数与定点数⚓

浮点数：float、double（real），截断时四舍五入

定点数：decimal（numberic），截断时不会四舍五入

两者在插入的数值的整数位超过精度之后，所有的位数都变为9；小数位超过精度后，会发生截断。

MariaDB [MYISAM_TEST]> SHOW CREATE TABLE FLOAT_NUM \G;
*************************** 1. row ***************************
       Table: FLOAT_NUM
Create Table: CREATE TABLE `FLOAT_NUM` (
  `F` float(5,2) DEFAULT NULL,
  `D` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

MariaDB [MYISAM_TEST]> INSERT INTO FLOAT_NUM VALUE (2122324.3, 2.322342);
MariaDB [MYISAM_TEST]> INSERT INTO FLOAT_NUM VALUE (2.3432654, 23545.42);

MariaDB [MYISAM_TEST]> SELECT * FROM FLOAT_NUM;
+--------+--------+
| F      | D      |
+--------+--------+
| 999.99 |   2.32 |
|   2.34 | 999.99 |
+--------+--------+

即使精度足够保存的数值，浮点数也还是可能会产生误差（计算、保存等）。

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