MySQl篇（数据类型）（持续更新迭代）

目录

常见类型一：数值类型

常见类型二：字符串类型

一、文本字符串类型

1. char & varchar

1.1. CHAR(M)类型

1.2. VARCHAR(M)类型

1.3. 两者应用

2. enum & set

二、二进制字符串类型

1. BINARY & VARBINARY类型

2. 二进制字符串和文本字符串

2.1. 在Navicat客户端查看“t_binary”的数据，发现乱码

2.2. 在Navicat客户端查看“t_binary”的数据，发现乱码

2.3. 编码处理注意事项

3. BLOB & TEXT

3.1. BLOB类型与TEXT类型的区别

3.2. BLOB类型使用时的注意事项

常见类型三：日期时间类型

1. YEAR

2. DATE

3. TIME

4. DATATIME

5. TIMESTAMP

常见类型四：JSON类型

常见类型五：空间类型

常见类型一：数值类型

常见类型二：字符串类型

MySQL的字符串类型有CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET等

MySQL的字符串类型可以用来存储文本字符串数据，还可以存储二进制字符串

一、文本字符串类型

1. char & varchar

1.1. CHAR(M)类型

CHAR(M)为固定长度的字符串， M表示最多能存储的字符数，取值范围是0~255个字符，

如果未指定(M)表示只能存储1个字符

例如:

CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4，

如果存储的值少于4个字符，右侧将用空格填充以达到指定的长度，当查询显示CHAR值时，尾部的空格将被删掉

1.2. VARCHAR(M)类型

VARCHAR(M)为可变长度的字符串，M表示最多能存储的字符数，

M的范围由最长的行的大小（通常是65535）和使用的字符集确定

例如：

utf8mb4字符编码单个字符所需最长字节值为4个字节，所以M的范围是[0, 16383]

而VARCHAR类型的字段实际占用的空间为字符串的实际长度加1或2个字节，

这1或2个字节用于描述字符串值的实际字节数,

即字符串值在[0,255]个字节范围内，那么额外增加1个字节，否则需要额外增加2个字节

1.3. 两者应用

CHAR(M)、VARCHAR(M)应用场景：

身份证号、手机号码、QQ号、用户名：username、密码：password、

银行卡号等固定长度的文本字符串适合使用CHAR类型，

而评论、朋友圈、微博不定长度的文本字符串更适合使用VARCHAR类型

存储引擎对于选择CHAR和VARCHAR的影响：

存储引擎对于选择CHAR和VARCHAR是有影响的

• 对于MyISAM存储引擎，最好使用固定长度的数据列代替可变长度的数据列，这样可以使整个表静态化，从而使数据检索更快，用空间换时间
• 对于InnoDB存储引擎，使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，

但由于VARCHAR是按照实际的长度存储的，比较节省空间，所以对磁盘I/O和数据存储总量比较好。

2. enum & set

无论是数值类型、日期类型、普通的文本类型，可取值的范围都非常大，

但是有时候我们指定在固定的几个值范围内选择一个或多个，那么就需要使用ENUM枚举类型和SET集合类型了，

例如：比如性别只有“男”或“女”；上下班交通方式可以有“地铁”、“公交”、“出租车”、“自行车”、

“步行”等

枚举和集合类型字段声明的语法格式如下：

字段名 ENUM（‘值1’，‘值2’，…‘值n’）

字段名 SET（‘值1’，‘值2’，…‘值n’）

ENUM类型的字段在赋值时，只能在指定的枚举列表中取值，而且一次只能取一个

枚举列表最多可以有65535个成员

ENUM值在内部用整数表示，每个枚举值均有一个索引值， MySQL存储的就是这个索引编号，

例如：定义ENUM类型的列(‘first’, ‘second’, ‘third’)

SET类型的字段在赋值时，可从定义的值列表中选择1个或多个值的组合，SET列最多可以有64个成员

SET值在内部也用整数表示，分别是1，2，4，8……，都是2的n次方值，

因为这些整数值对应的二进制都是只有1位是1，其余是0

二、二进制字符串类型

1. BINARY & VARBINARY类型

BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串

BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字节，

若未指定(M)表示只能存储1个字节

例如：

BINARY (8)，表示最多能存储8个字节，若字段值不足(M)个字节，将在右边填充'\0'以补齐指定长度

VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，

另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数

VARBINARY类型和VARCHAR类型一样必须指定(M)，否则报错！

2. 二进制字符串和文本字符串

二进制字符串是存储在客户端给服务器端传输的字符串的原始二进制值，

而文本字符串则会按照表和字段的字符集编码方式对客户端给服务器传输的字符串进行转码处理，

二进制字符串严格区分大小写（因为大小写字符的编码值不同），文本字符串在大多数字符集和校对规则中不区分大小写

2.1. 在Navicat客户端查看“t_binary”的数据，发现乱码

因为命令行客户端的编码默认是GBK，而服务器端t_binary表的编码是“utf8mb4”，

如果是binary等这种二进制字符串的话，客户端传给服务器端的字符编码的二进制（基于GBK编码的），

服务器接收后是“原样”存储，不转码的，所以表格中存储的是“政政政”基于GBK编码的二进制值，

在可视化工具中（UTF8编码）显示不了，中文乱码问题！

2.2. 在Navicat客户端查看“t_binary”的数据，发现乱码

如果是char和varchar类型等这种文本字符串的话，客户端传给服务器端的字符编码的二进制（基于GBK编码

的），并且会把编码方式一并告诉服务器端，服务器接收后会进行转码，存储为utf8的二进制值，显示正常，未出

现中文乱码问题！

2.3. 编码处理注意事项

utf8mb4编码处理，即utf8编码方式，一个汉字占用 3 个字节

GBK编码处理，一个汉字 占用 2 个字节

在可视化工具中，在t_binary表中添加“政哥”时，utf-8编码处理，一个汉字 占 3 个字节，“政哥” 就有 6 个

字节

在命令行客户端中，在t_binary表中添加“政哥”时，一个汉字 占 2 个字节，“政哥” 就有 4 个字

3. BLOB & TEXT

BLOB是一个二进制大对象，用来存储可变数量的二进制字符串，

分为TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB四种类型

TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT四种文本类型，

分别对应于以上四种BLOB类型，具有相同的最大长度和存储要求

3.1. BLOB类型与TEXT类型的区别

BLOB类型存储的是二进制字符串，TEXT类型存储的是文本字符串，BLOB类型还可以存储图片和声音等二进制数

据BLOB类型没有字符集，并且排序和比较基于列值字节的数值，TEXT类型有一个字符集，并且根据字符集对值进

行排序和比较

3.2. BLOB类型使用时的注意事项

BLOB类型的数据支持存储图片等数据，存储图片等数据可借助图形界面工具导图图片实现！

BLOB类型的数据除了受到类型本身大小的限制外，还会受到服务器端“max_allowed_packet”变量值限定的字

节值大小限制，

如果从客户端给服务器端上传的BLOB数据大小超过该值时会报错

如果确实需要上传并存储更大的图片，

可以停止MySQL服务并修改my.ini配置文件的“max_allowed_packet”值大小来解决这个问题，

例如：

将“max_allowed_packet”的默认值4M修改为“max_allowed_packet=16M”

如果图片大小超过blob类型，还需要修改字段的数据类型为mediumblob或longblob类型！

常见类型三：日期时间类型

1. YEAR

如果仅仅是表示年份信息，可以只使用YEAR类型，这样更节省空间，

格式为“YYYY”，例如“2022”，YEAR允许的值范围是1901~2155

YEAR还有格式为“YY”2位数字的形式，值是0069，表示20002069年，值是7099，表示19701999年，

从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用，YEAR默认格式就是“YYYY”，没必要写成YEAR(4)，

从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型，

这个0年，如果是以整数的0添加的话，那么是0000年，如果是以日期/字符串的'0'添加的话，是2000年

2. DATE

如果要表示年月日，可以使用DATE类型，格式为“YYYY-MM-DD”，

例如：“2022-02-04”

3. TIME

如果要表示时分秒，可以使用TIME类型，格式为“HH:MM:SS”，

例如：“10:08:08”

4. DATATIME

如果要表示年月日时分秒的完整日期时间，可以使用DATATIME类型，格式为“YYYY-MM-DD HH:MM:SS”，

例如：“2022-02-04 10:08:08”

5. TIMESTAMP

如果需要经常插入或更新日期时间为系统日期时间，则通常使用TIMESTAMP类型，格式为“YYYY-MM-DD

HH:MM:SS”，例如：“2022-02-04 10:08:08”

TIMESTAMP与DATETIME的区别：

TIMESTAMP 与 DATETIME 的区别在于 TIMESTAMP 的取值范围小，

TIMESTAMP只支持1970-01-01 00:00:01 UTC至2038-01-19 03:14:07 UTC范围的日期时间值，其中UTC是世界标准时间，

并且TIMESTAMP类型的日期时间值在存储时会将当前时区的日期时间值转换为时间标准时间值，

检索时再转换回当前时区的日期时间值，这会更友好，

而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的

另外，TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大

常见类型四：JSON类型

在MySQL5.7之前，若需要在数据库中存储 JSON数据只能使用 VARCHAR或TEXT字符串类型

从5.7.8版本之后开始支持 JSON 数据类型

常见类型五：空间类型

MySQL空间类型扩展支持地理特征的生成、存储和分析这里的地理特征表示世界上具有位置的任何东西，

可以是一个实体：一座山，

也可以是空间：一座办公楼，

还可以是可定义的位置：一个十字路口，

等等

现在的应用程序开发中空间数据的存储越来越多，

例如：钉钉的打卡位置是否在办公区域范围内，滴滴打车的位置、路线等

MySQL提供了非常丰富的空间函数以支持各种空间数据的查询和处理，

MySQL中使用Geometry（几何）来表示所有地理特征，

Geometry指一个点或点的集合，代表世界上任何具有位置的事物，

MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，

包括GEOMETRY、POINT、LINESTRING、POLYGON等单值类型，

以及MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION存放不

同几何值的集合类型