MySQL必知必会 02丨字段：这么多字段类型，该怎么定义？

Channing Hsu
  2023-07-18 20:29:54 2023-07-18 20:29:54 创建   2024-12-03 01:50:49 2024-12-03 01:50:49 更新      1.7k 字  6 分钟  

整数类型

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和BIGINT，它们的区别如下表所示：

类型 占用字节 使用场景
TINYINT 1 一般用于枚举数据，比如系统设定等取值范围很小且固定的场景
SMALLINT 2 可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等
MEDIUMINT 3 用于较大整数的计算比如车站每日的客流量等
INT（INTEGER） 4 取值范围足够大，一般情况下不用考虑超限问题，用得最多
BIGINT 8 只有当你处理特别巨的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等

浮点数类型和定点数类型

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

  • FLOAT 表示单精度浮点数，占用字节数少，取值范围小
  • DOUBLE 表示双精度浮点数，占用字节数多，取值范围也大。
  • REAL 默认就是 DOUBLE。

若要MySQL 认为 REAL 是 FLOAT，就可以通过SET sql_mode = "REAL_AS_FLOAT";实现

问题：浮点数类型不精准

假设用求和函数求和，期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1，可结果是 1.0999999999999999。尝试把数据类型改成 FLOAT，然后运行求和查询，得到的是1.0999999940395355。显然，误差更大了。

问题出在 MySQL 对浮点类型数据的存储方式上。

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

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

MySQL 用 DECIMAL（M,D）的方式表示高精度小数。其中，M 表示整数部分加小数部分，一共有多少位，M<=65。D 表示小数部分位数，D<M。

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

  • 浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）；

  • 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）。

文本类型

在实际的项目中，我们还经常遇到一种数据，就是字符串数据。有以下文本类型：

CHAR(M)：固定长度字符串。必须预先定义字符串长度。如果太短，数据可能会超出范围；如果太长，又浪费存储空间。

VARCHAR(M)： 可变长度字符串。也需要预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的。

TEXT：字符串。系统自动按照实际长度存储，不需要预先定义长度。

ENUM： 枚举类型，取值必须是预先设定的一组字符串值范围之内的一个，必须要知道字符串所有可能的取值。

SET：是一个字符串对象，取值必须是在预先设定的字符串值范围之内的 0 个或多个，也必须知道字符串所有可能的取值。

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

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

  • TINYTEXT：255 字符（这里假设字符是 ASCII 码，一个字符占用一个字节，下同）。

  • TEXT： 65535 字符。

  • MEDIUMTEXT：16777215 字符。

  • LONGTEXT： 4294967295 字符（相当于 4GB）。

日期与时间格式

用得最多的日期时间类型，就是 DATETIME。虽然 MySQL 也支持 YEAR（年）、TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，但尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，就会很不容易记，而且查询的时候，SQL 语句也会更加复杂。

类型 日期格式 范围 占用字节
YEAR YYYY 1901~2155 1
TIME HH:MM:SS 838:59:59~838:59:59 3
DATE YYYY-MM-DD 1000-01-01~9999-12-3 3
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~9999-12-31 23:59:59 8
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC~2038-01-1903:14:07 UTC 4

为了确保数据的完整性和系统的稳定性，优先考虑使用DATETIME 类型。因为虽然 DATETIME 类型占用的存储空间最多，但是它表达的时间最为完整，取值范围也最大。

为什么时间类型 TIME 的取值范围不是 -23:59:59～23:59:59呢？

原因是 MySQL 设计的 TIME 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。

总结

今天，介绍了几个常用的字段数据类型，包括整数类型、浮点数类型、定点数类型、文本类型和日期时间类型。同时，我们还清楚了为什么整数类型用得少，浮点数为什么不精准，以及常用的日期时间类型。另外，还学习了几个新的 SQL 语句。尤其是第 2 条，我们在项目中会经常用到，你一定要重点牢记。

1
2
3
4
5
6
7
-- 修改字段类型语句
ALTER TABLE demo.goodmaster
MODIFY COLUMN price DOUBLE;

-- 计算字段合计函数
SELECT SUM(price)
FROM demo.goodmaster;

最后，我还想再给你分享 1 个小技巧。

在定义数据类型时，

  • 整数： INT；
  • 小数：定点数类型 DECIMAL；
  • 字符串：只要不是主键，用 TEXT；
  • 日期与时间：DATETIME。

这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

评论
目录
MySQL必知必会 02丨字段：这么多字段类型，该怎么定义？
  1. 整数类型
  2. 浮点数类型和定点数类型
  3. 文本类型
  4. 日期与时间格式
  5. 总结