MySQL的数据类型

MySQL的数据类型

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

先来看看 MySQL 中最简单的数据类型：整数类型。

整数类型

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

类型 有符号数取值范围 无符号取值范围 占用字节数
TINYINT (-128，127) (0，255) 1
SMALLINT (-32 768，32 767) (0，65 535) 2
MEDIUMINT (-8 388 608，8 388 607) (0，16 777 215) 3
INT（INTEGER） (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 4
BIGINT (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 8

其中，无符号的类型可以在类型后面添加 UNSIGNED 作为数据类型，比如想要创建一个 INT 类型的无符号，那么该类型就是 INT UNSIGNED。下面是以 dbeaver 数据库管理工具为例：

在实际工作中如何选择呢？

其实，在评估用哪种整数类型的时候，需要考虑存储空间和可靠性的平衡问题：一方面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性。

不要为了贪图节省存储空间去选择取值范围小的整数类型，在工作中，因为取值范围溢出而造成的系统故障成本远远超过几个字段存储空间所产生的成本。所以首先确保数据不会超过取值范围，在这个前提下，在去考虑如何节省存储空间。

浮点数类型和定点数类型

浮点数和定点数类型的特点是可以处理小数，你可以把整数看成小数的一个特例。因此，浮点数和定点数的使用场景，就比整数大多了。

先来了解下 MySQL 支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

  1. FLOAT 表示单精度浮点数；

  2. DOUBLE 表示双精度浮点数；

  3. REAL 默认就是 DOUBLE。

FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？其实就是，FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。

类型 有符号数取值范围 无符号取值范围 占用字节数
FLOAT (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 4
DOUBLE (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 8

浮点数类型有个缺陷，就是不精准。在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误。那为什么在对精确度要求高的项目中不使用浮点数类型呢？

原因是会丢精度，借助一个实际的例子演示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// 创建表，demo表示数据库，创建一个goodsmaster表，里面的字段有 barcode、goodsname、price以及自增id itemnumber
// barcode代表商品编号，goodsname代表商品名称，price代表售价
CREATE TABLE demo.goodsmaster( barcode TEXT, goodsname TEXT, price DOUBLE, itemnumber INT PRIMARY KEY AUTO_INCREMENT);

// 往表里面插入数据
INSERT INTO bsm.goodsmaster(barcode,goodsname,price) VALUES('0001','书',0.47);
INSERT INTO bsm.goodsmaster(barcode,goodsname,price) VALUES('0002','笔',0.44);
INSERT INTO bsm.goodsmaster(barcode,goodsname,price) VALUES('0003','胶水',0.19);


// 查询表里的内容
barcode|goodsname|price|itemnumber|
-------+---------+-----+----------+
0001   |书        | 0.47|         1|
0002   |笔        | 0.44|         2|
0003   |胶水       | 0.19|         3|

自此准备工作完成，现在将这 3 个商品的价格进行累加然后输出出来，这里使用 SUM 函数来实现。 

1
SELECT SUM(price)  FROM goodsmaster

期待的运行结果是：0.47+0.44+0.19 = 1.0，可我们得到的是：

1
2
3
4
5
SELECT SUM(price)  FROM goodsmaster

SUM(price)        |
------------------+
1.0999999999999999|

查询结果是 1.0999999999999999。虽然误差很小，但确实是有误差。我把字段 price 的类型改为 FLOAT，计算后结果是 1.0999999940395355，误差更大了。

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

摘抄极客时间专栏《MySQL必知必会》

浮点数存储小数点不精确，那用什么精确的数据类型呢？使用 定点数类型：DECIMAL。

就像浮点数类型的存储方式，决定了它不可能精准一样，DECIMAL 的存储方式决定了它一定是精准的。

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

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

使用刚才的例子再次验证：

1
2
3
4
5
6
7
8
// 修改 price 字段的联系为 DECIMAL
ALTER TABLE bsm.goodsmaster MODIFY COLUMN price DECIMAL NULL;

// 再次求和
SELECT SUM(price)  FROM goodsmaster
SUM(price)|
----------+
      1.10|

这次得到了正确结果：1.10

在一些对精度要求不高的场景下，比起占用同样的字节长度的定点数，浮点数表达的数值范围可以更大一些。

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

文本类型

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

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

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

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

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

对于 ENUM 类型和 SET 类型来说，你必须知道所有可能的取值，所以只能用在某些特定场合，比如某个参数设定的取值范围只有几个固定值的场景。

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

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

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

  • TEXT： 65535 字符。

  • MEDIUMTEXT：16777215 字符。

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

不过，需要注意的是，TEXT 也有一个问题：由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用 CHAR(M)，或者 VARCHAR(M)。

所以只要不是主键字段，就可以按照数据可能的最大长度，选择这几种 TEXT 类型中的的一种，作为存储字符串的数据类型。

日期与时间类型

日期与时间是重要的信息，在系统中，几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签，从而进行数据查询、统计和处理。

在阿里巴巴 Java 开发手册中 MySQL 数据库章节中也明确表示，表中必备三字段是 id、create_time、update_time，create_time、update_time的类型是 datetime 类型。

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

下面列举出 MySQL 所支持的日期时间类型参数：

类型 日期格式 范围 占用字节数
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-19 03:14:07 UTC 4

可以看到，不同数据类型表示的时间内容不同、取值范围不同，而且占用的字节数也不一样，要根据实际需要灵活选取。

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

总结

以上就是 MySQL 中常用的字段类型，包括整数类型、浮点数类型、定点数类型、文本类型和日期时间类型，基本上涵盖了工作中所能用到的大部分数据类型。

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

  • 整数：INT。

  • 小数：DECIMAL。

  • 字符串：TEXT。

  • 日期与时间：DATETIME。

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

程序开发
#数据库/MySQL
MySQL的数据类型
https://sunshinechen.cn/2022/05/22/mysql的数据类型/
作者
sunshine
发布于
2022年5月22日
许可协议