MySQL 数据类型

环境说明

MySQL8.0.16

建表时如何选择合适的数据类型，通常选择合适的数据类型能够提高性能、减少不必要的麻烦。

CHAR 和 VARCHAR 的选择

char 和 varchar 是经常要用到的两个存储字符串的数据类型，char 一般存储定长的字符串，它属于固定长度的字符类型，比如下面

可以看到，不管值是什么，一旦指定了 char 字符的长度，如果字符串长度不够指定字符的长度的话，那么就用空格来填补，如果超过字符串长度的话，只存储指定字符长度的字符。

❝> 这里注意一点：如果 MySQL 使用了非 严格模式的话，上面表格最后一行是可以存储的。如果 MySQL 使用了 严格模式 的话，那么表格上面最后一行存储会报错。 ❞ 如果使用了 varchar 字符类型，看一下例子

可以看到，如果使用 varchar 的话，那么存储的字节将根据实际的值进行存储。可能会疑惑为什么 varchar 的长度是 5 ，但是却需要存储 3 个字节或者 6 个字节，这是因为使用 varchar 数据类型进行存储时，默认会在最后增加一个字符串长度，占用1个字节（如果列声明的长度超过255，则使用两个字节）。varchar 不会填充空余的字符串。
一般使用 char 来存储定长的字符串，比如「身份证号、手机号、邮箱等」；使用 varchar 来存储不定长的字符串。由于 char 长度是固定的，所以它的处理速度要比 VARCHAR 快很多，但是缺点是浪费存储空间，但是随着 MySQL 版本的不断演进，varchar 数据类型的性能也在不断改进和提高，所以在许多应用中，VARCHAR 类型更多的被使用。
在 MySQL 中，不同的存储引擎对 CHAR 和 VARCHAR 的使用原则也有不同

• MyISAM：建议使用固定长度的数据列替代可变长度的数据列，也就是 CHAR
• MEMORY：使用固定长度进行处理、CHAR 和 VARCHAR 都会被当作 CHAR 处理

• InnoDB：建议使用 VARCHAR 类型

  TEXT 与 BLOB

  一般在保存较少的文本的时候，会选择 CHAR 和 VARCHAR，在保存大数据量的文本时，往往选择 TEXT 和 BLOB；TEXT 和 BLOB 的主要差别是 BLOB 能够保存二进制数据；而 TEXT 只能保存字符数据，TEXT 往下细分有

• TEXT

• MEDIUMTEXT
• LONGTEXT

BLOB 往下细分有

• BLOB
• MEDIUMBLOB
• LONGBLOB

三种，它们最主要的区别就是存储文本长度不同和存储字节不同，用户应该根据实际情况选择满足需求的最小存储类型，下面主要对 BLOB 和 TEXT 存在一些问题进行介绍
TEXT 和 BLOB 在删除数据后会存在一些性能上的问题，为了提高性能，建议使用 OPTIMIZE TABLE 功能对表进行碎片整理。
也可以使用合成索引来提高文本字段（BLOB 和 TEXT）的查询性能。合成索引就是根据大文本（BLOB 和 TEXT）字段的内容建立一个散列值，把这个值存在对应列中，这样就能够根据散列值查找到对应的数据行。一般使用散列算法比如 md5() 和 SHA1() ，如果散列算法生成的字符串带有尾部空格，就不要把它们存在 CHAR 和 VARCHAR 中，下面看一下这种使用方式
首先创建一张表，表中记录 blob 字段和 hash 值

mysql> create table blob_test(id varchar(50),info blob, hash varchar(50));
Query OK, 0 rows affected (0.03 sec)

向 blob_test 中插入数据，其中 hash 值作为 info 的散列值。

mysql> insert into blob_test values(111, repeat('fcant', 10), md5(info));
Query OK, 1 row affected (0.01 sec)

然后再插入两条数据

mysql> insert into blob_test values(112, repeat('fcant', 10), md5(info));
Query OK, 1 row affected (0.00 sec)
mysql> insert into blob_test values(113, repeat('fcant', 10), md5(info));
Query OK, 1 row affected (0.00 sec)

插入一条 info 为 fcant005 的数据

mysql> insert into blob_test values(114, repeat('fcant005', 10), md5(info));
Query OK, 1 row affected (0.00 sec)

如果想要查询 info 为 fcant005 的数据，可以通过查询 hash 列来进行查询

mysql> select * from blob_test where hash - md5(repeat('fcant005', 10));
+------+----------------------------------------------------+----------------------------------+
| id   | info                                               | hash                             |
+------+----------------------------------------------------+----------------------------------+
| 111  | fcantfcantfcantfcantfcantfcantfcantfcantfcantfcant | 10da11f2f6689cf8815801711cd182a1 |
| 112  | fcantfcantfcantfcantfcantfcantfcantfcantfcantfcant | 10da11f2f6689cf8815801711cd182a1 |
| 113  | fcantfcantfcantfcantfcantfcantfcantfcantfcantfcant | 10da11f2f6689cf8815801711cd182a1 |
+------+----------------------------------------------------+----------------------------------+
3 rows in set, 4 warnings (0.00 sec)

这是合成索引的例子，如果要对 BLOB 进行模糊查询的话，就要使用前缀索引。
其他优化 BLOB 和 TEXT 的方式：

• 非必要的时候不要检索 BLOB 和 TEXT 索引
• 把 BLOB 或 TEXT 列分离到单独的表中。

  浮点数和定点数的选择

  浮点数指的就是含有小数的值，浮点数插入到指定列中超过指定精度后，浮点数会四舍五入，MySQL 中的浮点数指的就是 float 和 double，定点数指的是 decimal，定点数能够更加精确的保存和显示数据。下面通过一个示例讲解一下浮点数精确性问题
  首先创建一个表 cxuan006 ，只为了测试浮点数问题，所以这里选择的数据类型是 float

  mysql> create table float_test(info float(8, 1));
  Query OK, 0 rows affected (0.03 sec)

  然后分别插入两条数据 ```sql mysql> insert into float_test values(6.64389923); Query OK, 1 row affected (0.01 sec)

mysql> insert into float_test values(6.77677776); Query OK, 1 row affected (0.01 sec)

然后执行查询，可以看到查询出来的两条数据执行的舍入不同
```sql
mysql> select * from float_test;
+------+
| info |
+------+
|  6.6 |
|  6.8 |
+------+
2 rows in set (0.00 sec)

为了清晰的看清楚浮点数与定点数的精度问题，再来看一个例子

mysql> alter table float_test add column message decimal(8, 3);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table float_test modify column info float(8, 3);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

先修改 float_test 的两个字段为相同的长度和小数位数
然后插入两条数据

mysql> insert into float_test values(666666.77677776, 666666.77677776);
ERROR 1264 (22003): Out of range value for column 'info' at row 1
mysql> insert into float_test values(666666.776, 666666.776);
ERROR 1264 (22003): Out of range value for column 'info' at row 1
mysql> insert into float_test values(66666.776, 66666.776);
Query OK, 1 row affected (0.01 sec)

执行查询操作，可以发现，浮点数相较于定点数来说，会产生误差

日期类型选择

在 MySQL 中，用来表示日期类型的有 「DATE、TIME、DATETIME、TIMESTAMP」

• TIMESTAMP 和时区相关，更能反映当前时间，如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。
• DATE 用于表示年月日，如果实际应用值需要保存年月日的话就可以使用 DATE。
• TIME 用于表示时分秒，如果实际应用值需要保存时分秒的话就可以使用 TIME。
• YEAR 用于表示年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。