说个实话，干后端这些年，面试过不少人，也带过不少新人。聊到MySQL，十个人里有八个能把索引、事务、锁说得头头是道，但一落到建表，随手就是varchar(255)一把梭，金额用float，状态用varchar存中文。等到数据量上来、慢查询出现、对不上账的时候，才回过头来查数据类型的问题。MySQL的数据类型看着简单，无非就是数值、字符串、日期那几类，但选错了，轻则多占磁盘空间，重则索引失效、精度丢失、甚至整个库的性能被拖垮。
这篇东西不打算讲教科书式的定义，我把这些年建表踩过的坑、调优时排查过的案例、面试里常被问到的细节，全部揉碎了整理出来。内容包括每种类型的底层存储逻辑、适用场景、边界坑点，以及一套可以直接照抄的选型思路。不管是刚入门想搞懂int和bigint区别的新手，还是想系统梳理一遍、避免线上事故的老手，这篇都值得花十分钟读完。
1. 数值类型：别小看这几个数字
数值类型是所有表里用得最多的，也是问题最多的地方。很多人只记得int能存10位数字，但问到int(11)里的11是什么意思，能答对的人不多。这部分把整数、小数、布尔相关的类型一次说透。
1.1 整数类型：范围、字节数与显示宽度的真相
MySQL的整数类型一共有tinyint、smallint、mediumint、int、bigint五种，区别只在于存储字节数和能表示的数值范围。
| 类型 | 字节数 | 有符号范围 | 无符号范围 | 常见用途 |
|---|---|---|---|---|
| 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 | -9.22×10^18 ~ 9.22×10^18 | 0 ~ 1.84×10^19 | 大ID、雪花ID、金额*100 |
这里有个经典误区：int(11)里的11是显示宽度，不是存储上限。它只在设置了zerofill属性时，配合前导零补位展示用，对存储的数值范围没有任何影响。你写int(1)和int(11)，能存的最大值都是 2147483647。MySQL 8.0 已经弃用了显示宽度语法，建表时不要再写int(11)这种老古董写法了。
选择整数类型就一个原则：预估十年内的量级，选刚好够用且最小的。主键如果走自增，预估单表不超过20亿行就够用int unsigned，否则直接bigint。状态字段用tinyint就够，别用int，省下的空间虽然单行不多，但几千万行下来差距就很明显。我见过一张十亿行的流水表，状态字段从int改成tinyint，光这一列就省了几个G。
1.2 小数类型：float和double的精度陷阱，以及decimal的正确用法
小数类型是重灾区，尤其是涉及钱的场景。
float和double是浮点数，底层用二进制近似存储，天生就有精度误差。经典例子：
SELECT 0.1 + 0.2; -- MySQL 8.0 中返回 0.3，但这是显示层的四舍五入 -- 实际存储值并未精确等于 0.3很多语言里0.1 + 0.2 != 0.3，MySQL的浮点运算同样存在这个问题。如果你用float存金额，累计几万笔订单后对账出现几分钱的差异，就是精度误差累积的结果。
decimal是定点数，以字符串形式存储，按十进制精确计算，不存在精度丢失。语法是decimal(M, D)，M代表总位数（最大65），D代表小数位数。比如decimal(10, 2)代表总共10位数字，其中小数占2位，整数部分占8位，最大能存 99999999.99。
涉及金额、利率、百分比，一律用decimal，没有任何商量余地。float和double只适合用在不需要精确计算的科学计算、经纬度坐标展示、或者某个量大但精度要求不高的评分字段。
这里有个隐藏坑：decimal的运算速度比float慢，但在现代硬件和合理索引下，这个差距在绝大多数业务场景里可以忽略。为了那零点几毫秒的性能去牺牲金额精度，是典型的捡芝麻丢西瓜。
1.3 无符号、布尔与自增主键的特殊细节
无符号unsigned在一些建表语句里很常见。我的经验是：除非有明确理由，否则别用无符号。原因很简单，有符号和无符号的字段在join时如果一方有符号一方无符号，可能导致隐式转换，索引失效的坑非常隐蔽。而且多数业务场景用有符号加逻辑判断就足够了，负值可以在数据异常时起到警示作用，比如库存扣成负数，一眼就能发现问题。
布尔类型在MySQL里没有单独的boolean，实际上用的是tinyint(1)。这个(1)同样是显示宽度，不是取值范围限制，你往tinyint(1)里存 200 是完全合法的。业务层面建议约定只存 0 和 1，代码层面用枚举或布尔类型做映射，不要直接往数据库里写 2、3 这种未定义的值。
自增主键有个容易忽略的点：一旦接近类型的上限，自增会报Duplicate entry错误，而不是自动扩容。所以核心表的主键，初期就要想清楚量级。预算超过二十亿行，直接bigint unsigned，别用int硬撑。
2. 字符串类型：字节、字符与排序规则的复杂账
字符串类型看起来只是char和varchar的区别，但深挖下去，字符集、排序规则、行溢出、索引长度限制，每一个都能让人掉坑里。
2.1 char与varchar：定长和变长的底层逻辑
char(n)是定长字符串，最大255字符，存不满会用空格补齐，取出时自动去掉尾部空格。varchar(n)是变长字符串，最大65535字节，需要额外1~2字节存储实际长度。
底层的差异决定了各自的适用场景：
char适合存长度基本固定的值，比如手机号、身份证号、md5值、固定编码。varchar适合存长度变化大的值，比如用户名、邮箱、标题、备注。
长度固定时char的检索性能略优，因为它不需要读取长度前缀，而且行的长度是可预测的，更新时不容易触发页分裂。但现代存储引擎在多数情况下，这个性能差异已经小到可以忽略。大多数业务场景直接用varchar更省心。
有个常见的说法："varchar最长只能设255，因为255字节以内用1字节记录长度，超过要用2字节。"这个说法不够精确。准确的说，varchar的长度单位是字符，而记录长度用的是字节。在utf8mb4下，varchar(255)最大可能占用255 * 4 = 1020字节，依然只用1字节长度前缀。真正会因为超过255影响长度前缀的情况，跟所使用的字符集有关。简化的经验是：为了节省长度字节而去卡255，在utf8mb4下意义不大，真正要关注的是索引长度限制。
2.2 varchar(255) 的隐形成本与索引长度限制
InnoDB 的单个索引最大长度是 3072 字节。在utf8mb4字符集下，一个字符最多占用4字节，所以varchar(255)的列建索引，最多占用255 * 4 = 1020字节，单列索引没问题。
但如果是复合索引，比如(name, email, phone)三个字段都设成varchar(255)，索引长度就是3 * 1020 = 3060字节。如果再加一个varchar(100)，总长度逼近或超过 3072 字节限制，MySQL会直接报错。即使不报错，过长的索引不仅占内存，写入时维护成本也高，查询优化器还不一定愿意用。
推荐的做法是：按照业务实际长度去定义，不要习惯性全部设成255。比如用户名设varchar(32)，邮箱设varchar(64)，普通文本varchar(255)顶天。真有超长内容，用text类型，不要硬塞给varchar。
2.3 text与blob：大字段的存储方式与注意事项
text系列（tinytext、text、mediumtext、longtext）和blob系列的区别只有一个：text存的是字符，按字符集排序；blob存的是二进制字节，没有字符集概念。文章内容、JSON字符串、日志全文，用text。图片、文件二进制流，用blob，但多数时候文件应该放对象存储，数据库里只存路径。
大字段最容易踩的坑有两个：
一是行溢出。InnoDB 默认行格式是dynamic，当行的总长度超过innodb_page_size（默认16KB）的一半左右时，大字段会被移到独立的overflow page存储，原行只保留一个20字节的指针。这意味着select *查询大量大字段时，可能需要额外读溢出页，性能下降明显。经验是：大字段不要出现在select *里，按需查询。
二是不能直接给全文索引。text建普通索引需要指定前缀长度，比如index idx_content (content(100))。而全文检索在MySQL里要用fulltext索引，中文分词还麻烦，真正需要全文搜索的场景，业务量一大就该上专业的搜索引擎了。
2.4 字符集与排序规则：utf8mb4和utf8mb4_bin的选型细节
字符集和排序规则是一个被严重低估的配置点。MySQL 8.0 默认字符集是utf8mb4，默认排序规则是utf8mb4_0900_ai_ci，其中ai表示不区分重音，ci表示不区分大小写。
先说为什么用utf8mb4而不是utf8。MySQL的utf8实际上是utf8mb3，最多3字节，存不了4字节的 emoji 和一些特殊字符。你建表时用utf8，插入一条带 emoji 的数据，要么报错，要么乱码。业务上一旦出现这种情况，改库的字符集成本极高。新项目一律utf8mb4，是对未来兼容性的保障。
排序规则方面：
utf8mb4_0900_ai_ci：不区分大小写，适合常规业务，查询时name = 'abc'能匹配到ABC。utf8mb4_bin：按二进制比较，区分大小写，适合用户名登录匹配、敏感信息核对。
登录校验场景有个典型问题：如果用户名列的排序规则是ci（不区分大小写），那么user_abc和USER_ABC在唯一索引里是冲突的。业务上用邮箱或用户名登录的，建议唯一键字段采用utf8mb4_bin或干脆在应用层统一转小写再入库，避免大小写混用引发的账号错乱。
3. 日期时间类型：时区、范围与格式化的恩怨
日期时间类型看着最简单，实际是生产事故率最高的类型之一。2038年问题、时区错乱、格式化开销，每一个都是真金白银换来的教训。
3.1 date、time、datetime的适用范围
| 类型 | 字节数 | 取值范围 | 格式 | 场景 |
|---|---|---|---|---|
| date | 3 | 1000-01-01 ~ 9999-12-31 | 2024-06-01 | 生日、账单日 |
| time | 3 | -838:59:59 ~ 838:59:59 | 838:59:59 | 时长、时间段 |
| datetime | 8 | 1000-01-01 ~ 9999-12-31 | 2024-06-01 12:00:00 | 业务时间、记录创建时间 |
| timestamp | 4 | 1970-01-01 ~ 2038-01-19 | 2024-06-01 12:00:00 | 自动记录时间、按时间排序 |
datetime和timestamp是最容易混淆的。两者都能存年月日时分秒，但底层逻辑完全不同：
datetime存的是字面值，不依赖时区。你存进去2024-06-01 12:00:00，不管你数据库时区怎么改，读出来都是这个时间。timestamp存的是 UTC 时间戳，读取时会根据会话的time_zone参数转换成当地时间。
这个差异在生产环境是致命的。多机房部署时，如果A机房和B机房的数据库时区设置不一致，用timestamp存的时间读出来会相差好几个小时。而datetime没有这个问题，因为根本不转换。
实际业务里，我的建议是：
- 需要记录"绝对时刻"的，比如订单创建时间、操作日志时间，用
timestamp或datetime都可以，但务必统一时区，推荐全链路 UTC 存储，展示层转换。 - 跟具体日期有关的业务日期，比如账单日、生日，用
date，不要用datetime加时间部分。 - 存时间段或时长，用
time，不要用int存秒数然后在代码里换算，可读性和维护性都很差。
3.2 timestamp的2038年问题与自动初始化
timestamp的取值范围是 1970-01-01 到 2038-01-19，这是32位时间戳的上限。别觉得这是几十年后的事，凡是设计寿命超过15年的系统，都不建议核心时间字段用timestamp。datetime的范围到9999年，没有这个问题，代价是多占4个字节。
业务系统的创建时间和更新时间，推荐用datetime或bigint存毫秒时间戳。用bigint的好处是彻底绕开时区问题，排序和范围查找都很直接，缺点是可读性差，排查问题时要把数字翻译成时间。
MySQL 可以自动管理时间字段：
CREATE TABLE `order` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;这里有个细节：DEFAULT CURRENT_TIMESTAMP在 MySQL 5.6.5 以后才支持，5.5 及之前只能用timestamp实现这个效果。很多老项目的create_time用的是timestamp，改造成datetime时要注意检查默认值是否生效。
3.3 日期条件查询的三个高频错误
日期字段用不好，索引就会失效。我排查过太多慢查询，最后定位到日期上的问题。
第一个错误：对索引字段做函数运算。
-- 错误：create_time 是索引列，DATE() 函数导致索引失效 SELECT * FROM `order` WHERE DATE(create_time) = '2024-06-01'; -- 正确：使用范围查询，索引生效 SELECT * FROM `order` WHERE create_time >= '2024-06-01 00:00:00' AND create_time < '2024-06-02 00:00:00';第二个错误：字符串和日期做隐式转换。
-- 错误：create_time 被转换为字符串比较，索引失效 SELECT * FROM `order` WHERE create_time = '2024-06-01 12:00:00'; -- 正确：使用 STR_TO_DATE 或直接用日期类型 SELECT * FROM `order` WHERE create_time = STR_TO_DATE('2024-06-01 12:00:00', '%Y-%m-%d %H:%i:%s');第三个错误：按时间分页用了OFFSET加上ORDER BY create_time DESC，表一大，越往后翻越慢。正确的做法是记住上一页的最后一条时间，用WHERE create_time < 上次最后时间做游标分页。
4. 其他类型：json、enum、set与二进制的应用边界
除了数值、字符串、日期，MySQL还有几个"小众但好用"的类型，用对了能省不少事，用错了也能添不少乱。
4.1 json类型：什么时候该用，什么时候不该用
MySQL 5.7 开始支持json类型，8.0 里已经支持json索引（通过虚拟列实现）和json聚合函数。json类型的最大价值是：结构不确定的字段可以直接存，查询时用->或->>表达式提取，不需要在应用层手动序列化和反序列化。
CREATE TABLE `user_profile` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint unsigned NOT NULL, `extra` json DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 查询 extra 里的 nickname 字段 SELECT user_id, extra->>'$.nickname' AS nickname FROM user_profile WHERE user_id = 10086;json类型适合存一些低频变化的扩展属性，比如用户的偏好设置、营销活动的扩展配置、第三方接口透传的原始报文。但不要什么字段都塞进json，原因有三：
- json 字段无法像普通列那样走索引（除非用生成列模拟）。
- 更新 json 字段时，InnoDB 是整体替换，不是局部更新，高频更新场景性能很差。
- json 字段在
select *时占用的查询开销大，而且可读性差。
有个折中方案：高频查询的字段拆成独立列，低频扩展字段塞进json，两边兼顾。
4.2 enum与set：选它之前想清楚这几点
enum类似 Java/C# 的枚举，定义了一组允许的值，存储时实际存的是索引号（1、2、3...），最多65535个元素。set是集合，可以存多个值的组合，最多64个元素。
enum在存储上确实省空间，一字节就能存一个枚举值。但实际使用有几个坑：
- 变更成本高：线上要新增一个枚举值，需要执行
ALTER TABLE，在千万级表上代价很大。 - 隐式转换问题：
enum列在排序时按索引号排，不是按字典序排，容易让新人困惑。 - 默认值行为：不合法值在非严格模式下会被存成空字符串，不会报错，容易产生脏数据。
- 与代码枚举的同步风险：应用代码和数据库的枚举值必须手动保持一致，一旦漏改一处，数据解读就乱了。
我的建议是：大多数业务里，状态字段不需要真的用enum，用tinyint加代码层枚举就好。tinyint只有1个字节，存储开销和enum一样，但没有变更成本，加枚举值不需要改表，天然兼容。enum真正合适的场景是那些定义非常稳定、几乎不可能变的字段，比如性别（虽然这个也有争议）、关系类型这类。
4.3 二进制类型：binary、varbinary与blob的适用场景
binary和varbinary对应char和varchar，只是存的是字节，没有字符集概念，比较时按字节值运算。适合存的场景包括：MD5/SHA1 等哈希摘要、加密后的数据、大小写敏感的短字符串。
把哈希摘要存成binary而不是varchar，能省一半空间。比如 MD5 是32位十六进制字符串，用varchar(32)需要最少32字节，转成binary(16)只需要16字节。查询时把十六进制字符串转成二进制再比较：
-- 存：将十六进制字符串转为字节数组 INSERT INTO t (hash) VALUES (UNHEX('d41d8cd98f00b204e9800998ecf8427e')); -- 查：同理转二进制 SELECT * FROM t WHERE hash = UNHEX('d41d8cd98f00b204e9800998ecf8427e');不过现在多数团队都是直接用varchar存哈希串，换取可读性和排查方便。空间成本可接受的情况下，这样也没毛病。核心原则是：可接受的性能代价换可维护性，是值得的；不可接受的精度风险，是绝不能妥协的。
5. 建表时的选型思路：从业务场景反推数据类型
讲了这么多类型，关键是落到建表上。很多新人建表全凭感觉，字段类型随意选，等踩坑了再回头改，成本极高。这里给你一套可以直接套用的选型决策流程。
5.1 一张表的设计决策顺序
我建表时会按这个顺序思考，每一步都围绕业务量级和查询方式来定：
第一步，确定字段的业务语义。是ID、是名称、是状态、是时间、还是金额？语义决定了类型的候选范围。ID用整数类型，名称用字符串，状态用tinyint，时间用日期时间类型，金额用decimal。
第二步，估算量级和边界。单表会到多少行？主键会不会超过20亿？金额范围是多少？小数位要求几位？根据边界选择最小的可用类型。
第三步，明确查询方式。这个字段会用来做等值过滤、范围查询、排序、还是join？会建索引吗？如果是索引列，字符串类型要控制长度，日期类型要避免函数运算。
第四步，考虑变更频率和扩展性。字段值是固定集合还是经常变化？经常变化的不要用enum。字段长度设计得更短还是更宽？以业务当前最值加一定的余量为准。
第五步，统一字符集和排序规则。全库统一utf8mb4，敏感字段用utf8mb4_bin。表与表之间的关联字段，字符集和排序规则必须一致，否则join时无法使用索引。
5.2 常见业务字段的选型参考表
直接给出一份可以作为起点的参考表，实际使用时按业务量级微调：
| 业务字段 | 推荐类型 | 备注 |
|---|---|---|
| 主键ID（自增） | bigint unsigned | 单表超20亿行的保障 |
| 业务单号（字符串） | varchar(32) ~ varchar(64) | 必要时加唯一索引 |
| 用户手机号 | varchar(32) | 不建议 bigint，可能带国家码、前导零 |
| 邮箱 | varchar(64) | 常规长度足够 |
| 订单状态 | tinyint | 0/1/2/3，代码层维护枚举 |
| 库存数量 | int unsigned | 用无符号需要注意 join 时的一致性 |
| 金额 | decimal(10, 2) | 大金额项目可调整位数 |
| 利率/百分比 | decimal(5, 4) | 保留4位小数 |
| 创建时间 | datetime | 默认 CURRENT_TIMESTAMP |
| 操作日志时间 | datetime 或 bigint | 日志量大建议分区 |
| 用户昵称 | varchar(32) | utf8mb4_bin 防止混淆 |
| 文章内容 | longtext | 不建议 select * |
| 扩展属性 | json | 低频字段，不参与复杂查询 |
| 软删除标记 | tinyint | 0未删 1已删 |
| 版本号 | int unsigned | 乐观锁使用 |
这份表不是标准答案，但它符合绝大多数业务的实际需要。尤其是金额和时间这两类，很多线上事故都是从这两个地方开始的。
5.3 两个值得注意的隐式转换案例
数据类型的隐式转换是慢查询和事故的温床。MySQL 在比较不同类型的值时会发生隐式转换，转换规则是：字符串和数字比较，字符串转为数字；日期和时间与字符串比较，字符串转为日期。
第一个案例：字符串类型的手机号字段，存储层是varchar，查询时用了数字：
SELECT * FROM `user` WHERE phone = 13800138000;这个查询会把整列varchar手机号转成数字再比较，全表扫描，索引完全用不上。正确写法是phone = '13800138000'。这个错误我在生产环境见过不少于五次。
第二个案例：两个表的join字段，一个是bigint，一个是varchar但存的是数字。MySQL 会把字符串转成数字，虽然可能走索引，但转换导致优化器对基数的估算不准，执行计划选错的情况经常发生。
结论是：join 字段和等值过滤字段，类型必须完全一致，包括字符集和排序规则。这是设计规范，不是建议。
6. 常见问题排查与经验复盘
最后这部分，把实际工作中容易遇到的数据类型相关问题和排查思路整理出来，方便大家作为速查参考。
6.1 不同类型导致的慢查询排查思路
遇到查询变慢，先不要急着加索引，回头检查数据类型层面有没有问题。我排查的顺序通常是：
- 检查查询条件里的字段类型是否和表定义一致。
varchar字段是否被传入了数字，日期字段是否被传入了字符串。 - 检查表 join 的关联字段类型和字符集是否一致。
utf8mb4和utf8mb4_bin混合、int和bigint混合，都容易出问题。 - 检查索引列上是否使用了函数运算。
DATE()、YEAR()、LEFT()都会让索引失效。 - 用
EXPLAIN看key和rows，重点看type是否为ALL或index。优先复现最慢的查询条件，逐步缩小范围。
这些排查点里，隐式转换最隐蔽，因为它不会报错，只是性能悄悄下降。建议团队内部做一次代码审查，专门找 WHERE 和 JOIN 条件里的类型不匹配问题，一次性可以处理掉很多潜在慢查询。
6.2 老项目的数据类型改造建议
在线表的ALTER TABLE成本很高，尤其在大表和主从架构下，直接执行 DDL 可能造成锁表或主从延迟。如果确实要改类型，有几种相对温和的途径：
- 使用
pt-online-schema-change工具，以触发器同步数据的方式完成 DDL，减少锁表时间。 - 在从库上先做 DDL 验证，确认无误再切换。
- 新建一张新结构的表，应用双写，切读，最终改名为新表。这种方式适合数据结构有较大变化的情况。
- 实在不能改的，通过新增冗余列过渡。比如一个
varchar状态列要改成tinyint，可以先加一个status_new列，双写，稳定后再去掉旧列。
改造前建议先做一次字段长度的梳理，把低频大字段和高频小字段分离，该拆表就拆表。很多老系统的性能问题，根子上是"所有字段堆在一张表里，全是varchar(255)"，这种工程隐患不解决，加再多缓存都只是延缓。
6.3 一线实战后的几条真经验
写到这里，分享几条我实际做完大量表结构评审后的经验，不一定都写在官方文档里，但都是实打实踩出来的：
第一，能用tinyint用tinyint，能用smallint用smallint。磁盘成本这几年虽然在降，但内存成本还在，索引的叶子节点都在内存里，字段越短，一页能装的记录越多，扫描和排序的开销越小。这不是微优化，在千万级表里，tinyint和int的索引性能差距非常明显。
第二，整数字段做主键，永远别用 UUID。UUID 做主键不仅占空间，而且随机性导致索引频繁页分裂，写入性能下滑严重。真要全局唯一ID，用雪花算法生成的bigint，兼顾顺序性和唯一性。
第三，不要把业务状态设计成一组互相排斥的布尔字段。比如is_paid、is_shipped、is_finished各存一个tinyint，看着直观，但状态组合一多，怎么查都别扭。不如用一个status字段统一管理，代码层维护状态流转，配合updated_at记录变化时间。
第四，每次建表时，把select *的使用场景想清楚。如果这张表的字段超过20个，且包含大文本或 JSON 字段，select *在线上就是一颗定时炸弹。数据类型的收益，最终要落到查询方式上，不然顺手建的类型做得再好也白搭。
第五，数据类型的所谓"标准答案"不存在，量级不同结论完全不同。一张几百行的配置表，varchar(255)随便用；一张十亿行的流水表，每一列都得精打细算。先估量级，再定类型，顺序别反。
