MySQL数据类型

目录

1、整数类型

1.1、分类

1.2、区别

1.3、整数类型的可选属性

1.4、适用场景

1.5、如何选择

2、浮点类型

2.1、数据精度说明

2.2、四舍五入问题

2.3、浮点类型精度问题

3、定点数类型

3.1、DECIMAL精度

4、BIT位类型

5、日期与时间类型

5.1、YEAR类型

5.2、DATE类型

5.3、TIME类型

5.4、DATETIME类型

5.5、TIMESTAMP类型

5.6、DATETIME与TIMESTAMP的区别

5.7、日期时间开发总结

6、文本字符串类型

6.1、CHAR与VARCHAR类型

6.2、TEXT类型

7、ENUM类型

8、SET类型

9、二进制字符串类型

9.1、BINAY与VARBINNAY类型

9.2、BLOB类型

10、JSON 类型

11、总结

1、整数类型

1.1、分类

共5种：

• TINYINT
• SMALLINT
• MEDIUMINT
• INT（INTEGER）
• BIGINT

1.2、区别

1.3、整数类型的可选属性

有3个：

<1>M

M：表示显示宽度，M的取值范围为（0,255），实际上显示宽度单独使用时，没有任何作用

我们插入的值不需要受显示宽度的影响！（从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性，INT默认的显示宽度都是10）

显示宽度的唯一作用：与"ZEROFILL"关键字一起使用，表示插入数据时，在不满足显示宽度的基础上，在整数前面补0（MySQL8以上，都是按10位的显示宽度进行补0）。

例如：

CREATE TABLE TEST_INT01(A INT(5),B INT(5) zerofill);

INSERT INTO TEST_INT01(A,B) VALUES(123,123);		--B不满足5为显示长度，会补0
INSERT INTO TEST_INT01(A,B) VALUES(123456,123);		--A字段不会受显示长度约束	

<2>UNSIGNED

无符号的：表示整数只能是正数

<3>ZEROFILL

0进行填充

注意：当字段设置为ZEROFILL，MySQL会自动给该字段添加UNSIGNED

1.4、适用场景

TINYINT：一般用于枚举数据，比如系统设定取值范围很小且固定的场景，如0-100的取值

SMALLINT：可以用于较小范围的统计数据，如统计工厂的固定资产库存数量等

MEDIUMINT：用于较大整数的计算，比如车站每日的客流量等

INT/INTEGER：常用，一般不用考虑超限问题

BIGINT：只有处理特别巨大的整数才会用到。如双十一的交易量、大型门户的点击量、证券公司衍生产品持仓等。

1.5、如何选择

系统性能>>字段存储空间

在确保数据不会出现超出范围的前提下，再去选择字段的存储空间

2、浮点类型

用于处理小数，MySQL浮点类型共3类

• FLOAT：单精度浮点数
• DOUBLE：双精度浮点数
• REAL：默认就是DOUBLE，可以通过 SET SQL_MODE = "REAL_ASFLOAT";设置为FLOAT

2.1、数据精度说明

MySQL支持手动设置小数精度（不手动设置，默认会根据操作系统及配置来设置小数精度），如：

FLOAT(M,D)

DOUBLE(M,D)

• M：精度
• D：标度
• M=整数位+小数位
• D=小数位
• D<=M<=255
• 0<=D<=30

2.2、四舍五入问题

在手动设置精度情况下：

• 小数位超出范围，会自动四舍五入
• 整数位超出范围，会报错

2.3、浮点类型精度问题

FLOAT与DOUBLE在计算时，经常会出现精度问题，如：

字段中3个值：0.15,0.85,0.99

我们使用SUM函数求和后，会得到：1.999999999999（double类型的情况下，float精度问题更大）.

因此：我们在使用浮点型进行比较时，应当避免直接使用"="符号比较

3、定点数类型

在MySQL中，定点数据类型只有DECIMAL一种

但是：可以写成其他形式，如：DEC, NUMBERIC都是表示DECIMAL

3.1、DECIMAL精度

DECIMAL(M,D)精度与浮点数精度类似，如下：

• 字节数 = M+2
• 0<=M<=65
• 0<=D<=30
• D<M
• DECIMAL最大取值范围与double一样，DECIMAL小数是精准的
• DECIMAL在MySQL内部是以字符串的形式存储的
• DECIMAL在不指定精度的情况下，默认为DECIMAL(10,0)，即存储10位的整数，出现小数位会直接四舍五入处理

4、BIT位类型

BIT类型存储的是二进制值，类似：0100110

5、日期与时间类型

MySQL8.0版本支持的日期和时间类型主要有：

• YEAR：年
• DATE：年月日
• TIME：时分秒
• DATETIME：年月日时分秒
• TIMESTAMP：带有时区的年月日时分秒

注意：TIME的取值范围不是-23:59:59 ~ 23:59:59，在MySQL设计TIME类型时，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过24小时。

5.1、YEAR类型

默认情况下，时间精度为YEAR(4)，即：YYYY

我们也可以指定YEAR的长度，这个在开发中比较复杂，从而MySQL不推荐手动指定YEAR类型的长度，直接在声明字段时，使用YEAR即可

5.2、DATE类型

默认格式：YYYY-MM-DD

• 当我们插入数据的格式为YYYYMMDD时，会自动转换为YYYY-MM-DD
• 使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期

5.3、TIME类型

默认格式：HH:MM:SS

插入数据时，可以使用以下字符串格式进行插入：

1. D HH:MM:SS ：D表示天数，与HH组成一个"时"的整体（D*24+HH），存入数据库中
2. HH:MM:SS 
3. HH:MM
4. D HH:MM
5. D HH
6. SS
7. 当使用不带冒号的数字时，MySQL会自动转换为对应的格式，如：1210转换为00:12:10
8. 使用CURRENT_DATE()或者NOW()函数，会插入当前系统的时间

5.4、DATETIME类型

DATETIME类型是所有日期时间类型中占有空间最大的（8字节），格式为DATE类型+TIME类型

即：YYYY-MM-DD HH:MM:SS

• 当以YYYYMMDDHHMMSS插入数据时，会自动转换为YYYY-MM-DD HH:MM:SS
• 使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期时间

5.5、TIMESTAMP类型

与DATETIME类型相比，存储空间小（4字节），格式也是一样的，但是存储的时间范围很小，为：1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC （UTC表示世界统一时间/世界标准时间）

注意：这里存在一个SQL攻击：插入数据使用不在范围内的值，从而导致数据库报错

5.6、DATETIME与TIMESTAMP的区别

1. TIMESTAMP存储空间小，表示的日期时间范围也比较小
2. 底层存储方式不同，TIMESTAMP存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值
3. 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快
4. TIMESTAMP和时区有关。会根据用户的时区不同，显示不同的结果；而DATETIME只能获取插入数据时的当地时区，其他时区的人查看时，数据会存在误差

5.7、日期时间开发总结

DATETIME是使用最多的类型，在以下情况下（时间需要计算）：注册时间、商品发布时间等，使用时间戳，因为DATETIME不方便计算。

时间戳使用：使用BIGINT对当前时间戳进行存储

6、文本字符串类型

6.1、CHAR与VARCHAR类型

CHAR与VARCHAR都可以用来存储比较短的字符串

区别：

注意：CHAR默认长度为1，VARCHAR必须指定长度

案例：

CHAR(10)：存储10个字节以下的字符串时，占有空间都是10字节

VARCHAR(10)：存储多少个字符串，占用空间为"字符串实际长度+1"，这里的1的作用：用来存储实际字符串的长度

适用场景：

以下场景可以使用CHAR, 如：

1. 门牌号（101、102....）这种存储很短的信息，应该使用char，而varchar还要多使用一个字节用于记录实际长度
2. 固定长度：如使用uuid作为主键，由于是固定长度，varchar的长度动态优势也就消失了，而且还会多占有一个字节
3. 字段存储数据的长度变化频繁：因为varchar每次存储都要有额外的计算，得到长度等工作，如果是一个非常频繁改变的场景，MySQL会花费很多性能用于计算字符串长度，这些对于char是不需要的

在InnoDB存储引擎中，除了简短并且固定长度的字符串使用char外，其他的情况都建议使用varchar类型

CHAR与VARCHAR对应字符串左右空格的处理：

• char在存储数据时，会默认剔除字符串右侧空格，保留左侧空格
• varchar在存储数据时，不做剔除
• char与varchar在查询语句的where条件中，条件值会保留左侧空格，下面查询结果都是一样的，如：

-- NAME字段为CHAR或者VARCHAR类型
SELECT * FROM STUDENT WHERE NAME = " 张三";  -- 张三前有空格

SELECT * FROM STUDENT WHERE NAME = " 张三 ";  --张三前后有空格

6.2、TEXT类型

TEXT类型的存储空间与varchar类型相同，都是动态变化的，如下：

 由于TEXT实际存储长度不确定，MySQL不允许TEXT作为主键

实际开发中：TEXT类型会导致查询速度慢，在内容不是很大的情况下，建议使用CHAR、VARCHAR代替，TEXT是不需要添加默认值的（不会生效），并且TEXT和BLOB类型数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议使用TEXT类型字段，建议单独分出去，单独使用一张表（或者使用文件服务器进行存储）

7、ENUM类型

枚举类型：

注意：

• 枚举类型在插入修改时，忽略大小写
• 可以使用索引进行编辑元素，索引可以使用字符串形式
• 可以插入NULL值，可以使用字段非空进行限制

案例：

CREATE TABLE STUDENT(
SEX ENUM('男','女','unknow')   --只能插入枚举中规定的元素且忽略大小写
); 

INSERT INTO STUDENT(SEX) VALUES('男');
--使用索引进行枚举元素调用  插入第一个元素 
INSERT INTO STUDENT(SEX) VALUES(1);
--插入第三个元素 索引可以使用字符串
INSERT INTO STUDENT(SEX) VALUES('3');
--插入NULL元素也是可以的
INSERT INTO STUDENT(SEX) VALUES(NULL);

8、SET类型

SET表示一个字符串对象，可以包含0个或多个成员，但成员个数上限为64

 案例：

CREATE table STUDENT(
subject set ('语','数','英')
); 

INSERT INTO STUDENT(subject) VALUES('语');
INSERT INTO STUDENT(subject) VALUES('语,数');
INSERT INTO STUDENT(subject) VALUES('语,数,英,语');
INSERT INTO STUDENT(subject) VALUES('语,数,数');

SELECT * FROM  STUDENT;

结果为：

结论：SET类型会对数据进行去重

9、二进制字符串类型

MySQL中，二进制字符串类型主要存储一些二进制数据，如：图片、音频、视频等

包括：

• BINAY
• VARBINNAY
• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

9.1、BINAY与VARBINNAY类型

类似于CHAR与VARCHAR（参照6.1），只是用于存储二进制字符串

 开发中使用场景很少

9.2、BLOB类型

BLOB是一个二进制大对象，可以容纳可变数量的数据 

注意：在实际开发中，BLOB不会存储图片视频音频，会使用文件服务器进行存储，MySQL只是存储他们的路径

10、JSON 类型

11、总结