MySQL的字段类型，该怎么选？

starine
1,125 阅读8分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路

引言

MySQL 中的字段类型包括整数、文本、浮点数等等。准确地定义字段类型，不但关系到数据存储的效率，而且会影响整个信息系统的可靠性。例如浮点类型是不精确的，你需要使用decimal类型......

所以，我们必须要掌握不同字段的类型，包括它们的适用场景、定义方法。

整数类型

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

类型有符号数取值范围无符号数取值范围占用字节适用场景
TINYINT-128~1270~2551一般用于枚举数据，比如系统设定等，取值范围很小且固定的场景
SMALLINT-32768~327670~655352可用于较小范围的统计数据，比如统计工厂的固定资产库存数量
MEDIUMINT-8388608~83886070~167772153用于较大整数的计算，比如车站每日的客流量
INT (INTEGER)-2147483648~21474836470~42949672954取值范围足够大，一般情况下不用考虑超限问题，用得最多
BIGINT-9223372036854775808~92233720368547758080~184467440737095516158当处理特别大的整数时才会用到，比如双十一的交易量，大型门户网站点击量，证券公司衍生产品持仓等

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

建议首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。

浮点数类型和定点数类型

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

FLOAT 表示单精度浮点数；占4字节

DOUBLE 表示双精度浮点数；占8字节

REAL 默认就是 DOUBLE。如果你把 SQL 模式设定为启用“REAL_AS_FLOAT”，那么，MySQL 就认为 REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”，就可以通过以下 SQL 语句实现

SET sql_mode = “REAL_AS_FLOAT”;

image.png

为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分呢？

其实，这里的原因是，MySQL 是按照这个格式存储浮点数的：符号（S）、尾数（M）和阶码（E）。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此，所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

浮点数类型有个缺陷，就是不精准。

例1：
>>> 1.4 - 1.1
0.2999999999999998
为什么不是0.3？

例2：
4.0 + 1e+16 - 1e+16
>>> 4.0
没错。

5.0 + 1e+16 - 1e+16
>>> 4.0
为什么5变成了4？

4.0 + 1e+17 - 1e+17
>>> 0.0
为什么结果是0.0？ 4去哪了？

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

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

那么，MySQL 有没有精准的数据类型呢？当然有，这就是定点数类型：DECIMAL。

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

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

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

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

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

文本类型

MySQL 支持 TEXT、CHAR、VARCHAR、ENUM 和 SET 等文本类型。

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

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

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

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

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

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

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 语句也会更加复杂。

image.png

为什么时间类型 TIME 的取值范围不是 -23:59:59～23:59:59呢？原因是 MySQL 设计的 TIME 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。

总结

在定义数据类型时，如果确定是整数，就用 INT；

如果是小数，一定用定点数类型 DECIMAL；

如果是字符串，只要不是主键，就用 TEXT；

如果是日期与时间，就用 DATETIME。

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

面试题 2

mysql⾥记录货币⽤什么字段类型⽐较好？

  • 货币在数据库中常⽤Decimal和Numric类型表示，这两种类型被MySQL实现为同样的类型。他们被⽤于保存与⾦钱有关的数据。
  • salary DECIMAL(9,2)，9(precision)代表将被⽤于存储值的总的位数，⽽2(scale)代表将被⽤于存储⼩数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99。
  • DECIMAL和NUMERIC值作为字符串存储，⽽不是作为⼆进制浮点数，以便保存那些值的⼩数精度。
avatar
文章
阅读
粉丝