【MySQL】数据类型

分类	代表类型	核心用途
数值类型	TINYINT, INT, BIGINT, DECIMAL	存储整数、小数、金额
字符串类型	CHAR, VARCHAR, TEXT, BLOB	存储文本、二进制数据
日期时间类型	DATE, DATETIME, TIMESTAMP	存储时间点、时段
空间类型	POINT, LINESTRING, POLYGON	地理坐标、GIS应用
JSON类型	JSON	存储半结构化文档（MySQL 5.7+）

概论

整数类型

类型	字节	有符号范围	无符号范围	典型场景
TINYINT	1	-128 ~ 127	0 ~ 255	状态码、性别、开关
SMALLINT	2	-32,768 ~ 32,767	0 ~ 65,535	小型枚举、评分
MEDIUMINT	3	-8,388,608 ~ 8,388,607	0 ~ 16,777,215	中等范围ID（如日志）
INT	4	-21亿 ~ 21亿	0 ~ 42亿	常规主键ID
BIGINT	8	-9.22×10¹⁸ ~ 9.22×10¹⁸	0 ~ 1.84×10¹⁹	雪花ID、大表主键

关键点：

INT(11) 中的数字不表示存储大小，只表示显示宽度（配合ZEROFILL使用），不影响实际存储。

建议主键优先用 BIGINT（考虑未来数据量）或 INT（数据量可控）。

浮点与定点数

类型	字节	特点	适用场景
FLOAT	4	单精度，约7位有效数字	科学计算，精度要求不高
DOUBLE	8	双精度，约15位有效数字	科学计算，稍高精度
DECIMAL(M,D)	变长	精确存储，每9位数字占4字节	金融、金额、税率（必须用）

核心原则：

金额永远用 DECIMAL，避免浮点精度丢失（如 0.1+0.2 ≠ 0.3）。

DECIMAL(10,2) 表示总共 10 位数字，其中小数2位，整数部分最多8位。

精度过高（如 DECIMAL(30,10)）会消耗更多存储和 CPU，按需设置。

字符串类型

CHAR vs VARCHAR（核心区别）

类型	长度定义	存储方式	尾部空格	适用场景
CHAR(N)	固定 N 个字符	总是分配 N 个字符空间	默认移除	定长数据：MD5、手机号、身份证
VARCHAR(N)	可变，最多 N 个字符	实际长度 + 1~2字节（存储长度）	保留	用户名、标题、描述

性能与空间权衡：

CHAR 适合长度固定的数据（如UUID、MD5），更新时不会产生行迁移（性能更好）。

VARCHAR 适合长度波动大的数据，节省空间，但更新变长时可能引发页分裂。

重要：VARCHAR(N) 中的 N 是字符数，不是字节数。在 utf8mb4 下，一个汉字占3~4字节，所以 VARCHAR(255) 最多可存 255 个字符（但实际受行大小限制）。

TEXT 系列（大文本）

类型	最大长度	存储额外开销
TINYTEXT	255 字节	1字节
TEXT	65,535 字节（约64KB）	2字节
MEDIUMTEXT	16,777,215 字节（约16MB）	3字节
LONGTEXT	4,294,967,295 字节（约4GB）	4字节

关键注意：

TEXT 数据在 InnoDB 中可能存储在外部页（off-page），访问时需额外IO，影响性能。
若经常查询大文本字段（如文章内容），考虑拆分为独立表或使用缓存。
不能在 TEXT 上直接建索引（除非指定前缀长度）。

BLOB（二进制大对象）

与 TEXT 对应，但存储的是二进制数据（图片、音频、视频、序列化对象）。

排序和比较基于字节数值，而非字符集。

强烈不建议在数据库直接存大文件，推荐存 OSS/S3 路径。

日期时间类型（最容易混淆）

类型	字节	范围	精度	时区影响	场景
DATE	3	'1000-01-01' ~ '9999-12-31'	天	否	生日、财报日
TIME	3	'-838:59:59' ~ '838:59:59'	秒	否	时长、时间段
DATETIME	5~8	'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'	微秒（MySQL 5.6+）	否	订单创建时间（业务时间）
TIMESTAMP	4	'1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC	秒	是（自动转UTC）	系统时间戳、日志（含时区转换）
YEAR	1	1901 ~ 2155	年	否	年份单独存储

核心原则（高频面试点）：

TIMESTAMP 受时区影响：存入时转UTC，取出时转当前时区；DATETIME 不做任何转换。

TIMESTAMP 有 2038 年问题（Unix时间溢出），如果系统存远期时间，必须用 DATETIME。

MySQL 5.6 之后 DATETIME 支持微秒（如 DATETIME(3) 精确到毫秒）。

建表规范：推荐用 DATETIME(0) 或 DATETIME(3) 存储业务时间，避免时区歧义；系统字段（如 update_time）可考虑 TIMESTAMP 自动更新。

永远不要用 字符串 或 INT 存日期（无法使用日期函数，无法优化索引）。

JSON 类型（现代应用必备）

核心特性（MySQL 5.7+）

自动校验：插入非法JSON会报错。

高效访问：通过 -> 和 ->> 操作符直接提取字段，无需解析整个文档。

索引支持：可对JSON字段的特定路径创建虚拟列+索引，极大提升查询性能。

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY,
    info JSON
);

-- 插入数据
INSERT INTO users VALUES (1, '{"name":"张三","age":30,"tags":["vip","gold"]}');

-- 查询（返回带引号）
SELECT info->'$.name' FROM users;  -- "张三"

-- 查询（返回纯文本）
SELECT info->>'$.name' FROM users;  -- 张三

-- 为age字段建虚拟索引（加速范围查询）
ALTER TABLE users ADD COLUMN age INT GENERATED ALWAYS AS (info->>'$.age') VIRTUAL, ADD INDEX idx_age(age);

适合场景

属性不固定的扩展字段（如商品自定义属性）。

存储前端传来的复杂嵌套对象（避免拆多张表）。

不适合频繁更新JSON内部某个字段（会整块重写，性能差）。

数值类型

整数类型

类型	字节	有符号范围	无符号范围	典型场景
TINYINT	1	-128 ~ 127	0 ~ 255	状态码、性别、开关
SMALLINT	2	-32,768 ~ 32,767	0 ~ 65,535	小型枚举、评分
MEDIUMINT	3	-8,388,608 ~ 8,388,607	0 ~ 16,777,215	中等范围ID（如日志）
INT	4	-21亿 ~ 21亿	0 ~ 42亿	常规主键ID
BIGINT	8	-9.22×10¹⁸ ~ 9.22×10¹⁸	0 ~ 1.84×10¹⁹	雪花ID、大表主键

以 tinyint 为例介绍以上的数据类型，其他的数据类型可以以此类推。

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

数值越界测试：

mysql> create table tt1(num tinyint);
Query OK, 0 rows affected (0.02 sec)
mysql> insert  into tt1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert  into tt1 values(128); -- 越界插入，报错
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> select * from tt1;
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

如果要插入的数据超过了数据类型的范围，在 c/c++ 可能发生截断，在 MySQL 这里直接拦截不让插入，即： MySQL 如果插入数据成功，数据一定是合法的，所以MySQL的数据类型本身就是一种约束，它使得程序员思考插入的数据是否合法

bit 类型

基本语法

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

说明：

bit类型同样也有约束，当定义的 bit 位数大于大于 64 或插入的数据的 bit 位大于定义时规定的 bit 时，MySQL 同样会拦截
bit 类型在显示的时候，是按照 ASCII 码表对应的字符显示的，bit 类型在插入时，插入的数据也可以写成 ASCII 码对应的字符

小数类型

小数类型有 float 类型、double 类型、decimal 类型，它们的精度依次增加

类型	字节	特点	适用场景
FLOAT	4	单精度，约7位有效数字	科学计算，精度要求不高
DOUBLE	8	双精度，约15位有效数字	科学计算，稍高精度
DECIMAL(M,D)	变长	精确存储，每9位数字占4字节	金融、金额、税率（必须用）

语法

float[(m, d)] [unsigned] : M指定最大位数(整数部分 + 小数部分)，d指定小数位数，占用空间4个字节
double[(m, d)] [unsigned] : M指定最大位数(整数部分 + 小数部分)，d指定小数位数，占用空间4个字节
decimal[(m, d)] [unsigned] : M指定最大位数(整数部分 + 小数部分)，d指定小数位数，占用空间4个字节

说明：

如果插入的数据的小数位数大于定义时规定的小数位数，此时 MySQL 不会拦截，而是会进行四舍五入，但是如果四舍五入后整数和小数位数之和大于定义时规定的位数，MySQL 仍然会进行拦截
unsigned float 类型的数据的取值范围就是 float 类型的取值范围去掉负数部分
不指定 (M, D) 时，FLOAT 就是标准的 4 字节单精度，DOUBLE 就是标准的 8 字节双精度，完全交给硬件处理（等价于 c 语言的 float 类型），不额外限制小数位数。
double 类型的使用与 float 完全一样，只是精度更高
decimal整数最大位数 m 为 65。支持小数最大位数 d 是30。如果 d 被省略，默认为 0.如果 m 被省略，默认是 10。

字符类型

char 类型

语法：

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

注意：

在 MySQL 中，L 表示可以存储的长度，但并不是像 c/c++ 语言那样，比如 "中国" 在 utf8 中总共占 6 字节，在 c/c++ 语言的长度是 6，但是在 MySQL 中 “中国” 的长度是 2，即 MySQL 是真的有几个字符，长度就是几。
可以插入长度小于 L 的字符，但是如果长度大于 L，MySQL 会拦截

mysql> create table tt9(id int, name char(2));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tt9 values(100, 'ab');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt9 values(101, '中国');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt9;
+------+--------+
| id   | name   |
+------+--------+
|  100 | ab     |
|  101 | 中国   |
+------+--------+

varchar 类型

语法：

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

说明：

关于varchar(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字节）

mysql> create table tt10(id int ,name varchar(6)); --表示这里可以存放6个字符
mysql> insert into tt10 values(100, 'hello'); 
mysql> insert into tt10 values(100, '我爱你，中国');
mysql> select * from tt10;
+------+--------------------+
| id   | name               |
+------+--------------------+
|  100 | hello              |
|  100 | 我爱你，中国         |
+------+--------------------+

从上面的使用中，我们没有看出 varchar 和 char 的区别，那么它们的区别是什么？

varchar 和 char 的区别

char 类型是先开辟 L 长度的空间，但你可能只使用了其中的一部分，而 varchar 类型是使用多少就开辟多少，但不要超过最大限度。

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

如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
定长的磁盘空间比较浪费，但是效率高。
变长的磁盘空间比较节省，但是效率低。

日期与时间类型

类型	字节	范围	精度	时区影响	场景
DATE	3	'1000-01-01' ~ '9999-12-31'	天	否	生日、财报日
TIME	3	'-838:59:59' ~ '838:59:59'	秒	否	时长、时间段
DATETIME	5~8	'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'	微秒（MySQL 5.6+）	否	订单创建时间（业务时间）
TIMESTAMP	4	'1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC	秒	是（自动转UTC）	系统时间戳、日志（含时区转换）
YEAR	1	1901 ~ 2155	年	否	年份单独存储

常用的日期有三个： date、datetime、timestamp

注意：

如果某个表包含 timestamp 这一列，那么往后不需要对 timestamp 做任何插入，当创建 timestamp 这一列时，timestamp 自动初始化为当前时间，往后对包含 timestamp 的表的其他列做任何修改时，timestamp 都会自动更新为当前时间，而其他类型并不会更新

//创建表
mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.01 sec)

//插入数据：
mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1'); 
Query OK, 1 row affected (0.00 sec)
mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2017-11-12 18:28:55 | 
+------------+---------------------+---------------------+
                                    t3：添加数据时，时间戳初始化为当前时间

//更新数据：
mysql> update birthday set t1='2000-1-1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2000-01-01 | 2008-08-08 12:01:01 | 2017-11-12 18:32:09 | 
+------------+---------------------+---------------------+
                                    t3：更新数据，时间戳会更新成当前时间

enum 和 set 类型

enum：枚举，“单选”类型；

语法：

enum('选项1','选项2','选项3',...);

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

set：集合，“多选”类型；

语法：

set('选项1','选项2','选项3', ...);

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

注意：

不管是枚举类型还是集合类型，插入的数据如果是字符那么必须是定义时出现的选项，如果不是，MySQL会拦截
对于枚举类型，插入时可以输入定义时字符的下标（从 1 开始）
对于集合类型，插入时也可以输入数字，但这个数字就不是下标了，而是位图，它的每一位从低位到高位与定义集合类型时选项从左到右一一对应,0 表示不插入，1 表示插入。特殊情况：如果输入 0，可以插入成功，但不是 NULL，而是 ‘’。

案例

有一个调查表votes，需要调查人的喜好，比如（登山，游泳，篮球，武术）中去选择(可以多选)，（男，女）[单选]

mysql-> create table votes(
     -> username varchar(30),
     -> gender enum('男','女')); 
     -> hobby set('登山','游泳','篮球','武术'),     
Query OK, 0 rows affected (0.02 sec)

insert into votes values('张三','男','登山,武术');
insert into votes values('Juse',2,'登山,武术');
insert into votes values('李四','男',10); // 10 -> 1010(二进制)，表示插入：'登山','篮球
select * from votes where gender=2;
+----------+---------------+--------+
| username | hobby         | gender |
+----------+---------------+--------+
| Juse     | 登山,武术      |女       |
+----------+---------------+--------+

对 enum 和 set 类型的查询

mysql> select * from votes where hobby='登山';

注意：

对于 set 类型的查询，上面的方式是严格查询，即只会查询到爱好只有“登山”的，不会查询到爱好有“登山”和“武术”的。如果不想严格匹配，就要用 find_in_set 函数：

mysql> select * from votes where hobby=find_in_set('登山',hobby);

匹配条件如果不止一个：

mysql> select * from votes where hobby=find_in_set('登山',hobby) and find_in_set('代码',hobby);