MySQL必知必会02：字段类型选择

阅读整理自《MySQL 必知必会》- 朱晓峰，详细内容请登录极客时间官网购买专栏。

MySQL 中有很多字段类型，比如整数、文本、浮点数，等等。如果类型定义合理，就能节省存储空间，提升数据查询和处理的速度，相反，如果数据类型定义不合理，就有可能会导致数据超出取值范围，引发系统报错，甚至可能会出现计算错误的情况，进而影响到整个系统。

整型

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT
在这里插入图片描述
在评估用哪种整数类型的时候，需要考虑存储空间和可靠性的平衡问题：

用占用字节数少的整数类型可以节省存储空间；
要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性。

在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。

浮点数

MySQL 支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

FLOAT 表示单精度浮点数；
DOUBLE 表示双精度浮点数；
REAL 默认就是 DOUBLE。如果把 SQL 模式设定为启用 REAL_AS_FLOAT，那么，MySQL 就认为 REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”，就可以通过以下 SQL 语句实现：
```
SET sql_mode = "REAL_AS_FLOAT";
```

LOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分。

浮点数类型有个缺陷，就是不精准。因此，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。

show databases;
use demo;
create table demo.goodsmaster
(
barcode text,
goodname text,
price double,
itemnumber int primary key auto_increment
);

mysql> describe goodsmaster;
+------------+--------+------+-----+---------+----------------+
| Field      | Type   | Null | Key | Default | Extra          |
+------------+--------+------+-----+---------+----------------+
| barcode    | text   | YES  |     | NULL    |                |
| goodname   | text   | YES  |     | NULL    |                |
| price      | double | YES  |     | NULL    |                |
| itemnumber | int    | NO   | PRI | NULL    | auto_increment |
+------------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

insert into demo.goodsmaster (barcode, goodname, price) values ('001', 'book', 0.47);
insert into demo.goodsmaster (barcode, goodname, price) values ('002', 'pen', 0.44);
insert into demo.goodsmaster (barcode, goodname, price) values ('003', 'rubber', 0.19);

mysql> select * from goodsmaster;
+---------+----------+-------+------------+
| barcode | goodname | price | itemnumber |
+---------+----------+-------+------------+
| 001     | book     |  0.47 |          1 |
| 002     | pen      |  0.44 |          2 |
| 003     | rubber   |  0.19 |          3 |
+---------+----------+-------+------------+
3 rows in set (0.00 sec)

mysql> select sum(price) from goodsmaster;
+--------------------+
| sum(price)         |
+--------------------+
| 1.0999999999999999 |
+--------------------+
1 row in set (0.00 sec)

会存在这样的误差，问题还是出在 MySQL 对浮点类型数据的存储方式上。

MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据。无论哪个，都是采用二进制的方式来进行存储的。比如 9.625，用二进制来表达，就是 1001.101，或者表达成 1.001101×2^3。如果尾数不是 0 或 5（比如 9.624），就无法用一个二进制数来精确表达。怎么办呢？就只好在取值允许的范围内进行近似（四舍五入）。（十进制小数转化为二进制小数）

数据类型是 DOUBLE 的时候，得到的结果误差更小一些，而数据类型是 FLOAT 的时候，误差会更大一下。原因就是，DOUBLE 有 8 位字节，精度更高。

定点数

定点数 DECIMAL 的存储方式决定了它一定是精准的。

浮点数类型是把十进制数转换成二进制数存储，DECIMAL 则不同，它是把十进制数的整数部分和小数部分拆开，分别转换成十六进制数，进行存储。这样，所有的数值，就都可以精准表达了，不会存在因为无法表达而损失精度的问题。

mysql> alter table demo.goodsmaster modify column price decimal(5,2);
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select sum(price) from demo.goodsmaster;
+------------+
| sum(price) |
+------------+
|       1.10 |
+------------+
1 row in set (0.00 sec)

由于 DECIMAL 数据类型的精准性，在项目中，除了极少数（比如商品编号）用到整数类型外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能差。

小结下浮点数和定点数的特点：

浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）；
定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）。

文本

TEXT 类型是 MySQL 支持的文本类型的一种。此外，MySQL 还支持 CHAR、VARCHAR、ENUM 和 SET 等文本类型。

CHAR(M)：固定长度字符串。CHAR(M) 类型必须预先定义字符串长度。如果太短，数据可能会超出范围；如果太长，又浪费存储空间
VARCHAR(M)：可变长度字符串。VARCHAR(M) 也需要预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的
TEXT：字符串。系统自动按照实际长度存储，不需要预先定义长度
ENUM：枚举类型，取值必须是预先设定的一组字符串值范围之内的一个，必须要知道字符串所有可能的取值
SET：是一个字符串对象，取值必须是在预先设定的字符串值范围之内的 0 个或多个，也必须知道字符串所有可能的取值

因为不需要预先知道字符串的长度，系统会按照实际的数据长度进行存储，所以 TEXT 类型最为灵活方便。

TEXT 类型也有 4 种，它们的区别就是最大长度不同。

TINYTEXT：255 字符（这里假设字符是 ASCII 码，一个字符占用一个字节，下同）
TEXT： 65535 字符
MEDIUMTEXT：16777215 字符
LONGTEXT： 4294967295 字符（相当于 4GB）

需要注意的是，TEXT 也有一个问题：由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，只能采用 CHAR(M)，或者 VARCHAR(M)。在项目中，只要不是主键字段，就可以按照数据可能的最大长度，选择这几种 TEXT 类型中的的一种，作为存储字符串的数据类型。

日期与时间

用得最多的日期时间类型，就是 DATETIME。虽然 MySQL 也支持 YEAR（年）、TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，不同数据类型表示的时间内容不同、取值范围不同，而且占用的字节数也不一样，要根据实际需要灵活选取。
在这里插入图片描述
在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，可以确保数据的完整性和系统的稳定性，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，就会很不容易记，而且查询的时候，SQL 语句也会更加复杂。

小结

在定义数据类型时，如果确定是整数，就用 INT；如果是小数，一定用定点数类型 DECIMAL；如果是字符串，只要不是主键，就用 TEXT；如果是日期与时间，就用 DATETIME。

凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

进一步优化的需求，可参考：Data Type。

原文链接：https://blog.csdn.net/qq_31362767/article/details/123162627