MySQL数据类型

整型

整数类型 占字节数 范围
TINYINT 1
SMALLINT 2
MEDIUMINT 3
INT、INTEGER 4
BIGINT 8

整型可选属性

1. MySQL5.7以下属性：ZEROFILL
MySQL5.7及之前版本的整型带位宽显示，表示负数(含符号位)占的位宽长度，当输出整数需要统一位宽时(不足位宽以0填充高位，超出位宽则不影响)，可以使用ZEROFILL保留字修饰数据(此时类型字段转变成UNSIGNED无符号)。 

1
2
salary int,
id int(5) ZEROFILL  -- 123输出00123，123456仍然输出123456；
但MySQL在8.0以上已经不建议使用位宽显示。

2. 无符号UNSIGNED
使用UNSIGNED修饰表示其是无符号数，对应范围应该是0——2^{占位数}-1，例如TINYINT对应0——255； 

1
id TINYINT UNSIGNED  -- 或者其他int类型

浮点/定点数

浮点类型 占字节数
FLOAT 4
DOUBLE 8

浮点数细节问题

1. 浮点数存储原理

数据库和C写了那么多次float和double，我们似乎从来没有担心过浮点数越界的问题，因为浮点数的存储并非简单地将二进制地位划给小数、高位划给整数，而是采用了指数和尾数的记录方式，这种方式使得浮点数有足够的精度表示0附件的小数，也可以表示极大的一个数，但是在远大于0的地方会带来精度损失。

以float为例存储浮点数6.5原理： 6.5转换成二进制：整数除2取余、小数乘2取整，为110.1；（乘2取整法告诉我们以5或者0结尾的小数不存在精度损失）。

转换成科学计数法为：，即指数部分为2、尾数部分是101；注意，指数存储需要加上127偏移量，即存储的指数部分应该是129；

因此float默认存储1符号位+8指数位+23位小数位，分别存入0、129的二进制以及101二进制。

float的最大范围如何呢？0+255的二进制+23全1，对应1.11111...(23个小数1)×2{255-127} = ？，注意这里还要修正，最大指数不能是128，这个用于表示无限大，因此对应应该是1.11111...(23个小数1)×2{127}（注意这里是二进制的1.111111...而不是十进制，其十进制应该以23阶接近2）,约3.40E+38。远大于long和int表示的范围，这就是科学计数法的基本存储原理。

double的原理也是如此，double默认1符号位+11位指数位+52位小数位。

也因为这种存储方式，因此UNSIGNED关键字不会扩大其表示范围，仅去除负数的表示范围。

2. MySQL标度和精度

标度=整数+小数位数、精度=小数位数，如FLOAT(标度，精度)，FLOAT(5,2)可以表示-999.99到999.99，DOUBLE同理。当定义精度小于插入位数，例如111.456，会对其执行四舍五入，存入111.46，如果因为进位导致越界，插入失败。

3. 精度损失

小数的二进制存储需要乘2取整，因此如果不以0和5结尾的小数，必然会存在精度损失，因此存入一批小数进行浮点数运算时，只会逼近目标数，不会等于目标数，因此应该避免使用float和double类型进行等值比较。

精度表示：定点数

为了精确支持小数表示，只能放开占用字节数的限制，使用字符串存储，在FPGA、MySQL中都存在定点数的广泛使用。

定点数类型 字节数 范围
DECIMAL(M,D) M+2字节 取决于M、D，默认M=10，D=0

位类型

位类型 占字节数 二进制长度范围
BIT(M) (M+7)/8字节 1<=M<=64 
1
2
3
4
5
id BIT  -- 默认M=1位，可表示0、1
id1 BIT(8) -- 一字节

SELECT BIN(id),HEX(id1),id1+0  -- 输出二进制、十六进制、十进制
FROM table;

日期类型

类型 名称 字节 日期格式 最小值 最大值
YEAR 年 1 YYYY或YY 1901 2155
TIME 时间 3 HH:MM:SS -838:59:59 838:59:59
DATE 日期 3 YYYY-MM-DD 1000-01-01 9999-12-03
DATETIME 日期时间 8 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP 日期时间 4 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 2038-01-19 03:14:07