DBA MySQL数值类型

整数类型

基本介绍

​ 整数类型包含TINYINT、SMALLINT、MEDIUMINT、INT 与BIGINT等。

​ 下表中是其存取范围与显示宽度等信息：

类型	存储大小	默认显示宽度（个）	范围（有符号）	范围（无符号）
TINYINT(m)	1Byte	m：4	-128 - 127	0 - 255
SMALLINT(m)	2Byte	m：6	-32768 - 32767	0 - 65535
MEDIUMINT(m)	3Byte	m：9	-8388608 - 8388607	0 - 16777215
INT|INTEGER(m)	4Byte	m：11	-2147483648 - 2147483647	0 - 4294967295
BIGINT(m)	8Byte	m：20	-9233372036854775808 - 9223372036854775807	0 - 18446744073709551615

​ m为其显示宽度，在为字段设置 ZEROFILL约束条件时有效，否则将不会填充满整个显示宽度。

可选约束

​ 除了公用完整性约束外，还有两个可选约束，如下表所示：

约束条件	描述
UNSIGNED	无符号存储，该约束条件必须紧跟在类型声明后，如INT UNSIGNED
ZEROFILL	显示宽度不够时使用0进行填充，该约束推荐跟在类型声明或无符号存储约束声明后

​ 使用一切数值类型时，指定其宽度均是为其指定显示宽度，并非存入的限制宽度。

​ 以下示例将演示为INT类型设置设置了显示宽度后，当宽度不够时将以指定字符进行填充。

# 创建temp表，显示宽度为5，有num字段，无符号整型，不可为空，默认值是0，显示宽度不够时使用0进行宽度填充
M > CREATE TABLE temp (
        num INT(5) UNSIGNED ZEROFILL NOT NULL DEFAULT 0 COMMENT "显示宽度测试字段"
    ) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

# 插入两条数据，一条显示宽度不够，一条显示宽度足够，查看显示情况
M > INSERT INTO
        temp(num)
    VALUES
        (1),
        (99999);

# 查看显示情况
M > SELECT * FROM temp;
+-------+
| num   |
+-------+
| 00001 |
| 99999 |
+-------+

范围超出

​ 当范围超出默认值时，将会按照最大值或最小值进行存入。

MySQL5.7.28版本中这样的操作将会抛出异常

但是在MySQL5.6.X版本中这样的操作是被允许的（非严格模式下）

​ 示例演示，5.6版本：

M > INSERT INTO
        temp(num)
    VALUES
        (-1),
        (4294967296);
        
M > SELECT * FROM temp;
+------------+
|    temp    |
+------------+
|      0     | # -1存成了0
| 4294967295 | # 4294967296变为4294967295
+------------+

浮点类型

基本介绍

​ 浮点类型包括FLOAT、DOUBLE、DECIMAL。

​ 下表中是其存取范围与显示宽度等信息：

类型	存储大小	最大显示宽度（个）	范围（有符号）	范围（无符号）	精确度
FLOAT(m[,d])	4Bytes	m：255，d：30	(-3.402 823 466 E+38，-1.175 494 351 E-38) - 0	0 - (1.175 494 351 E-38，3.402 823 466 E+38)	点七位以内
DOUBLE(m[,d])	8Bytes	m：255，d：30	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308) - 0	0 - (2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	点十五位以内
DECIMAL(m[,d])	m+2（如果m<d则d+2）	m：65，d：30	取决于m,d（m范围（1-65），d范围（0-30））	取决于m,d（m范围（1-65），d范围（0-30））	绝对精准

​ m为其整数部分显示个数，n为其小数部分显示个数。

​ DECIMAL底层由字符串进行存储，故精度不会出现偏差，也被称为定点类型。

精度问题

​ 根据不同的需求，应当使用不同的浮点类型进行存储，一般来说使用FLOAT足以，但是对于一些精度非常高的数据则应该使用DECIMAL类型进行存储。

​ 以下示例将演示使用不同的浮点类型进行值存储时会发生精度问题。

# 创建三张表，并指定显示宽度
M > CREATE TABLE t1(num FLOAT(255,30)); 

M > CREATE TABLE t2(num DOUBLE(255,30)); 

M > CREATE TABLE t3(num DECIMAL(65,30))

# 插入三条记录，查看结果是否准确
M > INSERT INTO t1(num) VALUES(1.11111111111111111);

M > INSERT INTO t2(num) VALUES(1.11111111111111111);

M > INSERT INTO t3(num) VALUES(1.11111111111111111);

# 查看结果，FLOAT精确度在点后7位以内
M > SELECT * FROM t1;  
+----------------------------------+
| num                              |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+

# 查看结果，DOUBLE精确度在点后15位以内
M > SELECT * FROM t2;  
+----------------------------------+
| num                              |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+

# 查看结果，DECIMAL绝对精确
M > SELECT * FROM t3;  
+----------------------------------+
| num                              |
+----------------------------------+
| 1.111111111111111110000000000000 |
+----------------------------------+

位类型

​ 位类型为BIT(m)，可存放多位二进制数，m范围从1~64，如果不写默认为1位。

​ 需要注意的是对于位字段需要使用函数读取：

函数	描述
bin()	显示为二进制
hex()	显示为十六进制

​ 示例如下：

# 创建temp表，num字段为bit类型
M > CREATE TABLE temp(num BIT);  

# 默认显示宽度为1
M > DESC temp; 
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| num   | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+

# 插入记录，1
M > INSERT INTO temp(num) VALUES(1);  

# 直接查看是查看不到的
M > SELECT * FROM temp;  
+------+
| num  |
+------+
| 
    |
+------+

# 需要转换为二进制或十六进制进行查看
M > SELECT bin(num),hex(num) FROM temp; 
+----------+----------+
| bin(num) | hex(num) |
+----------+----------+
| 1        | 1        |
+----------+----------+

# 修改num字段的显示宽度为5
M > ALTER TABLE temp MODIFY num BIT(5);  

# 插入记录，num为8
M > INSERT INTO temp VALUES(8); 

# 显示为不同的进制
M > SELECT bin(num),hex(num) FROM temp;
+----------+----------+
| bin(num) | hex(num) |
+----------+----------+
| 1        | 1        |
| 1000     | 8        |
+----------+----------+