【MYSQL】数据库的常见数据类型--详解-平芜编程栈

一.数据类型

1-1数据类型分类

1-2数值类型

整型可以指定是有符号的和无符号的，默认是有符号的。

tinyint类型(有符号）
在 MySQL 表中建立属性列时，我们可以发现列名称在前，类型在后。

插入数据进行越界测试（会报错）

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。
可以通过unsigned来说明某个字段是无符号的

unsigned(无符号)

插入数据进行越界测试（会报错）

char a = 123456; C/C++ 中，编译器不会报错，最多也就是警告，这里会发生截断，甚至发生隐式转换。 MySQL 中，在特定的类型中插入不合规的数据，一般会发生拦截，不让我们做对应的操作。（约束） 反过来，如果已经有数据被成功插入到 MySQL 中，那么说明插入时一定是合法的。 所以，在 MySQL 中，一般而言，数据类型本身也是一种约束 目的是：能够保证数据库中的数据是可预期、完整的
也就类比做约束，下一篇文章我会讲
补充：
C/C++ 的 char 类型通常只占 1 字节，取值范围是 -128 到 127 或 0 到 255。赋值 123456 远超这个范围，编译器只会警告，实际运行时只保留低 8 位，造成数据丢失。
MySQL 的数据类型约束更严格，插入非法值（如将 'abc' 插入 INT 列）会直接报错拒绝执行。
这种设计让 MySQL 的数据质量更有保障，应用程序写错了也能被数据库拦截，避免脏数据入库

注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不
下，与其如此，还不如设计时，将int类型提升为bigint类型。

bit类型

基本语法：

bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

插入数据越界测试

1.0x01 / 0x00 是什么？
这是MySQL命令行对二进制/位类型数据的默认显示方式， 0x 开头表示后面的内容是十六进制数： 0x01 = 十进制的 1 0x00 = 十进制的 0
2. hex(online) 函数的作用

hex() 是MySQL提供的函数，它会把字段里的内容转换成十六进制字符串输出：
当 online 存的是 1 ， hex(online) 就返回字符串 '1' （本质上是十六进制表示的结果）
当 online 存的是 0 ， hex(online) 就返回字符串 '0'
online 字段的类型是单字节类型，只能存储 0 和 1，插入大于 1 的值（比如 2）会触发 Data too long 报错。

修改online的bit位

当 online 是 BIT （二进制位类型）时
select * 直接查询原始二进制数据二进制字符串（显示为0x...） 0x0061
hex(online) 把二进制数据转成十六进制文本字符串（十六进制表示） 61
online+0 把十六进制数据隐式转为十进制整数数字（十进制） 97

mysql8.0

mysql5.7

结果可以看到：这说明online 字段的类型是字符/字符串类型（如 CHAR 、 VARCHAR 或 TINYTEXT ）；
当你插入数字 97 时，MySQL会把它当作ASCII码值处理，自动转换为对应的字符 'a' （因为 'a' 的ASCII码就是97）。而直接插入 'a' 时，MySQL直接存储字符本身，所以两种插入方式的结果是一样的。

bit 字段在显示时，是按照ASCII码对应的值显示（这个旧版本存在的）。
如果我们有这样的值，只存放 0/1，这时可以定义 bit(1)，可以节省空间
BIT(1) ：只占 1 位，存 0 或 1
BIT(3) ：占 3 位，可以存 000、001、010、011、100、101、110、111 这 8 种状态

这是因为字段在创建时定义的数据类型不一样，MySQL会根据你定义的类型来决定怎么存储和处理数据。
1. 当 online 是 VARCHAR / CHAR （字符串类型）时
存储：把输入的内容当作文本字符直接存起来，比如输入 'a' 就存字符 a ，输入 97 也会被转成字符串 "97" 存储（特殊情况：如果输入数字 97 ，MySQL会自动把它转成ASCII码对应的字符 'a' 存储）。
显示： SELECT * 时直接显示文本内容，比如 a 、 97 。
处理： HEX(online) 会把字符的ASCII码转成十六进制，比如 'a' 的ASCII码是97，十六进制就是 61 。
2. 当 online 是 BIT （二进制位类型）时
存储：把输入的内容当作二进制数值存起来，比如输入 1 就存二进制 0000000000000001 ，输入 97 就存二进制 0000000001100001 。
显示： SELECT * 时会以 0x 开头的十六进制形式显示（比如 0x0001 、 0x0061 ），这是MySQL对二进制数据的默认显示方式。
处理： HEX(online) 会直接把二进制数值转成十六进制字符串（比如 1 、 61 ）； online+0 会把二进制数值转成十进制整数（比如 1 、 97 ）。
建表时写 online VARCHAR(10) → 就是字符串类型
建表时写 online BIT(16) → 就是二进制位类型

这是MySQL 8.0 客户端对 BIT 类型的默认显示方式：直接显示为0x开头的十六进制字面量，而不是像旧版本那样，把二进制字节当作 ASCII 字符显示出来（比如0x0061显示成a）。
旧版本（MySQL 5.x）： BIT 字段查询时，会把存储的二进制字节直接当作字符串输出，客户端就会按 ASCII 码解析成字符。比如0x61会直接显示成a，0x0A会显示成换行符，0x00显示成空白，这就是你听到的 “按 ASCII 显示” 的来源。
MySQL 8.0 以后：客户端默认行为改了：BIT 类型不再直接解析为 ASCII 字符，而是直接以0x十六进制的形式展示原始字节。你截图里的0x0001、0x0061，就是这个新行为的直接体现。

小数类型

float

基本语法：

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

【补充】

MySQL 5.7 及之前： INT(1) 、 INT(3) 、 INT(11) 这种写法是有“显示宽度”的概念的，配合 ZEROFILL 可以控制补零显示，比如 INT(3) ZEROFILL 存 5 会显示成 005 。
MySQL 8.0 以后：这个数字就只剩个“占位”作用了，完全不影响存储和显示，只是个语法兼容的摆设，甚至很多地方默认都不推荐写了。
很多人会误以为 INT(1) 只能存 0-9， INT(3) 只能存 0-999，这是个经典误区：
它从来都不限制存储的数值范围，只影响“显示时的最小宽度”。
INT 本身固定是 4 字节，能存 -2,147,483,648 到 2,147,483,647 ，和括号里的数字一点关系都没有。
8.0 以后的变化
显示宽度功能被废弃了， ZEROFILL 也不推荐用了。
现在写 INT 就行，写 INT(1) 、 INT(11) 完全没区别，MySQL 会忽略括号里的数字

插入数据越界测试：

（\G后面的分号可以省略）

小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。
注意：在合法范围内允许五入，但在边界值五入会导致整体的浮点数增多，从而超过浮点数对应的范围，所以不是所有情况都可以五入。

unsigned(无符号）

会有精度损失

decimal

语法：

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

decimal(5,2) 表示的范围是 -999.99 ~ 999.99
decimal(5,2) unsigned 表示的范围 0 ~ 999.99
decimal和float很像，但是有区别:
float和decimal表示的精度不一样

插入数据越界测试

decimal 和 float 的区别： float 和 decimal 表示的精度不一样。 float 表示的精度大约是 7 位。（会发生截断或四舍五入） decimal 整数最大位数 m 为 65。支持小数最大位数 d 是 30。 d如果被省略，默认为0。 m如果被省略，默认为是 10。（很大程度保证精度准确）
补充说明：
float是浮点数，占用 4 字节，约 7 位有效数字。存储的是近似值，运算后可能产生微小误差（如 0.1 + 0.2 ≠ 0.3）。
decimal是定点数，存储的是精确的十进制数值，适合金额、体重、身高等需要精确计算的场景。
decimal(10,2)表示总共 10 位数字，其中小数点后 2 位，小数点前 8 位。
在 MySQL 中，如果插入的小数位数超过 d，会进行四舍五入截断（严格模式下会报错）。
性能上 float/double 比 decimal 快，但 decimal 更精确。金融类项目强制使用 decimal。

1-3字符串类型

char

语法：

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

说明：
char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个，最多只能是255

字符编码与 MySQL 中的 char 类型 在 UTF-8 中，一个汉字一般占 3 个字节；在 GBK 中，一个汉字一般占 2 个字节。 注意：char 类型的单位为字符，MySQL 中的"字符"和 C/C++ 中的概念相同。C/C++ 中一个 char 占 1 字节，而 MySQL 中一个字符代表一个符号，其实际占用的字节数取决于字符集。 MySQL 5.7 与 8.0 的区别 在 MySQL 5.7 及更早版本中： 默认字符集是 latin1 使用 utf8 时，实际是 utf8mb3（每个字符最多 3 字节） 无法存储 emoji 和部分生僻汉字 在 MySQL 8.0 中： 默认字符集改为 utf8mb4 utf8mb4 每个字符最多 4 字节，支持完整 Unicode（包括 emoji） 官方推荐新应用使用 utf8mb4 重要提醒 utf8 在 8.0 中仍是 utf8mb3 的别名，但已被标记为废弃 未来版本中 utf8 将变为 utf8mb4 的别名 为避免歧义，创建表时建议显式指定 CHARACTER SET utf8mb4 总结 char(10) 表示能存 10 个字符，能存多少汉字取决于字符集： utf8mb3：最多 10 个汉字，占 30 字节 utf8mb4：最多 10 个汉字，占 40 字节 gbk：最多 10 个汉字，占 20 字节

varchar

语法：

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字

插入数据进行越界测试

1. 为什么 VARCHAR(65536) / VARCHAR(21846) 会报错？
报错 Column length too big ，核心原因是：
MySQL 的 InnoDB 表有一个单行最大数据长度限制：约 65535 字节（注意是字节，不是字符）。
VARCHAR(65536) ：直接超出了这个限制，所以直接报错。
VARCHAR(21846) ：在 UTF-8 编码下，每个字符占 3 字节， 21846 * 3 = 65538 ，超过了 65535 字节的上限，所以也报错。
2. 为什么 VARCHAR(16383) 能成功？
VARCHAR(16383) 在 UTF-8 编码下， 16383 * 3 = 49149 字节，远小于 65535 字节的行限制，所以可以正常创建，没有任何警告。
3. 为什么 VARCHAR(21845) 会有 1 warning ？
21845 * 3 = 65535 ，刚好等于行长度上限。
能创建成功，但 MySQL 会给你一个警告：你把行空间全用完了，没法再加其他字段了。

于varchar(len),len到底是多大，这个len值，和表的编码密切相关：
varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字
节数是65532。
当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占
用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符
占用2字节）。
varchar 的最大长度限制是 65535 字节，不是 65535 个字符。在 utf8mb3 下每个汉字占 3 字节，所以最多 21845 个字符。21846 × 3 = 65538 超出限制，所以创建失败。

char和varchar比较

如何选择定长或变长字符串？

如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
定长的磁盘空间比较浪费，但是效率高。
变长的磁盘空间比较节省，但是效率低。
定长的意义是，直接开辟好对应的空间
变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

1-4日期和时间类型

用的日期有如下三个：
date :日期 'yyyy-mm-dd' ，占用三字节
datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节
timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节

1.date

格式'yyyy-mm-dd'，占用 3 字节。

取值范围：'1000-01-01'到'9999-12-31'
只存储日期，不存储时间
适合存储生日、入职日期、节日等

2.datetime

格式'yyyy-mm-dd HH:ii:ss'，范围 1000~9999，占用 8 字节。

取值范围：'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
存储日期和时间，与时区无关（你存什么就是什么）
适合存储创建时间、活动开始时间等不需要考虑时区的场景
MySQL 8.0 中支持小数秒（微秒精度）

3. timestamp

会自动更新时间戳，格式和datetime一样，占用 4 字节，从 1970 年开始。

详细补充

取值范围：'1970-01-01 00:00:01'UTC 到'2038-01-19 03:14:07'UTC
2038 年问题：因为 TIMESTAMP 底层是 4 字节有符号整数，最大只能表示 2038 年
存储的是从 1970-01-01 00:00:00 UTC 开始的秒数
自动更新特性：配合ON UPDATE CURRENT_TIMESTAMP，每次 UPDATE 时自动刷新
时区敏感：存储时转为 UTC，查询时转为当前时区
适合存储最后登录时间、记录更新时间等场景

你会发现什么，他不会自动刷新时间

MySQL 5.7 默认让第一个 TIMESTAMP 列自动获得自动刷新能力，而 8.0 关闭了这个行为，必须显式指定ON UPDATE CURRENT_TIMESTAMP才能实现

解决方法：

ALTER TABLE t11 ADD COLUMN update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间';

解决刷新问题后

1-5enum 和 set

语法：

enum

enum：枚举，“单选”类型；
enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,....最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

set

set：集合，“多选”类型；
set('选项值1','选项值2','选项值3', ...);

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，....
最多64个。

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。

MySQL 中 ENUM 类型的底层存储机制：ENUM 实际存储的是枚举值对应的数字下标（从 1 开始），而不是字符串本身。因此当向 gender 列（定义为 ENUM('男','女')）插入'1'时，MySQL 会将其解释为下标 1，从而存入'男'；插入'2'得到'女'；插入'3'或'-1'则因为下标越界而报错；而'0'或'-0'会被当作无效下标，在宽松模式下转为空字符串。这就是为什么同样的数字字符串插入结果不同，核心在于 ENUM 的值是以索引位置来判断的。

插入语句（hobby 值）	插入的数字	对应二进制	底层数值（位掩码）	选中的爱好选项	`SELECT *`时的显示结果	`hobby+0`强制转数字结果
`insert ... '1'`	`1`	`001`	`1`	代码	`代码`	`1`
`insert ... '2'`	`2`	`010`	`2`	羽毛球	`羽毛球`	`2`
`insert ... '3'`	`3`	`011`	`3`	代码、羽毛球	`代码,羽毛球`	`3`
`insert ... '4'`	`4`	`100`	`4`	足球	`足球`	`4`
`insert ... '7'`	`7`	`111`	`7`	代码、羽毛球、足球	`代码,羽毛球,足球`	`7`

层原理：SET类型的每个选项，会按定义顺序自动分配一个 2 的幂次数值：
代码 = 1（2⁰）
羽毛球 = 2（2¹）
足球 = 4（2²）
游泳 = 8（2³）
为什么看不到数字：SELECT *时，MySQL 会自动把位掩码解析成对应的选项字符串；只有用+0强制转数字，才能看到原始的数值。

字段	类型	查询语句	`=`的含义	底层逻辑	匹配结果
`gender`	`ENUM('男','女')`	`gender='男'`	字符串值匹配	匹配 ENUM 定义的文本值	所有性别为 “男” 的记录（12 条）
`gender`	`ENUM('男','女')`	`gender=1`	底层索引值匹配	匹配 ENUM 内部的数字索引（`男`对应索引 1）	和`gender='男'`结果完全相同（12 条）
`gender`	`ENUM('男','女')`	`gender='女'`	字符串值匹配	匹配 ENUM 定义的文本值	所有性别为 “女” 的记录（2 条）
`gender`	`ENUM('男','女')`	`gender=2`	底层索引值匹配	匹配 ENUM 内部的数字索引（`女`对应索引 2）	和`gender='女'`结果完全相同（2 条）
`hobby`	`SET('代码','羽毛球','足球','游泳')`	`hobby='羽毛球'`	字符串组合精确匹配	匹配完整的选项字符串	仅匹配 “只选了羽毛球” 的记录（2 条）
`hobby`	`SET('代码','羽毛球','足球','游泳')`	`hobby=3`	底层位掩码精确匹配	匹配 SET 内部的数值（`代码`+`羽毛球`= 1+2=3）	和`hobby='代码,羽毛球'`结果完全相同（1 条）
`hobby`	`SET('代码','羽毛球','足球','游泳')`	`hobby=7`	底层位掩码精确匹配	匹配 SET 内部的数值（`代码`+`羽毛球`+`足球`= 1+2+4=7）	和`hobby='代码,羽毛球,足球'`结果完全相同（1 条）

号在ENUM和SET字段上，既可以匹配显示给用户的字符串值，也可以匹配MySQL 内部存储的数字值，两种写法效果等价，都是精确匹配。

1-6集合查询使用find_ in_ set函数：

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；
str_list 用逗号分隔的字符串

列表必须用英文逗号,分隔，不能用空格、顿号等其他符号；
列表中的每个元素是独立的，只会匹配完整元素，不会做部分匹配；
常用于WHERE条件中，实现多值匹配场景（比如标签、爱好、权限等逗号分隔字段的查询）。

MYSQL 语句	执行结果	解析说明
`select find_in_set('a', 'a,b,c');`	`1`	`'a'`是列表中第 1 个元素，返回位置 1
`select find_in_set('a,b', 'a,b,c');`	`0`	`'a,b'`不是列表中的独立元素（列表里是`a`/`b`/`c`三个元素），返回 0
`select find_in_set('d', 'a,b,c');`	`0`	`'d'`不在列表中，返回 0
`select find_in_set('b', 'a,b,c');`	`2`	`'b'`是列表中第 2 个元素，返回位置 2
`select find_in_set('c', 'a,b,c');`	`3`	`'c'`是列表中第 3 个元素，返回位置 3

案例 1：查询所有爱好包含羽毛球的用户

MYSQL 语句：

SELECT * FROM votes WHERE FIND_IN_SET('羽毛球', hobby);

案例 2：查询同时包含代码和羽毛球的用户

MYSQL 语句：

SELECT * FROM votes WHERE FIND_IN_SET('代码', hobby) AND FIND_IN_SET('羽毛球', hobby)

FIND_IN_SET()vsLIKE：为什么不用LIKE？

很多人会用LIKE '%羽毛球%'来实现类似需求，但它有明显的缺陷，对比一下：

方式	MYSQL 语句	匹配结果	问题
`FIND_IN_SET`	`WHERE FIND_IN_SET('羽毛球', hobby)`	仅匹配包含独立「羽毛球」的记录	精准匹配，无副作用
`LIKE`	`WHERE hobby LIKE '%羽毛球%'`	会匹配「羽毛球」「羽毛球拍」「打羽毛球」等包含子串的记录	可能误匹配，比如爱好是「羽毛球拍」的用户也会被查出来