四.MySQL数据类型-CSDN博客

数据类型分类

一.数值类型

1.tinyint类型

MySQL 整数类型范围（有符号 / 无符号）

类型	字节	最小值（有符号/无符号）	最大值（有符号/无符号）
`TINYINT`	1	-128 / 0	127 / 255
`SMALLINT`	2	-32,768 / 0	32,767 / 65,535
`MEDIUMINT`	3	-8,388,608 / 0	8,388,607 / 16,777,215
`INT`	4	-2,147,483,648 / 0	2,147,483,647 / 4,294,967,295
`BIGINT`	8	-9,223,372,036,854,775,808 / 0	9,223,372,036,854,775,807 / 18,446,744,073,709,551,615

mysql> create table ttl(num tinyint); num字段数据类型为tinyint 范围-128~127

在数据类型后加上unsigned 表示无符号

2.bit类型

bit(M)

M：表示位数（bit 数），范围是 1 ~ 64；

如果不写 M，默认是 BIT(1)；

存储时按位存放，但查询时按二进制数值处理。

新增了online 字段类型为bit(2) 也就是只有两个bit大小的存储空间 00 ,只能表示0~3的数，

显示的时候以16进制进行显示。

bit(64) 有64bit位大小的空间以16进制进行显示

3.浮点数类型

1.float类型

float(M,m) unsigned

M代表有几位数(整数位数+小数位数)

m代表小数位数 (四舍五入)

eg. float(4,2)代表4位数有两位小数可表示的范围-99.99~99.99 可写入的范围-99.994~99.994

unsigned 无符号不能表示负数范围0~99.99 0~99.994

float()默认范围：

存储长度是 4字节（32位）；

能表示约 7 位十进制有效数字（总位数）；

小数位数不固定，精度是近似值，非精确。

2.decimal类型

简单来说decimal存储的数据更精准

对比点 FLOAT DECIMAL
精度约 7 位有效数字（近似）最多 65 位整数 + 30 位小数（精确）
有无误差 ✅ 存在舍入误差 ❌ 无误差（高精度）
示例插入插入 123.456789 → 123.457 插入 123.456789 → 保留原值

对比点	`FLOAT`	`DECIMAL`
精度	约 7 位有效数字（近似）	最多 65 位整数 + 30 位小数（精确）
有无误差	✅ 存在舍入误差	❌ 无误差（高精度）
示例插入	插入 `123.456789` → 123.457	插入 `123.456789` → 保留原值

二.字符串类型

1.char类型

char(M)

M指的是字符串中字符的个数而不是字符串占的字节大小。

eg.char(2)表示：最多可以存 2 个“字符”，而不是 2 个字节。

注意：char 的范围0~255 最多255个字符

2.varchar类型

varchar(L): 可变长度字符串，L表示最多可以存多少字符(和char不同的是 varchar是存多少字符就用多少字符的空间)，最大长度65535个字节。

注意是字节而不是字符数:

关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：

编码格式每字符占用头部开销（长度标识）最大 L 值估算
utf8 3 字节 1~2 字节 65533 / 3 = 21844 字符以内
utf8mb4 4 字节最坏预留 2 字节 65533 / 4 = 16383 字符以内
gbk 2 字节 1~2 字节 65533 / 2 = 32766 字符以内
latin1 1 字节最多2字节最大约 65533 个字符

对比项 char(n) varchar(n)
是否定长 ✅ 是，始终为 n 个字符 ❌ 否，存多少用多少（+1~2 字节额外）
存储空间固定长度，空白用空格补足 实际字符长度 + 长度标记（1或2字节）
最大长度 255 个字符（固定） 65535 字节（字符数依编码而异）
性能读写快，适合频繁查询（不涉及变长）写入高效，空间节省，但查询稍慢
是否节省空间 ❌ 不节省，浪费空间 ✅ 节省空间
是否自动截断 ✅ 超过会被截断（有警告） ✅ 超过也会截断（有警告）
使用场景建议身份证号、手机号、固定码、MD5等用户名、地址、留言、标题等变长内容
字符串长度n<=255 可以用1字节表示
255<n<=65535 用2字节表示所以基本上2字节大小的空间就够记录字符串长度

为什么会出错？

表中已有字段（如 id、_var）+ 元数据，合计超出65535限制

编码格式	每字符占用	头部开销（长度标识）	最大 L 值估算
`utf8`	3 字节	1~2 字节	`65533 / 3 = 21844` 字符以内
`utf8mb4`	4 字节	最坏预留 2 字节	`65533 / 4 = 16383` 字符以内
`gbk`	2 字节	1~2 字节	`65533 / 2 = 32766` 字符以内
`latin1`	1 字节	最多2字节	最大约 65533 个字符

对比项	`char(n)`	`varchar(n)`
是否定长	✅ 是，始终为 n 个字符	❌ 否，存多少用多少（+1~2 字节额外）
存储空间	固定长度，空白用空格补足	实际字符长度 + 长度标记（1或2字节）
最大长度	255 个字符（固定）	65535 字节（字符数依编码而异）
性能	读写快，适合频繁查询（不涉及变长）	写入高效，空间节省，但查询稍慢
是否节省空间	❌ 不节省，浪费空间	✅ 节省空间
是否自动截断	✅ 超过会被截断（有警告）	✅ 超过也会截断（有警告）
使用场景建议	身份证号、手机号、固定码、MD5等	用户名、地址、留言、标题等变长内容

3.日期和时间类型

类型	格式	占用空间	表示范围	默认行为	作用与应用场景
date	`YYYY-MM-DD`	3 字节	1000-01-01 到 9999-12-31	无默认值	纯日期，如生日、注册日、节假日等
`datetime`	`YYYY-MM-DD HH:MM:SS`	8 字节	1000-01-01 00:00:00 ~ 9999年	手动赋值	记录完整时间点，如下单、登录时间等
`timestamp`	`YYYY-MM-DD HH:MM:SS`	4 字节	1970-01-01 ~ 2038年	默认自动记录当前时间	系统日志、更新时间、创建时间戳等

date保存日期 datetime保存日期+时间 timestamp也是日期+时间但如果对表中insert into插入新行 或者 update更新除timestamp的其它字段会自动更新。

但为什么下面没有进行自动更新呢？

在创建timestamp字段时必须指明default current_timestamp

_timestamp timestamp default current_timestamp;

加上这个可以看到在插入新行时 timestamp自动刷新了

update更新其它字段的值为什么没有自动刷新？

因为要在此基础上再加上on update current_timestamp，这样才可以在更新其它字段的值时 timestamp才会自动刷新。

timestamp字段更新为default current_timestamp on update current_timestamp

简单总结timestamp字段：

设置语句插入时是否自动填当前时间更新其他字段时是否自动刷新时间
无任何设置 ❌ 否 ❌ 否
default current_timestamp ✅ 是 ❌ 否
on update current_timestamp ❌ 否 ✅ 是
default current_timestamp on update current_timestamp ✅ 是 ✅ 是

设置语句	插入时是否自动填当前时间	更新其他字段时是否自动刷新时间
无任何设置	❌ 否	❌ 否
`default current_timestamp`	✅ 是	❌ 否
`on update current_timestamp`	❌ 否	✅ 是
`default current_timestamp on update current_timestamp`	✅ 是	✅ 是

4.enum和set

类型	含义	存储机制	最大选项数量	特点	应用场景
`enum`	枚举类型（单选）	存储为数字下标	最多 65535 个选项	每行只能存一个值	性别、状态、分类字段
set	集合类型（多选）	存储为位图组合	最多 64 个选项	每行可存多个值（逗号分隔）	兴趣标签、权限字段

enum 从多个选项选一个可以通过字符串直接匹配或者通过数字(下标，下标从1开始)

set 从多个选项选多个 '选项1 , 选项2 , ... '或者通过数字(数字代表的是位图 7->000111 前三个选项 )可以输入0表示什么都不选为空。

注意：enum set可以不输入值 mysql> insert into t5 () values ();

在表中表示的就是 NULL NULL

空!=NULL 空代表输入0什么都不选 NULL表示没输入

set集合查询find_ in_ set函数

从表中找出包含动漫的行怎么找？

select * from 表 where 字段=值，但这样是精确查找也就是说只会查找只包含动漫的行。

要找set字段中包含动漫的行 select * from 表 where find_in_set(值,set字段);

找同时包含动漫小说的行怎么办?

and 两个find_in_set的结果交集

select * from 表 where find_in_set(值,set字段) and find_in_set(值,set字段);