说个实话干后端这些年面试过不少人也带过不少新人。聊到MySQL十个人里有八个能把索引、事务、锁说得头头是道但一落到建表随手就是varchar(255)一把梭金额用float状态用varchar存中文。等到数据量上来、慢查询出现、对不上账的时候才回过头来查数据类型的问题。MySQL的数据类型看着简单无非就是数值、字符串、日期那几类但选错了轻则多占磁盘空间重则索引失效、精度丢失、甚至整个库的性能被拖垮。这篇东西不打算讲教科书式的定义我把这些年建表踩过的坑、调优时排查过的案例、面试里常被问到的细节全部揉碎了整理出来。内容包括每种类型的底层存储逻辑、适用场景、边界坑点以及一套可以直接照抄的选型思路。不管是刚入门想搞懂int和bigint区别的新手还是想系统梳理一遍、避免线上事故的老手这篇都值得花十分钟读完。1. 数值类型别小看这几个数字数值类型是所有表里用得最多的也是问题最多的地方。很多人只记得int能存10位数字但问到int(11)里的11是什么意思能答对的人不多。这部分把整数、小数、布尔相关的类型一次说透。1.1 整数类型范围、字节数与显示宽度的真相MySQL的整数类型一共有tinyint、smallint、mediumint、int、bigint五种区别只在于存储字节数和能表示的数值范围。类型字节数有符号范围无符号范围常见用途tinyint1-128 ~ 1270 ~ 255状态码、年龄、开关smallint2-32768 ~ 327670 ~ 65535小型计数、端口号mediumint3-8388608 ~ 83886070 ~ 16777215中等计数int4-2147483648 ~ 21474836470 ~ 4294967295主键、常规计数bigint8-9.22×10^18 ~ 9.22×10^180 ~ 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.3MySQL的浮点运算同样存在这个问题。如果你用float存金额累计几万笔订单后对账出现几分钱的差异就是精度误差累积的结果。decimal是定点数以字符串形式存储按十进制精确计算不存在精度丢失。语法是decimal(M, D)M代表总位数最大65D代表小数位数。比如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的适用范围类型字节数取值范围格式场景date31000-01-01 ~ 9999-12-312024-06-01生日、账单日time3-838:59:59 ~ 838:59:59838:59:59时长、时间段datetime81000-01-01 ~ 9999-12-312024-06-01 12:00:00业务时间、记录创建时间timestamp41970-01-01 ~ 2038-01-192024-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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有个细节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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 查询 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)常规长度足够订单状态tinyint0/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低频字段不参与复杂查询软删除标记tinyint0未删 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)随便用一张十亿行的流水表每一列都得精打细算。先估量级再定类型顺序别反。