MySQL 数值类型

整数类型

MySQL 整数类型如下表所示。

使用整数类型需要注意该类型支持的使用范围，防止出现越界情况，比如我们在使用自增主键的时候，一般推荐使用 BIGINT 类型。

• 存储空间：存储需要占用多少字节（占用字节 bytes）。
• M：表示最大显示宽度，建表若设置 zerofill（0 填充），会在数字前面补充 0，若没有设置 0 填充，那么直接显示，不会影响存储空间大小。

  浮点类型

MySQL 浮点类型如下表所示。

• 注意：财务系统必须使用 DECIMAL。
• FLOAT [(M,D)] [UNSIGNED] [ZEROFILL]
  • 单精度浮点数，有符号值的范围是（-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），这些是基于 IEEE 标准的理论限制，实际范围可能会略小，具体取决于您的硬件或操作系统；
  • M 是总位数，D 是小数点后的位数，如果省略 M 和 D，则将值存储到硬件允许的极限，单精度浮点数的精度约为小数点后 7 位；
  • FLOAT (M, D) 是非标准的 MySQL 扩展。从 MySQL 8.0.17 开始，该语法已被弃用，您应该期望在将来的 MySQL 版本中删除对该语法的支持；
  • 如果指定了 UNSIGNED，则不允许使用负值。从 MySQL 8.0.17 开始，对于 FLOAT 类型的列，UNSIGNED 属性已被弃用，您应该期望在将来的 MySQL 版本中删除对它的支持；
  • 使用 FLOAT 可能会给您带来一些意想不到的问题，因为 MySQL 中的所有计算都是以双精度进行的。
• DOUBLE [(M,D)] [UNSIGNED] [ZEROFILL]
  • 双精度浮点数，有符号值的范围是（-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），这些是基于 IEEE 标准的理论限制，实际范围可能会略小，具体取决于您的硬件或操作系统；
  • M 是总位数，D 是小数点后的位数，如果省略 M 和 D，则将值存储到硬件允许的极限，双精度浮点数的精度大约为 15 个小数位；
  • DOUBLE (M, D) 是一个非标准的 MySQL 扩展。从 MySQL 8.0.17 开始，该语法已被弃用，您应该期望在将来的 MySQL 版本中删除对该语法的支持；
  • 如果指定了 UNSIGNED，则不允许使用负值。从 MySQL 8.0.17 开始，对于 DOUBLE 类型的列，UNSIGNED 属性已被弃用，您应该期望在将来的 MySQL 版本中删除对它的支持。
• DECIMAL [(M[,D])] [UNSIGNED] [ZEROFILL]
  • 一个包装好的 “精确 “定点数字。
  • M 是总的位数（精度），D 是小数点后的位数（比例）。小数点和负号不计入 M 中。如果 D 为 0，数值没有小数点或小数部分。DECIMAL 的最大位数（M）是 65。支持的小数（D）的最大位数为30。如果省略 D，则默认为 0，省略 M，则默认为 10。（DECIMAL 字符的文本长度也有限制，参考： Section 12.25.3, “Expression Handling”）；
  • 如果指定了 UNSIGNED，则不允许使用负值。从 MySQL 8.0.17 开始，UNSIGNED 属性对于 DECIMAL （和任何同义词）类型的列来说是不适用的，你应该期望在未来的 MySQL 版本中删除对它的支持；
  • 所有带 DECIMAL 列的基本计算 (+, -, *, /) 都是以 65 位数的精度来完成的。

    特性描述

    SIGNED & UNSIGNED

整数类型分为有符号（signed）和无符号（unsigned）类型，在创建表结构的时候可以指定。需要注意，有符号和无符号的整数类型，它的范围是不一样的。

mysql> create table t1(a int unsigned, b int unsigned);
Query OK, 0 rows affected (0.14 sec)
mysql> insert into t1 values(1, 2);
Query OK, 1 row affected (0.03 sec)

字段设置为无符号的，需要注意，执行如下 SQL ，会出现越界的情况。一般情况下使用 INT 时，推荐有符号数，使用无符号数只是比原来多一倍的取值，数量级上没有改变。

mysql> select a - b from t1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`burn_test`.`test_unsigned`.`a` - `burn_test`.`test_unsigned`.`b`)'

我们新建了一个测试表，两个字段均为 unsigned int 型，插入两个值，然后做减法运算时报错，并不是我们想要的结果，正常情况 1-2=-1，但是当数据类型为 unsigned 时，MySQL 运算的结果也是 unsigned，而 -1 已经超出了 unsigned 范围，所以会报错。

如何解决这个问题呢，设置一下 sql_mode 就可以解决这个问题：

mysql> set sql_mode = 'no_unsigned_subtraction'; -- 这样就可以得到负数
Query OK, 0 rows affected (0.00 sec)
mysql> select a - b from t1;
+-------+
| a - b |
+-------+
|    -1 |
+-------+
1 row in set (0.00 sec)

sql_mode 的设置是有风险的，因为可以通过这个设置允许一些非法的操作。如将 null 值插入 not null 字段，将非法的日期 2017-12-32 插入到日期字段，一般在生产环境都都设置为严格模式，所以在不熟悉 MySQL 底层机制或者不了解 unsigned 原理时，慎用它。

INT (M)

INT(M) 中的 M 指的是该字段能输出显示的最大数字长度，不表示数字存储的长度上限，不会影响存储空间大小，M 本身没有意义，需要配合 zerofill 使用才有意义。当存储数字的长度超过 M 时，按照实际存储的数字显示。

比如，创建一个 M 是 3 的列，插入两条数据。

mysql> create table t2(a int(3));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t2 values(1),(111),(1111);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t2;
+------+
| a    |
+------+
|    1 |
|  111 |
| 1111 |
+------+
3 rows in set (0.00 sec)

在创建字段的时候如果指定了 zerofill 表示当存储的数字长度 < M 时，用数字 0 填充左边，直至补满长度 M。

mysql> create table t3(a int(3) zerofill);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t3 values(1),(111),(1111);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t3;
+------+
| a    |
+------+
|  001 |
|  111 |
| 1111 |
+------+
3 rows in set (0.00 sec)

所以一般情况下，我们不需要指定 M 值。

不同的数据类型对长度的处理也不一样：

• 整数类型
  • 这里显示的长度和数据类型的取值范围是没有任何关系的，显示长度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的长度时会由空格填充（前提是指定了 zerofill）；
  • 如果插入了大于显示长度的值，只要该值不超过该类型的取值范围，数值依然可以插入，而且能够显示出来；
  • 如果你不设置宽度，系统将添加默认的宽度 TINYINT (4)、SMALLINT (6)、MEDIUMINT (9)、INT (11)、 BIGINT (20)，这些默认的宽度是跟该类型的取值范围长度相关。
• 字符串类型：对于字符串类型，这个长度才真的用上了。不管是 CHAR 还是 VARCHAR，长度都定义了字符串的最大长度；例如 password varchar(20)，如果你输入了一个 21 个字符的密码，在严格模式下会提示长度越界异常；

• 浮点和日期等数据类型：对数据的宽度没有要求，一般也不设置，默认是 0。

  总结

• 整数类型不需要指定显示宽度 (M)，没有意义；

• 如果要使用浮点类型，建议使用双精度浮点数（DOUBLE）；
• 浮点类型不需要指定显示宽度 (M, D)，没有意义；
• 财务相关的字段必须使用 DECIMAL，其他浮点类型精度不够，容易出现偏差。

详细内容参考 MySQL 8.0 官方文档：Numeric Data Type Syntax

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/sqac50 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。