4.MySQL数据类型
文章目录
上一篇笔记我们把库与表的结构操作全部打通：建库删库即建删目录，表结构增删改统一走 alter table，所有结构操作归属 DDL；建表时已经能用上 int、varchar、char、date 等列类型，desc 却只回答"每列是什么类型"，没有解释这些类型的取值规则。本篇解决这个问题：按数值、字符串、日期时间、聚合四类展开 MySQL 常见数据类型的定义与行为。本篇贯穿一个核心认识—— 数据类型本身就是一种约束：列一旦定下类型，能存什么、不能存什么就由 MySQL 强制把关，越界数据会被直接拦截，这也是下一篇正式讲约束的引子。
一、数据类型分类总览
MySQL 与 C++/Java 一样自带一套类型体系，建表时按业务场景为每列选择类型。MySQL 常见数据类型归为四大类：
各类别要点先行交代，详细规则在对应章节展开：
- 数值类型：整型按字节分档，默认有符号，可显式声明 unsigned；BOOL 以 0 和 1 表示真假；bit 是位字段；float/double 是浮点（有精度损失），decimal 是精确的定点数。
- 文本、字符串与二进制：char 定长、varchar 变长，是本篇重点；text 存大文本、blob 存二进制数据。
- 日期与时间：date 只记日期，datetime 记日期加时间，timestamp 是能随行数据自动刷新的时间戳。
- 聚合类型：enum 与 set 把列取值限定在预先声明的选项里，enum 单选、set 多选，属于课件归类的 String 型（存的是字符串）。
二、数值类型（一）：整型家族
整型按占用字节数分档：tinyint 占 1 字节、smallint 占 2 字节、mediumint 占 3 字节、int 占 4 字节、bigint 占 8 字节，每种类型的字节数与取值范围都由 MySQL 内部规定。只写类型名不写unsigned修饰时默认是有符号的，取值范围与 C/C++ 对应宽度的整型完全一致（tinyint 即对应 C/C++ 的 char）。完整范围见下表：
| 类型 | 字节 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| tinyint | 1 | -128 到 127 | 0 到 255 |
| smallint | 2 | -32768 到 32767 | 0 到 65535 |
| mediumint | 3 | -8388608 到 8388607 | 0 到 16777215 |
| int | 4 | -2147483648 到 2147483647 | 0 到 4294967295 |
| bigint | 8 | -9223372036854775808 到 9223372036854775807 | 0 到 18446744073709551615 |
想要无符号，在类型后追加 UNSIGNED 关键字，取值范围变为从 0 起的正区间。以 tinyint 为例建表实测（演示建在 test_db 库，字符集默认 utf8）：
mysql> create table tt1(num tinyint); -- 不写 unsigned，默认有符号：-128 到 127
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tt1 values(-128), (127); -- 两个边界值都能插入
Query OK, 2 rows affected (0.00 sec)
mysql> insert into tt1 values(128); -- 超出上限
ERROR 1264 (22003): Out of range value for column 'num' at row 1
换成无符号列，负数与超界值同样被拦：
mysql> create table tt2(num tinyint unsigned); -- 无符号：0 到 255
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tt2 values(0), (255);
Query OK, 2 rows affected (0.00 sec)
mysql> insert into tt2 values(-1); -- 无符号列里负数也是越界
ERROR 1264 (22003): Out of range value for column 'num' at row 1
插入的值超出类型取值范围时，MySQL 直接报错（ERROR 1264）拒绝插入。
三个补充认识：
- 列定义顺序：列名在前、类型在后（如
num tinyint），与 C/C++"类型 变量名"的写法相反；有符号/无符号修饰跟在类型后面。 - unsigned 不解决"存不下"的问题。若 int 都放不下的数据，int unsigned 通常同样放不下，与其用 unsigned 硬撑，不如直接把类型提升为 bigint。能否用 unsigned 取决于场景——账户余额、年龄这类天然不应为负数的列，用 unsigned 表达"非负"语义是合理的。
- 选类型要在场景与空间之间取平衡。比如年龄列用 tinyint unsigned（0 到 255）绰绰有余，不必上 int 或 bigint；一条记录多占 7 字节看似不起眼，放大到 1000 万行、上亿行就是可观的空间浪费。不建议无脑选最大类型，够用即可，把空间留给真正需要它的列。
三、越界拦截与"数据类型即约束"
为什么 MySQL 对越界如此"不近人情"？与 C/C++ 对比就清楚了：向 C/C++ 的 char 里塞 129，语言层面通常只给个告警甚至一声不吭，超出的部分被截断或隐式转换掉，数据已经悄悄变了。MySQL 的选择截然相反：
- MySQL 不允许截断。一旦允许截断，库里的数据就真假混杂，使用者无法信任查询结果；反过来，凡是成功插入的数据必然合法——插入被拦下的场景里，库里已有的每一行都经得起类型的检验。
- 数据类型是一种约束，约束的对象是使用者。数据库假定使用者可能随意操作，类型把关保证只有合规数据能进库；这不只是保护库，也是倒逼程序员插入前先想清楚取值边界。对类型不熟练的使用者，数据库也不会因乱插而坏掉——非法数据进不来。
- 约束带来可预期性。tinyint 列里的数据一定落在 -128 到 127 之间，不会出现 -300、+500；数据没有经过截断或隐式转换，形态完整。这种"库中数据永远符合列声明"的可预期性，是上层业务正确性的地基。
类型约束只是 MySQL 约束体系的第一块敲门砖：表上还有主键、外键、not null、default 等更多约束，下一篇专门展开。本篇先把类型这条天然约束的体验做实。
四、数值类型（二）：bit 位类型
bit 是位字段类型，书写时大小写不敏感。语法 bit(M)：M 表示位数，取值范围 1 到 64，省略时默认为 1。它适合只存二值状态的列（如用户是否在线、性别 0/1），1 个比特位即可表达，空间最省。建表演示：
mysql> create table tt4(id int, a bit(8));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tt4 values(10, 10);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tt4;
| id | a |
| 10 | | -- 奇怪：插入的 10 没显示出来
bit 列显示时按 ASCII 码对应的字符输出：10 对应的 ASCII 是不可见字符，所以看起来"没有数据"。插入 65 验证，65 对应字符 ‘A’，立刻显示出来：
mysql> insert into tt4 values(65, 65);
mysql> select * from tt4;
| id | a |
| 10 | |
| 65 | A | -- 65 按 ASCII 显示为字符 A
数据确实存进去了，只是显示成字符。想按十进制看位里存的数值，把 bit 列放进算术表达式即可，例如 select id, a+0 from tt4 会输出 10 与 65。bit 列本身有位数约束，超宽即拦，看性别列的经典用法：
mysql> create table tt5(gender bit(1)); -- 1 位：只能存 0 或 1
mysql> insert into tt5 values(0);
mysql> insert into tt5 values(1);
mysql> insert into tt5 values(2); -- 2 超出 1 个比特位可表达的范围
ERROR 1406 (22001): Data too long for column 'gender' at row 1
小结 bit 的三条规则：位宽 1 到 64，超 64 建表直接报错；插入按二进制位存储，超出位宽的值进不来（约束）；显示默认按 ASCII 码，需要数值时用算术或进制函数转换。
五、数值类型（三）：float 与 double
浮点类型有三种：float、double、decimal。float 语法 float(M, D)：M 表示数字总位数，D 表示小数位数，占用 4 字节。以课件演示的 float(4,2) 为例，M=4、D=2，整数部分最多 M-D=2 位，因此有符号范围是 -99.99 到 99.99；float(6,3) 的整数部分则是 3 位，规则可类推。double 占 8 字节、精度比 float 高，行为一致，自行验证。
越界拦截与四舍五入
float(4,2) 建表插入，看"多一点"的数据如何处理：
mysql> create table tt6(id int, salary float(4,2));
mysql> insert into tt6 values(100, -99.99);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt6 values(101, -99.991); -- 小数超了 1 位
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt6;
| id | salary |
| 100 | -99.99 |
| 101 | -99.99 | -- -99.991 被四舍五入为 -99.99
浮点插入遵循"先四舍五入、再查越界"：
- 合法范围内多给的小数位被四舍五入：-99.991 舍入为 -99.99，99.994 舍入为 99.99，照常插入。
- 舍入结果越界则拒绝：如 99.995 舍入后变成 100.00，超出 99.99 上限，即使原值"看起来接近边界"也插不进去。四舍五入不是无条件进行的，只在结果仍落在类型范围内时才生效。
- 整数部分超位同样被拦：float(4,2) 的整数部分最多 2 位，插入 999 一类三位整数直接报错。
再看无符号浮点。float 默认有符号（浮点数的二进制首位本就是符号位），但 MySQL 允许定义 unsigned 浮点，范围砍掉负数部分：
mysql> create table tt7(id int, salary float(4,2) unsigned); -- 范围 0 到 99.99
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tt7 values(100, -0.1);
Query OK, 1 row affected, 1 warning (0.00 sec) -- 课件环境：负数越界只产生 warning，未报错
具体行为与 sql_mode 配置相关。设计表结构时不要依赖这种放行，按"越界值不可信"来约束程序即可。
精度损失是浮点的本性
float 的存储方式决定它天生丢精度：任何浮点数在机器里都按科学计数法拆成符号位、指数位与尾数位存储，十进制转二进制时，整数部分除二取余、小数部分乘二取整，小数部分几乎不可能被精确表示完，总有一部分精度在转换中丢失。因此：
- float 的有效精度大约 7 位（十进制有效数字），double 约 15 到 16 位；超过的部分不被信任。
- 不只是小数，大整数一样会丢。
- 对精度敏感的数据（金额、计量值），float/double 都不合适，用下一节的 decimal。
六、数值类型（四）：decimal 定点数
decimal 语法 decimal(M, D)：M 总位数、D 小数位数，同样可带 unsigned。它和 float 最大的区别在存储方式：decimal 是定点精确存储，“怎么存进去就怎么取出来”，没有 float 的精度损失。decimal(5,2) 有符号范围是 -999.99 到 999.99（整数 3 位），unsigned 为 0 到 999.99。
同一张表放一个 float 列和一个 decimal 列，插入完全相同的数据，对比回读结果：
mysql> create table tt8(id int, salary float(10,8), salary2 decimal(10,8));
mysql> insert into tt8 values(100, 23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt8;
-- 回读结果：salary（float）与原始值 23.12345612 出现偏差（有效精度约 7 位，尾部失真）；
-- salary2（decimal）与原始值完全一致。
decimal 的边界参数：M 最大 65，D 最大 30；D 省略默认 0，M 省略默认 10。选型结论一句话：精度要求不高用 float/double（省空间、速度快），精度要求高用 decimal——典型如银行金额，money 类数据一律 decimal。
decimal 精确的关键在于"把小数当整数存"：D 已在建表时固定小数位，存储时先去掉小数点（19.99 → 1999，小数点位置记在列元数据里），再按每 9 位十进制数字一组、把每组当作 0~999999999 的整数装入 4 字节（10⁹ < 2³²）。
七、字符串类型（一）：char 定长字符串
char 是固定长度字符串，语法 char(L)：L 表示可存储的字符个数上限，最大 255。先厘清一个关键概念，MySQL 里的"字符"与 C/C++ 里的"字符"不是一回事：
- C/C++ 的字符对应 1 个字节；MySQL 的字符对应一个"符号"——字母、数字、汉字都算 1 个字符。
- 一个汉字在 utf8 编码下占 3 个字节，但作为 MySQL 字符仍只算 1 个（gbk 下占 2 字节、算 1 字符）。
- 所以 char(2) 能放下 “ab”，也能放下两个汉字的 “中国”（6 个字节），但放不下 3 个字符。
建表实测：
mysql> create table tt9(id int, name char(2)); -- 最多 2 个字符
mysql> insert into tt9 values(100, 'ab');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt9 values(101, '中国'); -- 2 个汉字 = 2 个字符，允许
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt9 values(102, 'abc'); -- 第 3 个字符：越界，被拒（课堂演示报 Data too long）
char 的 255 上限同样建表时把关，超了连表都建不成：
mysql> create table tt10(id int, name char(256));
ERROR 1074 (42000): Column length too long for column 'name' (max = 255);
use BLOB or TEXT instead
错误信息顺带引出两个没细讲的类型：超长文本用 text（大文本），二进制数据用 blob。char 的分配语义是"空间按声明一次性开好，用多少由内容决定，分配多少由声明决定"：char(2) 声明 2 个字符，就按 2 个字符的空间分配，与列里实际存了几个字符无关。
八、字符串类型（二）：varchar 变长字符串
varchar 是可变长度字符串，语法 varchar(L)，名字里的 L 常被误当成"最多 65535 个字符"——准确说法是：varchar 的数据最长 65535 字节（不是字符），而且这个上限受整行 65535 字节的行大小限制约束。它和 char 的分配语义相反：上限之内用多少字符就分配多少空间，但声明多大只是上限。先看变长的直观行为（课件 tt10，varchar(6) 存 6 个字符）：
mysql> create table tt10(id int, name varchar(6)); -- varchar(6)：6 个字符上限（同名建表曾失败，名字可复用）
mysql> insert into tt10 values(100, 'hello');
mysql> insert into tt10 values(100, '我爱你，中国'); -- 逗号也算 1 个字符，恰好 6 个
mysql> insert into tt10 values(101, 'abcdefg'); -- 第 7 个字符：越界被拒
变长是怎么实现的？varchar 要额外用 1 到 2 个字节记录"实际存了几个字节"：内容最长不超过 255 字节时，1 个字节足以记录长度；可能超过 255 字节时，长度要用 2 个字节。推算行上限时按最坏情况预留 2 个字节，账就算得过来：
整行可用空间 = 65535 字节（含长度字节）：先扣除记录长度的字节（剩 65533），再除以单字符字节数并向下取整，即为字符上限。utf8 一个字符 3 字节，单列 varchar 上限是 65533 / 3 = 21844（取整）；gbk 一个字符 2 字节，上限是 65533 / 2 = 32766（取整）。验证：
mysql> create table tt11(name varchar(21845)) charset=utf8; -- 21845 × 3 = 65535，再加长度字节超限
ERROR 1118 (42000): Row size too large. The maximum row size for the used table
type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql> create table tt11(name varchar(21844)) charset=utf8; -- 21844 × 3 + 2 = 65534，不超过 65535
Query OK, 0 rows affected (0.01 sec)
要记住：
21844 是"单列、无其他字段"的极限。行大小是整行总账：同一行还有其他列（如 int 占 4 字节）时，varchar 能用的空间更小，实际允许的字符数要按行内剩余空间重算。
类比C++ ：char 像定长数组（char[6] 一次开 6 格），varchar 像 C++ 的 string。
定长 char 不管内容多短都占满声明空间，换来的是简单高效：空间一次开好、无需长度计数器；变长 varchar 用多少占多少、省空间，代价是每次存取都要维护长度信息，管理成本更高。
九、日期与时间类型
先区分三个概念：日期是年月日，时间是时分秒，日期时间是年月日时分秒。MySQL 常用三个类型对应三种粒度：
| 类型 | 字节 | 内容与格式 | 行为 |
|---|---|---|---|
| date | 3 | 日期，如 2026-09-03 | 只记年月日 |
| datetime | 8 | 日期加时间，如 2026-09-03 08:30:00，范围 1000 到 9999 | 由程序插入固定时刻，不自动变化 |
| timestamp | 4 | 显示格式与 datetime 相同，从 1970 年开始计 | 行被插入或更新时自动刷新为当前时间 |
timestamp 与 C 语言里以秒计数的 time_t 时间戳不同：MySQL 的 timestamp 列直接以可读的日期时间格式呈现，并带自动维护能力：
mysql> create table birthday(t1 date, t2 datetime, t3 timestamp);
mysql> insert into birthday(t1, t2) values('1997-7-1', '2008-8-8 12:1:1');
-- 只插了 t1、t2，t3 留空
mysql> select * from birthday;
| t1 | t2 | t3 |
| 1997-07-01 | 2008-08-08 12:01:01 | 2017-11-12 18:28:55 | -- t3 自动补为插入时的当前时间
插入行时 timestamp 列不填也自动记录当前时间；此后更新该行的任何字段，timestamp 都会再次刷新为新的当前时间：
mysql> update birthday set t1='2000-1-1'; -- 只改了 t1
mysql> select * from birthday;
| t1 | t2 | t3 |
| 2000-01-01 | 2008-08-08 12:01:01 | 2017-11-12 18:32:09 | -- t3 跟随更新自动刷新
按场景选型，三个类型各有用武之地：
- 需要记录"最后修改时间"的字段用 timestamp。
- 需要固定登记时刻的字段用 datetime。
- 只需日期的字段用 date。
十、聚合类型（一）：enum 与 set 的定义与插入
enum（枚举）与 set（集合）把列取值限制在预先声明的一组选项中，是"带选项的类型"。两者语义不同：enum 单选（如性别男女），set 多选（如爱好可勾多项）。出于效率考虑，选项并不以字符串原样存储，而是存数字：
| 类型 | 语义 | 底层存储 | 选项上限 |
|---|---|---|---|
| enum | 单选，存选项下标 | 下标从 1 开始依次编号（1、2、3…），存的是这个下标 | 最多 65535 个选项 |
| set | 多选，存位图 | 每个选项占一个比特位，对应数值 1、2、4、8…，同时选多项则存这些数值之和 | 最多 64 个成员 |
set 的位图思路与 Linux 权限一致：权限把 rwx 编码成 4、2、1 三个权值相加，set 把每个选项映射成一个比特位，组合用按位相加表达。一个调查表案例同时用上两种类型：
create table votes(
username varchar(30),
hobby set('登山', '游泳', '篮球', '武术'), -- 多选：每项一个比特位
gender enum('男', '女') -- 单选：男=1，女=2
);
插入字面值，以及枚举的下标写法（Juse 用下标 2 表示女）：
mysql> insert into votes values('雷锋', '登山,武术', '男'); -- set 多选用逗号分隔多个值
mysql> insert into votes values('Juse', '登山,武术', 2); -- enum 可用下标插入：2 对应'女'
mysql> select * from votes;
| username | hobby | gender |
| 雷锋 | 登山,武术 | 男 |
| Juse | 登山,武术 | 女 | -- 下标 2 存入，显示仍为字面值'女'
enum 的下标规则：从 1 开始，每个选项对应一个序号，插入时可写字面值也可写序号；0、负数与超出选项数的序号都进不去。set 的数值插入则是位图语义，以下表为例：
| 成员 | 位图数值 | 4 位二进制示意 |
|---|---|---|
| 登山 | 1 | 0001 |
| 游泳 | 2 | 0010 |
| 篮球 | 4 | 0100 |
| 武术 | 8 | 1000 |
按位图理解数值的含义：‘登山,武术’ 存储值为 1 + 8 = 9；数值 3（0011）表示登山加游泳；0 表示空集；4 个选项全选是 15（1111）。位图里比特位从低位到高位依次对应声明顺序中的第 1、2、3…个成员，某位为 1 表示选中该成员。enum 的下标是"数组式"的（1、2、3 对应第 1、2、3 个选项），set 的数值是"位图式"的（1、2、4、8 对应第 1、2、3、4 个比特位），两者机制不同，不要混淆。
两点使用注意：
- 插入推荐写字面值，不推荐写数字；
- 空集与 NULL 是两回事：set 列存数值 0 时显示为空串，表示"选过、但一个都没选"；NULL 表示"没填过"。
十一、聚合类型（二）：enum 与 set 的查询
给 votes 表补了几行数据（同名 LiLei 出现两次仅为构造演示数据），表中数据如下：
| username | hobby | gender |
|---|---|---|
| 雷锋 | 登山,武术 | 男 |
| Juse | 登山,武术 | 女 |
| LiLei | 登山 | 男 |
| LiLei | 篮球 | 男 |
| HanMeiMei | 游泳 | 女 |
enum 是单选列，查询直接按字面值或按下标都行——存储层面 gender 就是下标，按数字筛等于按存储值筛：
mysql> select * from votes where gender=2; -- 等价于 gender='女'
| username | hobby | gender |
| Juse | 登山,武术 | 女 |
| HanMeiMei | 游泳 | 女 |
set 是组合值，查询麻烦在这里：等值匹配要求列值与查询串完全一致。想找所有喜欢登山的人，直接 where hobby=‘登山’：
mysql> select * from votes where hobby='登山';
| username | hobby | gender |
| LiLei | 登山 | 男 |
雷锋、Juse 的 hobby 是 ‘登山,武术’，不等于 ‘登山’，被漏掉了——他们明明也喜欢登山。等值匹配只能命中"只勾了登山这一项"的人，这通常不是查询意图。要表达"包含"语义，用 MySQL 的 find_in_set 函数：
find_in_set(str, list)：判断 str 是否在逗号分隔的列表 list 中；在则返回它的位置（下标从 1 开始），不在返回 0。
mysql> select find_in_set('a', 'a,b,c'); -- a 在第 1 位
| 1 |
mysql> select find_in_set('d', 'a,b,c'); -- d 不存在
| 0 |
函数返回 0 或非 0，天然可作 where 的条件：返回非 0 表示命中（下标恰好非 0），返回 0 表示不命中——这也是 enum、set 的下标都从 1 开始的原因，方便把"返回下标"直接当"真假"用。包含查询写为：
mysql> select * from votes where find_in_set('登山', hobby);
| username | hobby | gender |
| 雷锋 | 登山,武术 | 男 |
| Juse | 登山,武术 | 女 |
| LiLei | 登山 | 男 |
三个喜欢登山的人都出来了。要求"同时包含多个爱好"，用 and 连接多个 find_in_set 条件（where 相当于语言里的条件判断，函数结果可用逻辑运算组合）：
-- 同时喜欢登山和武术的人
select * from votes where find_in_set('登山', hobby) and find_in_set('武术', hobby);
返回雷锋与 Juse 两行。判断"是否含有某成员"一律 find_in_set，不要用等值匹配；等值匹配只用于"恰好选了这一组"的场景。
总结
本篇把 MySQL 的常见数据类型按四类过了一遍，核心认识只有一个：数据类型是 MySQL 的第一道约束，列的类型即列的取值承诺——满足范围的数据放行，越界的数据拦截，库中数据因此完整、可预期。数值类：整型按 1 到 8 字节分档、默认有符号、unsigned 取非负区间、越界报 ERROR 1264；bit 是 1 到 64 位的位字段，按 ASCII 显示，适合二值状态；float 四舍五入后仍越界即拒，有效精度约 7 位，精度敏感场景换 decimal（M 最大 65、D 最大 30），double 介乎其间、自行验证。字符串类：char(L) 定长、上限 255 个字符（字符是符号不是字节），varchar(L) 变长、受整行 65535 字节限制，utf8 下单列上限 21844，长度稳定用 char、起伏大用 varchar。日期时间类：date 记日期、datetime 记固定时刻、timestamp 随行自动刷新。聚合类：enum 单选按下标存、set 多选按位图存（值 1、2、4、8…），包含查询用 find_in_set。mediumint、text、blob 等未展开的类型思路一致，可照本篇方法自查。下一篇将正式展开约束体系：类型之外的 not null、default、主键外键如何继续为表的合法性把关。
转载自 CSDN-专业IT技术社区
原文链接：https://blog.csdn.net/feng_qi0325/article/details/166254825
