MySQL 中的数字类型

Myth丶恋晨 2024-04-17 22:23 74阅读 0赞

#  #

[MySQL 中的数字类型][MySQL]

MySQL 中数据类型常用的就三大类：

*  数字类型/numeric types
 *  日期和时间/date and time types
 *  字符类型/string (character and byte) types

另外还包含两个没那么常用的大类：

*  特殊类型/spatial types
 *  JSON

继续之前，先来看一些单位上的约定和概念，

*  **M**：根据具体不同的类型，其表示的意思不一样，见下方关于这个参数的讨论。
 *  **D** 用于定点及浮点数，表示小数点后有多少位。最大可能取值为 30，但不应该超过 M-2。
 *  **fsp** 适用于 [TIME][], [DATETIME][] 及 [TIMESTAMP][DATETIME]。可理解秒后面的小数点位数。它应该是介于 0~6 之间的，0 表示没有小数部分（fractin part）。默认为 0。
 *  `[]` 方括号表示类型中可选的部分。

存储字符串时指定的类型 `VARCHAR(50)` 中可接收一个数字作为长度，其实除了字符串类型，数字类型也是可指定该参数的，比如 `INT(10)`，`BIGINT(20)`。假设后续讨论中这个参数使用字母 **M** 来表示，即上面提到的。该参数被用在不同类型上时，其表示的意思不一样。

*  对于整形，它表示 **展示宽度/display width**。
 *  对于定点数（fixed point）或浮点数（floating point），表示能够存储的总位数，即精度。
 *  对于字符串，表示能够存储的字符串长度。

#### 展示宽度/Display Width ####

那么什么是**展示宽度**。展示宽度这个参数具有迷惑性，它不像 `CHAR(M)` 中有实际意义表示能够存储的字符串长度，在数字类型中，它指数字展示时需要的宽度，是 MySQL 格式化时使用的。即 `INT(5)`，`INT(15)`，`INT(25)` 能够存储的数字范围都是 `INT` 类型的范围 -2147483648 ~ 2147483647。如果指定了 `ZEROFILL`，MySQL 在返回该数字时，对于实际位数小于展示宽度的数字，将自动在左边补零。比如列的类型为 `INT(5)`，实际存储了数字 5，返回时会得到 `00005`。对于没有指定 `ZEROFILL` 或实际存储的位数大于指定的展示宽度，则不会自动补零，此时看上去没有任何效果。

CREATE TABLE test_zero_fill 
      ( 
         with_fill    INT(5) UNSIGNED ZEROFILL NOT NULL PRIMARY KEY, 
         without_fill INT(5) UNSIGNED NOT NULL 
      );

mysql> INSERT INTO test_zero_fill (with_fill, without_fill) VALUES (5, 5),(123456, 123456);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0

mysql> select \* from test\_zero\_fill;  
±----------±-------------+  
| with\_fill | without\_fill |  
±----------±-------------+  
| 00005 | 5 |  
| 123456 | 123456 |  
±----------±-------------+  
2 rows in set (0.00 sec)

另外，如果使用了 `ZEROFILL`，该列将自动设置为 `UNSIGNED` 类型。

所以对于数据存储层面来说，展示宽度其实没什么用途。如果真的需要格式化，程序中能够请求 MySQL 的 meta 信息以获取到相应的展示宽度。

假如在 Node.js 中使用 [mysqljs/mysql][mysqljs_mysql] 作为数据库连接的模块，在执行请求时，其回调中返回的 `fields` 入参便包含了列相应的 meta 信息。

connection.query("SELECT * from test_zero_fill", function(
        error,
        results,
        fields
    ) {
        if (error) throw error;
        console.log(fields);
    });

fields 中包含列的 meta 信息

FieldPacket {
        catalog: 'def',
        db: 'data_type',
        table: 'test_zero_fill',
        orgTable: 'test_zero_fill',
        name: 'with_fill',
        orgName: 'with_fill',
        charsetNr: 63,
        length: 5,
        type: 3,
        flags: 20579,
        decimals: 0,
        default: undefined,
        zeroFill: true,
        protocol41: true
      },
      FieldPacket {
        catalog: 'def',
        db: 'data_type',
        table: 'test_zero_fill',
        orgTable: 'test_zero_fill',
        name: 'without_fill',
        orgName: 'without_fill',
        charsetNr: 63,
        length: 5,
        type: 3,
        flags: 4129,
        decimals: 0,
        default: undefined,
        zeroFill: false,
        protocol41: true
      },
      FieldPacket {
        catalog: 'def',
        db: 'data_type',
        table: 'test_zero_fill',
        orgTable: 'test_zero_fill',
        name: 'signed_num',
        orgName: 'signed_num',
        charsetNr: 63,
        length: 5,
        type: 3,
        flags: 4193,
        decimals: 0,
        default: undefined,
        zeroFill: true,
        protocol41: true
      }
    ]

因此，在设计表时，应该关注使用哪种具体的数据类型能够满足数据存储的需要，而不要被展示宽度所迷惑。

## 数字类型 ##

数字类型分为有符号 `SIGNED` 和无符号 `UNSIGNED` 的情况，有符号即最前面有一位符呈位，可表示正负数。默认情况下为 `SIGNED` 即有符号。

### 整型 ###

MySQL 中支持标准的 SQL 整型，

*  INTEGER (INT)
 *  SMALLINT

并且扩展了一些类型：

*  TINYINT
 *  MEDIUMINT
 *  BIGINT

以下是 MySQL 中支持的整型，及其对应所需存储空间和取值范围。

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>空间 (字节)</th> 
   <th>有符号时最小取值</th> 
   <th>无符号时最小取值</th> 
   <th>有符号时最大取值</th> 
   <th>无符号时最大取值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td>1</td> 
   <td>-128</td> 
   <td>0</td> 
   <td>127</td> 
   <td>255</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>2</td> 
   <td>-32768</td> 
   <td>0</td> 
   <td>32767</td> 
   <td>65535</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>3</td> 
   <td>-8388608</td> 
   <td>0</td> 
   <td>8388607</td> 
   <td>16777215</td> 
  </tr> 
  <tr> 
   <td>INT</td> 
   <td>4</td> 
   <td>-2147483648</td> 
   <td>0</td> 
   <td>2147483647</td> 
   <td>4294967295</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>8</td> 
   <td>-2<sup>63</sup></td> 
   <td>0</td> 
   <td>2<sup>63</sup>-1</td> 
   <td>2<sup>64</sup>-1</td> 
  </tr> 
 </tbody> 
</table>

具体到每种类型：

*  [TINYINT\[(M)\] \[UNSIGNED\] \[ZEROFILL\]][TINYINT_M_ _UNSIGNED_ _ZEROFILL]：微整型，取值范围 -128 ~ 127，无符号情况下为 0 ~ 255。
 *  [BOOL, BOOLEAN][TINYINT_M_ _UNSIGNED_ _ZEROFILL]：效果等同 `TINYINT(1)`，0 表示 FALSE，其他非 0 值处理成 TRUE。其中关键字 `TRUE`，`FALSE` 真实代表的是数字 1 和 0。

mysql> SELECT IF(0, 'true', 'false');
    +------------------------+
    | IF(0, 'true', 'false') |
    +------------------------+
    | false                  |
    +------------------------+

mysql> SELECT IF(1, ‘true’, ‘false’);  
±-----------------------+  
| IF(1, ‘true’, ‘false’) |  
±-----------------------+  
| true |  
±-----------------------+

mysql> SELECT IF(2, ‘true’, ‘false’);  
±-----------------------+  
| IF(2, ‘true’, ‘false’) |  
±-----------------------+  
| true |  
±-----------------------+

mysql> SELECT IF(0 = FALSE, 'true', 'false');
    +--------------------------------+
    | IF(0 = FALSE, 'true', 'false') |
    +--------------------------------+
    | true                           |
    +--------------------------------+

mysql> SELECT IF(1 = TRUE, ‘true’, ‘false’);  
±------------------------------+  
| IF(1 = TRUE, ‘true’, ‘false’) |  
±------------------------------+  
| true |  
±------------------------------+

mysql> SELECT IF(2 = TRUE, ‘true’, ‘false’);  
±------------------------------+  
| IF(2 = TRUE, ‘true’, ‘false’) |  
±------------------------------+  
| false |  
±------------------------------+

mysql> SELECT IF(2 = FALSE, ‘true’, ‘false’);  
±-------------------------------+  
| IF(2 = FALSE, ‘true’, ‘false’) |  
±-------------------------------+  
| false |  
±-------------------------------+

*  [SMALLINT\[(M)\] \[UNSIGNED\] \[ZEROFILL\]][TINYINT_M_ _UNSIGNED_ _ZEROFILL]：小整型。取值范围 -32768 ~ 32767，无符号情况下为 0 ~ 65535。
 *  [MEDIUMINT\[(M)\] \[UNSIGNED\] \[ZEROFILL\]][TINYINT_M_ _UNSIGNED_ _ZEROFILL]：中整型。取值范围 -8388608 ~ 8388607，无符号情况下为 0 ~ 16777215。
 *  [INT\[(M)\] \[UNSIGNED\] \[ZEROFILL\]][TINYINT_M_ _UNSIGNED_ _ZEROFILL]: 整型。取值范围 -2147483648 ~ 2147483647，无符号情况下为 -2147483648 ~ 2147483647。
 *  [INTEGER\[(M)\] \[UNSIGNED\] \[ZEROFILL\]][TINYINT_M_ _UNSIGNED_ _ZEROFILL]：同 `INT`。
 *  [BIGINT\[(M)\] \[UNSIGNED\] \[ZEROFILL\]][TINYINT_M_ _UNSIGNED_ _ZEROFILL]：大整型。取值范围 -9223372036854775808 ~ 9223372036854775807，无符号情况下为 0 ~ 18446744073709551615。

关于大整型，关键字 `SERIAL` 等同于 `BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE`。

还记得创建表时一般需要指定一个自增的整形 ID 字段么，

CREATE TABLE table_name (id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT)

`SERIAL` 关键字其实是 `BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE` 的别名，所以下次创建表时可直接使用该关键字，会省事很多。

CREATE TABLE table_name (id SERIAL PRIMARY KEY)

如果你不想要 BIGINT，`SERIAL DEFAULT VALUE` 是 `NOT NULL AUTO_INCREMENT UNIQUE` 的别名，那么可以这样来简写 ID 字段：

CREATE TABLE table_name (id INT SERIAL DEFAULT VALUE PRIMARY KEY)

### 定点型 ###

[DECIMAL\[(M\[,D\])\] \[UNSIGNED\] \[ZEROFILL\]][DECIMAL_M_D_ _UNSIGNED_ _ZEROFILL] 定点型数字，其中 M 表示总的位数（不包含正负号及小数点），D 表示小数位数。D 为 0 则表示没有小数部分。M 最大取值 65，默认 10；D 最大支持到 30，默认 0。所有的算术运算（`+`，`-`，`*`，`/`）都基于 65 位的 DECIMAL。

[DEC\[(M\[,D\])\] \[UNSIGNED\] \[ZEROFILL\]][DECIMAL_M_D_ _UNSIGNED_ _ZEROFILL], [NUMERIC\[(M\[,D\])\] \[UNSIGNED\] \[ZEROFILL\]][DECIMAL_M_D_ _UNSIGNED_ _ZEROFILL], [FIXED\[(M\[,D\])\] \[UNSIGNED\] \[ZEROFILL\]][DECIMAL_M_D_ _UNSIGNED_ _ZEROFILL] 同 `DECIMAL`。

定点型数字存储精确的数字，用于准确性要求高的场合，比如涉及**金钱**。底层实现上，MySQL 使用二进制形式存储该类型的值。

通常的用法如下：

salary DECIMAL(5,2)

上面示例中，salary 为一个 5 位精度两位小数的定点型。取值范围 -999.99 ~ 999.99。

因为 D 缺省时默认为 0，所以 `DECIMAL(M)` 表示 `DECIMAL(M,0)`，现时，MySQL 中，M 缺省时默认为 10，所以 `DECIMAL` 表示 `DECIMAL(10,0)`。

当实际存储的值其小数大于指定的位数时，其精度会自动转换成所存储的值的精度。

### 浮点型 ###

区别于 DECIMAL，浮点型存储的数字是个近似值。内部存储时，MySQL 为单精度使用 4 字节（bytes），双精度使用 8 字节。

浮点型包含以下这些类型：

*  [FLOAT\[(M,D)\] \[UNSIGNED\] \[ZEROFILL\]][FLOAT_M_D_ _UNSIGNED_ _ZEROFILL]：小型的单精度浮点型。根据 IEEE 标准理论取值范围 -3.402823466E+38 ~ -1.175494351E-38, 0, 1.175494351E-38 ~ 3.402823466E+38，实际的取值范围因硬件和操作系统而异，会比理论值要小。
    
     *  M 表示总位数，D 表示小数位数。两者省略的情况下，其值为硬件允许的最大值。比如 `FLOAT(7,4)` 看起来会是这个样子： `-999.9999`。
     *  `FLOAT[(M,D)` 这种形式的类型不是标准的 SQL 类型，后续会废弃掉。
 *  [FLOAT(p) \[UNSIGNED\] \[ZEROFILL\]][FLOAT_M_D_ _UNSIGNED_ _ZEROFILL]：是标准的 SQL 类型，p 表示精度。但 MySQL 中，根据 p 取值的不同，底层实际将其处理成别的类型。比如 0 ~ 24 时，当成 4 字节单精度 FLOAT 类型来处理，25 ~ 53 时处理成 8 字节双精度的 DOUBLE 类型。
 *  [DOUBLE\[(M,D)\] \[UNSIGNED\] \[ZEROFILL\]][FLOAT_M_D_ _UNSIGNED_ _ZEROFILL]：双精度浮点型。取值范围 -1.7976931348623157E+308 ~ -2.2250738585072014E-308, 0, 2.2250738585072014E-308 ~ 1.7976931348623157E+308。同 `FLOAT(M,D)`，`DOUBLE(M,D)` 这种形式的双精度类型也是非标准 SQL 类型，后续会废弃。
 *  [DOUBLE PRECISION\[(M,D)\] \[UNSIGNED\] \[ZEROFILL\]][FLOAT_M_D_ _UNSIGNED_ _ZEROFILL], [REAL\[(M,D)\] \[UNSIGNED\] \[ZEROFILL\]][FLOAT_M_D_ _UNSIGNED_ _ZEROFILL]：DOUBLE 的别名。

所以实际使用时，为了最大限度的兼容性，直接使用 `FLOAT`，`DOUBLE`，`PRECISION` 而不要指定精度及小数。

### BIT 类型 ###

[BIT\[(M)\]][BIT_M] 类型用于存储单个状态值，M 表示包含几位。默认为1，最大可取 64。

该类型的值可通过 `b'value'` 的形式书写，其中 value 部分以二进制的形式呈现，比如 b'111' 和 b'10000000' 分别表示 7 和 128。更加详细的信息可参考 [9.1.5 Bit-Value Literals][]。

如果赋值到该类型上的值小于 M 指定的位数，值左边会补零，比如将 b'101' 存储到类型为 BIT(6) 的列，实际会是 b'000101'。

## 存储的值溢出的情况 ##

将要存储的值超出数字类型的范围时，其表现跟当前设置的 SQL 模式有关。具体来说，

*  开启 SQL 严格模式时，超出范围的值会写入失败，MySQL 会中断操作并且直接抛错。
 *  非严格模式下，MySQL 会将值裁剪到合适的大小进行写入。即超出的情况下存成该类型能够接收的最大值。

考察一个通过如下语句创建的表 `t1`：

CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED);

SQL 严格模式下，尝试写入一个超出范围的值时抛错：

mysql> SET sql_mode = 'TRADITIONAL';
    mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
    ERROR 1264 (22003): Out of range value for column 'i1' at row 1
    mysql> SELECT * FROM t1;
    Empty set (0.00 sec)

以下是非严格模式下进行裁剪存储的情况：

mysql> SET sql_mode = '';
    mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
    mysql> SHOW WARNINGS;
    +---------+------+---------------------------------------------+
    | Level   | Code | Message                                     |
    +---------+------+---------------------------------------------+
    | Warning | 1264 | Out of range value for column 'i1' at row 1 |
    | Warning | 1264 | Out of range value for column 'i2' at row 1 |
    +---------+------+---------------------------------------------+
    mysql> SELECT * FROM t1;
    +------+------+
    | i1   | i2   |
    +------+------+
    |  127 |  255 |
    +------+------+

上述表现同样会出现在涉及到对列进行转换修改的一些操作上，比如 `ALTER TABLE`，`LOAD DATA`，`UPDATE` 以及使用 `INSERT` 同时插入多行数据时。严格模式下会抛错失败，非严格模式下值会进行裁剪。但失败的情况不尽相同，如果是事务类型的表，会整个全失败，其他情况根据具体的值会部分成功，部分失败。

进行数字计算时如果有溢出，也会抛错，比如对于 BIGINT 其最大值为 9223372036854775807，因为 MySQL 中默认对数字类型是有符号类型，如下操作会抛错，

mysql> SELECT 9223372036854775807 + 1;
    ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

对于上述情况，可显式将 被操作数进行类型转换，转成无符号的 BIGINT：

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
    +-------------------------------------------+
    | CAST(9223372036854775807 AS UNSIGNED) + 1 |
    +-------------------------------------------+
    |                       9223372036854775808 |
    +-------------------------------------------+

通过带上小数后，转成 DECIMAL 也能修正上面的错误，因为 DECIMAL 比整形要大，

mysql> SELECT 9223372036854775807.0 + 1;
    +---------------------------+
    | 9223372036854775807.0 + 1 |
    +---------------------------+
    |     9223372036854775808.0 |
    +---------------------------+

两数相减时，其中一个为无符号数，得出的结果默认为也为无符号。所以如果想减之后结果是负数，则会抛错。

mysql> SET sql_mode = '';
    Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;  
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ‘(cast(0 as unsigned) - 1)’

除非开启了 [`NO_UNSIGNED_SUBTRACTION`][NO_UNSIGNED_SUBTRACTION]：

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
    mysql> SELECT CAST(0 AS UNSIGNED) - 1;
    +-------------------------+
    | CAST(0 AS UNSIGNED) - 1 |
    +-------------------------+
    |                      -1 |
    +-------------------------+

## 总结 ##

对于整型或浮点型，可指定 `AUTO_INCREMTN` 属性。指定该属性性，将不能接收负值。同时 `CHECK` 属性与该属性冲突，也不能同时使用。但对于 FLOAT 和 DOUBLE，`AUTO_INCREMENT` 属性的支持将逐渐废弃掉，实际使用时尽量避免。

对于需要精确数值的场合，使用 DECIMAL，比如涉及金钱的情况。

对于整形，展示宽度不是其存储的值范围，只用来格式化。

[MySQL]: https://www.cnblogs.com/Wayou/p/mysql_data_type_number.html
[TIME]: https://dev.mysql.com/doc/refman/8.0/en/time.html
[DATETIME]: https://dev.mysql.com/doc/refman/8.0/en/datetime.html
[mysqljs_mysql]: https://github.com/mysqljs/mysql
[TINYINT_M_ _UNSIGNED_ _ZEROFILL]: https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
[DECIMAL_M_D_ _UNSIGNED_ _ZEROFILL]: https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html
[FLOAT_M_D_ _UNSIGNED_ _ZEROFILL]: https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html
[BIT_M]: https://dev.mysql.com/doc/refman/8.0/en/bit-type.html
[9.1.5 Bit-Value Literals]: https://dev.mysql.com/doc/refman/8.0/en/bit-value-literals.html
[NO_UNSIGNED_SUBTRACTION]: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_unsigned_subtraction