MySQL 数据库的数值数据类型说明

[TOC]

## 数据类型说明
数据类型是对数据进行统一的分类，使数据库管理系统有统一的方法对数据进行管理，更好的利用磁盘空间，关系型数据的以表结构的方式存储数据，即使这行数据中有字段是空的，但它仍能占用了空间，所以合理的定义数据类型可以尽可能的节省不必要的空间浪费

## MySQL数据类型
在SQL中将数据类型分为了三类，数值类型、字符串类型和时间日期类型

## 数值类型
数值类型包含了`整数类型`和`小数类型`

### 1. 整数型
__说明：__MySQL默认是有符号的分正负，需要使用无符号时，需设置 `unsigned` 属性，表示不允许负值
__语法：__alter table 表 add 字段名 类型 [unsigned]，只要在字段后面加`unsigned`即可，添加修改列都可使用

>__附加数值类型范围计算方式：__1个字节为8位(bit)，以tinyint为例
+ __有符号范围：__8位最大为11111111，用2进制转换成10进制后就是-127，`因为高位是符号位了（1是负，0是正）`，8位最小为01111111，转换为10进制就是127，tinyint的取值范围为什么不是-127到127？这是因为在有符号中-127到-0和+0到+127，-0和+0重合了，而为了避免重复，-0根据正、反、补码体系，采用了补码规定使用-128代表了-0
+ __无符号范围：__8位最大为11111111，换算成10进制后就是255，8位最小为00000000，范围就是0-255

|类型|说明|字节|位|有符号范围|无符号范围|
|:--|:--|:--|:--|:--|:--|
|tinyint|极小整型，表示的状态最多为256种|1|8|-128 ~ 127|0 ~ 255|
|smallint|小整型，表示的状态最多为65536种|2|16|-215 ~ 215-1|0 ~ 216-1|
|mediumint|中整型，表示的状态最多224种|3|24|-223 ~ 223-1|0 ~ 224-1|
|int|标准整型，表示的状态最多232种|4|32|-231 ~ 231-1|0 ~ 232-1|
|bigint|大整型，表示的状态最多264种|8|64|-263 ~ 263-1|0 ~ 264-1|

#### 1.1. 整数型常见的错误
常见的错误有超出范围和数据类型不正确

```SQL
-- 创建一张表来验证数据类型和范围
create table my_int (minint intyint) charset utf8;
-- 超出范围：Out of range value for column 'minint' at row 1
insert into my_int (minint) values(255); -- 255超出范围是因为MySQ默认使用的是有符号范围
-- 数据类型不正确：Incorrect integer value: 'a' for column 'minint' at row 1
insert into my_int (minint) values('a');
```

#### 1.2. 显示宽度
__说明：__从8.0.17开始，整型类型默认不再需要指定显示宽度了，之前的版本使用`desc my_int`查看表结构时，可以看到在Type列（类型列）显示为`tinyint:<4>`，尖括号里面的数字就是这个类型显示的宽度，显示宽度是数据最终显示的位数。如-123是4位显示宽度，显示宽度是包含符号的，255是3位显示宽度。以tinyint类型为例，如果声明了是无符号那么显示宽度默认为3，未声明则显示宽度默认为4

显示宽度可以设置，但它不影响数据存放的范围，数据范围是由数据类型控制的。例如tinyint的显示范围设置为1，仍然是可以将123保存成功的

__意义：__当数据不够显示宽度时，会自动让数据变成对应的显示宽度，在保证不改变值的大小前提下，使用0来补充不够的显示宽度，使用`zerofill`（零填充）进行属性声明，需要注意的是声明了`zerofill`后，字段会自动添加`unsigned`属性变为无符号类型，零填充保证了数据格式的一致
__语法：__alter table 表 add 字段名 类型 [zerofill]，案例如下

+ 给my_int表添加一个零填充的列

```SQL
alter table my_int add column small_int smallint zerofill;
desc my_int;
```
添加零填充的列后的表结构

| Field     | Type                          | Null | Key | Default | Extra |
|-----------|-------------------------------|------|-----|---------|-------|
| minint    | tinyint                       | YES  |     | NULL    |       |
| small_int | smallint(5) unsigned zerofill | YES  |     | NULL    | &nbsp;|

+ 插入记录后查看结果，在small_int字段前添加了4个零

```SQL
insert into my_int values(2,2);
select * from my_int;
```
新增数据后查询结果

| minint | small_int |
|--------|-----------|
|      2 |     00002 |

__使用场景：__需求需要在前端显示同位数的学生编号时，这时可以使用指定显示宽度且设置`zerofill`属性来完成需求，还有时间显示问题，当时间为个位数时，使用零填充的方式就可以保证数据格式一致了

### 2. 小数类型
小数型是指带有小数点或者范围超出整型的数值类型，在MySQL中小数型又分成了浮点型和定点型

__浮点型和定点型的区别：__

+ 浮点型小数点浮动，精度有限，会丢失精度
+ 定点型小数点固定，精度固定，不会丢失精度

#### 2.1. 浮点型
浮点型是一种精度型数据，超出指定范围之后，会丢失精度（自动的四舍五入），如果浮点类型使用(M, D)方式来表示，其中M称为总长度，D为小数的长度，整数部分长度就是M-D，以float为例，直接使用float没有小数部分，使用float(10,2)则表示总长度为10，小数点位数为2，整数部分则为8，float和double类型被保存为硬件所支持的最大精度

##### 2.1.1. 浮点型为有符号范围
浮点型都为有符号范围，因为浮点类型采用的是IEEE标准， IEEE标准未解决无符号值

```SQL
-- 创建一张表用来验证小数类型
create table test_float (float1 float,float2 float(10,2),float3 float(4,2)) charset utf8mb4;
-- 添加有unsigned属性的字段
alter table test_float add column _float4 float(3,2) unsigned;
-- 添加有zerofill属性的字段
alter table test_float add column _float5 float(5,2) zerofill;
```
从`MySQL8.0.17`版之后，执行上面的语句将会提示警告：

+ 第一句添加字段SQL：Warning (code 1681): Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning (code 1681): UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.

+ 第二句添加字段SQL：Warning (code 1681): The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning (code 1681): Specifying number of digits for floating point data types is deprecated and will be removed in a future release.

上面的提示是在告诫不要给float类型声明精度范围、不要使用zerofill和unsigned属性，因为之后的MySQL版本可能会对其进行弃用，所以使用浮点类型时建议不要申明精度和使用unsigned和zerofill属性

|类型|说明|字节|位|取值范围
|:--|:--|:--|:--|:--|
|float|单精度|4|32|负：【-3.402823466E+38 ～ -1.175494351E-38】 正：【0 和 1.175494351E-38 ～ 3.402823466E+38】|
|double|双精度|8|64|负：【-1.7976931348623157E+308 ～ -2.2250738585072014E-308】 正：【0 和 2.2250738585072014E-308 ～ 1.7976931348623157E+308】|

> __附加科学计数法：__把一个数表示成a与10的n次幂相乘的形式，这种形式决定了a的绝对值必须大于等于0且小于10，这种记数法叫做科学记数法。
> 例如：199002230000 = 1.9900223×1011。计算器或电脑表达10的幂是一般是用E或e，也就是1.9900223E11=199002230000`

##### 2.1.2. 浮点型特别说明
__下面以`test_float`表为例__

| Field   | Type                         | Null | Key | Default | Extra |
|---------|------------------------------|------|-----|---------|-------|
| float1  | float                        | YES  |     | NULL    |       |
| float2  | float(10,2)                  | YES  |     | NULL    |       |
| float3  | float(4,2)                   | YES  |     | NULL    |       |
| \_float4 | float(5,2) unsigned zerofill| YES  |     | NULL    | &nbsp;|

+ 浮点型插入数据时可以是小数，也可以是科学计数法
+ 浮点型数据插入整数部分不能超出长度，但小数部分可以，系统会自动进行四舍五入

```SQL
insert into test_float values(1.33,1.9999999999999E+7,99.99,111.11);
```
结果：

| float1 | float2   | float3 | \_float4 |
|--------|----------|--------|---------|
|   1.33 | 20000000 |  99.99 |  111.11 |

插入的float2列就是使用了科学计数法，至于float2为什么是20000000，插入时float2的值原本应该是19999999.999999，那是因为小数点超出范围自动四舍五入后进位了

+ 浮点类型数据超出精度范围后一定会进行四舍五入，如果因为系统四舍五入导致进位，而使得整数部分超出指定的长度，系统也是认可的，数据库保存结果也是超出整数位数的，但是从`MySQL8.0.17`版本后不在支持这类进位溢出，会有下面的提示

> 小数型超出范围同样会提示：ERROR: 1264: Out of range value for column '\_float4' at row 1

#### 2.2. 定点型
定点型绝对的保证整数部分不会被四舍五入，小数部分理论上也不会丢失精度，但不是绝对，定点类型格式`decimal(M,D)`

|类型|说明|字节|位|取值范围
|:--|:--|:--|:--|:--|
|decimal|定点型|变化不固定的，每9个数字采用4个字节存储，整数和小数是分开计算的 整数字节计算公式：(M-D)/9\*4 小数字节计算公式：D/9\*4|M/9\*4\*8|最大为65\*9，因为M最大为65，D最大为30，MySQL默认M=10，D=2|

插入数据时定点数的整数部分一定不能超出长度，进位也是不允许的，小数部分的长度可以随意超出，系统会对小数部分自动进行四舍五入计算

下面用一个示例来验证小数的进位
```SQL
create table test_decimal(dec1 decimal(10,2)) charset utf8mb4;
-- 插入一条小数进位的记录
insert into test_decimal values(12345678.999);
```

结果：记录插入成功了，但是有警告提示Note (code 1265): Data truncated for column 'dec1' at row 1，系统允许了小数四舍五入进位，小数位数被D约束了，`整数的范围是M-D`，千万别记错以为整数就是M

| dec1        |
|-------------|
| 12345679.00 |

> __附加系统查看警告提醒命令：__
```SQL
-- 查看警告
show warnings;
```
这条命令可以查看上次命令执行时出现的警告信息

使用下面的语句验证小数进位后，整数如果超出范围也是不允许的，会提示超出范围
```SQL
-- 插入一条小数进位的记录
insert into test_decimal values(99999999.999);
```
结果：插入失败，错误提示ERROR: 1264: Out of range value for column ‘dec1’ at row 1