1. 数据类型怎么学先搞懂 MySQL 为什么把它当重点说起 MySQL 学习很多人上来就啃 SQL 语法、背命令结果一到实际设计表结构就懵了字段到底用INT还是BIGINT电话号码能不能用VARCHAR存时间字段为什么有时候是字符串有时候是数字这些问题归根结底都指向同一个基础——MySQL 数据类型。可以说学不会数据类型你写的每一个建表语句都是在给自己埋雷轻则浪费存储空间重则直接运行报错、数据失真。我最早带新人的时候发现他们最常见的操作就是把所有字段都定义成VARCHAR(255)理由是不确定存什么、怕存不下。这种偷懒做法在开发阶段的确跑得通但到了性能优化、数据迁移、容量评估阶段就会非常痛苦。一个VARCHAR字段在索引上、排序上、比较上的行为和数值字段完全不同例子一抓一大把对VARCHAR存数字排序时10会排在9前面用INT存日期想要按月份查询就得用FROM_UNIXTIME转换索引直接失效。这些都是类型没选对惹的祸。这篇内容适合谁看准备面试的、刚转行做后端开发的、以及那些已经写过不少 SQL 但还是想系统理一遍MySQL数据类型的人。我会把自己在实际项目中踩过的坑、积累的选型经验还有那些网上教程很少讲到的细节都拆开讲清楚。学完以后你再回头建表会有一种“手中有尺、心里有底”的感觉。2. 数据类型的整体分类与选型思路2.1 先建立全局认知常见类型都有哪些MySQL 的数据类型可以粗分成四类数值类型、字符串类型、日期时间类型、其他特殊类型JSON、空间、枚举、集合等。每一类下面又有细分用一个表格先把你需要记住的骨干列出来大类常见类型典型场景存储空间粗略注意事项整数TINYINT / SMALLINT / MEDIUMINT / INT / BIGINTID、数量、状态码1/2/3/4/8 字节有符号无符号范围要算清小数DECIMAL / FLOAT / DOUBLE金额、比例、科学计算变长 / 4 / 8 字节金额用 DECIMAL禁止用 FLOAT字符串CHAR / VARCHAR / TEXT / BLOB名称、内容、文件二进制变长或定长TEXT 不能直接给默认值枚举集合ENUM / SET固定选项 / 多选标签1~2 字节 / 8 字节修改枚举值开销不小日期时间DATE / TIME / DATETIME / TIMESTAMP / YEAR生日、创建时间、过期时间3/3/8/4/1 字节TIMESTAMP 受时区和 2038 年限制特殊类型JSON / GEOMETRY / POINT 等动态属性、地理坐标见官方文档JSON 有函数支持但不宜滥用这张表只是骨架。实际设计时你还要考虑字符集、排序规则、填充因子这些衍生因素但第一步一定是把基础类型本身搞清楚。我见过不少工程师反问“BIGINT 不是用来存手机号吗”——这就是典型的没理解类型边界。2.2 选型原则三个维度决定用哪个类型先看存储空间。MySQL 中一张表的 InnoDB 数据页是 16KB如果一行数据平均多占 50 字节一页能存的行数就会少查询时扫页的数量增加IO 开销直接上升。不要觉得“多几个字节无所谓”在线业务表几千万行时一个BIGINT换成INT就能省下 4 字节每行即 4 * 5000万 ≈ 190MB 的裸数据量加上索引轻松省出几个 GB。再看比较规则。数值类型比较是数字比大小字符串类型是字符集序。ORDER BY和索引缺省时混合类型会导致隐式转换例如WHERE phone 13800138000如果 phone 是VARCHARMySQL 会把字段转成数字再比较索引大概率作废。据我观察这类隐式转换引发的慢查询在故障排查中占了很大比例。最后看精度和范围。金额、坐标这类数据对精度极度敏感用FLOAT会出现“1.1 2.2 3.3000000000000003”的经典误差ID 自增值要根据业务量估算峰值TINYINT最大 127随随便便就爆。选型本质上是“用最便宜的代价装下最确定的需求”。2.3 从为什么角度理解整数类型的符号位与存储单位很多初学者背过“INT 是 4 字节”但不知道为什么是 4 字节。其实很简单8 位一个字节4 字节就是 32 位。INT有符号范围是-2^31 ~ 2^31-1也就是 -2147483648 到 2147483647刚好利用全部 32 位。BIGINT是 8 字节范围达到2^63-1对于绝大多数业务 ID 绰绰有余。有时候面试题问你“为什么有符号和无符号范围不一样”就是因为最高位变成了符号位。虽然 MySQL 8.0 里支持UNSIGNED但我的习惯是尽量不依赖无符号一是不符合行业通用规范二是在做数据迁移的时候容易踩边界坑。TINYINT占 1 字节也就是 8 位用来存状态值0/1/2/3 足够用。SMALLINT2 字节适合省市区编码之类的数值。只要能用小类型不要贪大这是容量规划的底层直觉。3. 核心类型逐个拆解不背文档只记要点3.1 数值类型整数、定点数、浮点数的正确用法整数类型大家最熟悉但有一个隐藏的小知识INT(N)中的N表示显示宽度不是存储范围。比如INT(11)很多老教程说这是“最大 11 位”其实你的INT照样可以存更大的值只是显示的时候可能截断。在 MySQL 8.0.17 之后显示宽度属性已经被标记为可废弃我建议你建表时直接写INT不要写INT(11)省得多生枝节。DECIMAL是面试高频。它的语法是DECIMAL(M, D)M 是总位数D 是小数位数。比如DECIMAL(10, 2)表示整数部分 8 位、小数部分 2 位最大可以存 99999999.99。它为什么精确因为它底层采用二进制编码用字符串逻辑存储数字不会像浮点数那样有精度丢失。金额、税率、余额这类数据无条件用 DECIMAL。注意 DECIMAL 是变长存储实际占用字节与 M 相关计算公式大致是“每 9 位数字占 4 字节剩余部分向上取整加 1”不要按照官方表上的单值死记灵活估算即可。FLOAT和DOUBLE虽然运算快但存在舍入误差所以它们只适合指纹相似度、经纬度坐标在可接受误差范围内、统计指标等对精度不敏感的场景。我很反感有人拿 FLOAT 存价格因为分账脚本一旦跑出 19.999999 元财务和开发必有一场拉扯。3.2 字符串类型CHAR、VARCHAR、TEXT 真的是长度区别吗CHAR是定长VARCHAR是变长。定长的意义在于存储空间固定读取的时候不需要额外信息定位长度变长需要额外的 1~2 字节保存实际长度。这个区别在短字符串上表现明显比如性别CHAR(1)就比VARCHAR(1)少一部分长度开销而且不会因为长度变化导致页分裂。VARCHAR是最常用的。它的最大长度理论可达 65535 字节但实际受行大小限制一个 16KB 的数据页要容纳至少两行所以单行总列长度不能超过约 65535 字节。注意这里说的是字节不是字符。utf8mb4 一个字符最多占 4 字节因此VARCHAR(255)实际上可能占用 1020 字节。我建议在定义VARCHAR时除了要预留未来扩展空间还要考虑索引长度。InnoDB 单列索引最大支持 767 字节旧版本或 3072 字节新版本如果你给VARCHAR(500)加索引在 utf8mb4 下就是 2000 字节超出旧版本限制只能报Specified key was too long。TEXT家族TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT是完全不同的逻辑它们以对象形式存储最大 4GBLONGTEXT。但有两个致命问题不能有默认值除非你用表达式默认值、索引必须指定前缀长度。所以我的铁律是能用VARCHAR解决的绝不用TEXT只有在内容真的很长比如文章正文、序列化协议数据才考虑TEXT。ENUM也是字符串相关但它的底层是整数索引。好处是数据可读性好、存储紧凑坏处是它的排序按照内部索引而不是字典序且修改枚举值需要 DDL很重。我个人只有在选项特别稳定时用它比如订单状态机的状态标签平时更推荐用TINYINT存状态码再加注释这样后续改动灵活。3.3 日期时间类型DATETIME 与 TIMESTAMP 的世纪选择日期时间类型是让很多人头疼的部分。先看几个常用的YEAR1 字节范围 1901~2155少用。DATE3 字节格式YYYY-MM-DD。TIME3 字节格式HH:MM:SS。DATETIME8 字节范围1000-01-01 00:00:00到9999-12-31 23:59:59不受时区影响。TIMESTAMP4 字节范围1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC受时区影响存储时会转换为 UTC展示时转换回当前时区。实际项目中绝大多数人会纠结“创建时间用DATETIME还是TIMESTAMP”。我的建议是优先DATETIME因为TIMESTAMP的 2038 年问题不是所有业务都能保证自己在 2038 年前升级TIMESTAMP的时区转换容易产生“咦我存的时间怎么差了 8 小时”的迷惑DATETIME范围更大更适合通用的时间表示。但是如果你做跨时区的全球业务且希望所有时间统一以 UTC 存储TIMESTAMP反而更省心因为它自动转换你只需要设置好数据库的time_zone。这也不是绝对的重点是你必须清楚自己的业务需求。关于默认值MySQL 5.6.5 之前DATETIME不能设置默认值CURRENT_TIMESTAMP导致很多人只能用TIMESTAMP。新版本已经不存在这个问题了DATETIME完全可以写DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP使用体验一样好。3.4 JSON 类型与空间类型什么时候可以放开用MySQL 5.7 开始支持原生JSON类型。它的好处是字段内可以存任意结构化数据并且提供了一套 JSON 路径表达式JSON_EXTRACT、-等来做查询。说实话动态表单、预留字段这类场景用它非常香。但要注意JSON 列无法直接建普通索引只能通过虚拟列或函数索引间接优化。空间类型GEOMETRY、POINT、LINESTRING等属于小众需求主要用于地图、GIS 场景。如果你只是想存经纬度我其实建议用DECIMAL(10, 6)或DOUBLE配合空间索引来做范围查询反而更简单。真到了需要算距离、判断点面关系的时候再上空间函数不迟普通业务完全用不到那么重的东西。4. 实操环节从建表开始把选择落地4.1 一个例子讲透用户表与订单表的类型设计光说理论不好记咱们拿电商场景来练。假设要做这两张表用户表user和订单表order。CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, nickname VARCHAR(32) NOT NULL DEFAULT COMMENT 昵称, gender TINYINT NOT NULL DEFAULT 0 COMMENT 性别 0未知 1男 2女, email VARCHAR(128) NOT NULL DEFAULT COMMENT 邮箱, phone BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 手机号, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;这里有几个关键点id用BIGINT UNSIGNED而不是INT原因是防止未来用户量过大溢出。早期表用INT能存 21 亿左右感觉很多了但业务一旦达到量级扩容就是噩梦。既然数据库设计阶段能规避就不要等运维去ALTER。gender用TINYINT而不是枚举字符串。原因是新增一种性别时不需要改表结构只需要在代码层映射。这个做法牺牲了一点可读性换来了灵活性。phone用BIGINT UNSIGNED还是VARCHAR(11)这是个经典之争。我坚持用BIGINT一是手机号本质是数字用数值类型比较高效二是你不需要在手机号码上做子串匹配也就不会用到字符串函数三是BIGINT的查询比VARCHAR快一点。不过要注意如果用BIGINT存手机号前端展示可能因为超出 JS 安全整数范围而精度丢失这个问题在接口层加个字符串返回就行。如果你喜欢用VARCHAR也完全可以只是索引存储差别不大两者都行但要统一。created_at用DATETIME而不是TIMESTAMP理由上面说过这样就没有 2038 年隐患和时区困惑。再看订单表CREATE TABLE order ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 订单ID, user_id BIGINT NOT NULL COMMENT 下单用户ID, order_no VARCHAR(64) NOT NULL COMMENT 订单号, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态 0待支付 1已支付 2已发货 3已完成 4已取消, expire_at DATETIME NOT NULL COMMENT 支付截止时间, remark VARCHAR(255) NOT NULL DEFAULT COMMENT 备注, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT订单表;金额字段是重中之重amount DECIMAL(10,2)总长度 10 位其中 2 位小数最大 99999999.99。如果你觉得未来一个订单可能上亿就扩成DECIMAL(12,2)。千万别图性能用DOUBLE订单流水错一分钱对账脚本跑死都查不出来。4.2 用 ALTER TABLE 修改类型的注意点现实情况是很多表不是一开始就设计好的你在运营过程中不得不修改字段类型。在ALTER TABLE之前先把自己要回答的问题列出来这个字段是否在索引中如果是修改类型会导致索引重建耗时取决于表大小。当前数据能否安全转换比如VARCHAR存了非数字字符串改成INT会直接报错或变成 0。是否有外键或关联关系改类型可能导致其他表关联失败。主从复制时ALTER会产生临时表锁业务高峰期做变更等于自杀。我的经验是用ALTER TABLE加ONLINE DDL策略如果不是非常有把握先在测试库用相同量级的数据演练一遍统计耗时。比如把一张千万级表的phone从VARCHAR改BIGINT很可能要锁表几十秒线上业务绝对不允许。处理这类变更更安全的方式是新建一个字段双写过渡再灰度切换。听起来笨但比出事故强一万倍。4.3 隐式类型转换SQL 慢查询里最常见的陷阱很多新手看到WHERE user_id 123就会说“傻类型不匹配”。可实际上MySQL 有时会把字段类型自动转换有些转换是聪明的有些则让你追悔莫及。经典的案例phone字段是VARCHAR你写WHERE phone 13800138000因为等号右边是数值MySQL 会把左边的字符串列强行转成数值再比较。这意味着字段上即使有索引也无法正确使用因为每一行都要做一次转换。我在测试环境复现过同样的索引改写条件后性能能差 100 倍以上。另一个隐藏更深的坑WHERE string_col 0。如果string_col是VARCHARMySQL 会把字符串转成数字比如abc转成 0于是所有非数字开头的字符串都会匹配到。这种查询结果完全违背直觉极易产生数据漏洞。解决办法就是写 SQL 时保持类型一致参数传什么类型字段用什么类型永远不要指望隐式转换帮你兜底。5. 常见问题与排查技巧实录5.1 数字的溢出与截断INSERT时如果数值超出类型范围严格模式下 MySQL 会报错非严格模式下会自动截断并产生警告。比如给TINYINT插入 128非严格模式下会变成 127或 127 附近静默丢失上界。更可怕的是这条数据已经写进去了你根本不知道。解决办法是用SHOW WARNINGS检查每次导入时的告警或者直接打开严格模式SET sql_mode STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE;MySQL 8.0 默认就是严格模式但很多迁移到 5.7 的项目还沿用旧配置。建议任何新装环境都检查一遍sql_mode别留下静默截断的隐患。5.2 VARCHAR(255) 与 TEXT 的边界VARCHAR(255)是很多表的“万金油”但你要知道 255 是历史遗留的字符数上限在 utf8mb4 下它占 1020 字节已经足够大了。如果字段要存一两段句子或者简短描述这个长度够用。超过这个长度但小于几千字用TEXT也可以但索引和默认值限制会麻烦。在 InnoDB 中即使你把VARCHAR设成 10000底层存储引擎也可能自动升级为TEXT策略行溢出存储所以关键点不是“能不能存”而是“要不要直接走 TEXT”。我的选择规则是长度相对固定比如地址、邮箱用VARCHAR(255)封顶长度不固定且可能超过 255直接TEXT避免超长时隐式转换导致行迁移。TEXT无法直接设置默认值的问题在 MySQL 8.0.13 可以用表达式默认值绕过比如DEFAULT ()但为了兼容性我还是建议TEXT字段必须用NOT NULL并靠代码层赋值。5.3 TIMESTAMP 的时区连环坑TIMESTAMP存储的是 UTC 时间戳但在客户端展示时MySQL 会根据time_zone变量把值转成当前会话时区。如果半夜收到告警“数据时间比真实时间多了 8 小时”先检查三样东西JDBC 连接串是否指定serverTimezone、操作系统时区、MySQLtime_zone变量。SHOW VARIABLES LIKE %time_zone%; SET time_zone 08:00;如果所有配置都对时间还是不对再检查代码里用的框架有没有把DATETIME当成Date瞎折腾一遍。我的统一建议是业务时间统一用DATETIME 应用层时区数据库侧只保留一个规则“不转时区”。这样排查心智负担最低。5.4 零日期出现在“为什么会写入失败”NO_ZERO_DATE开启之后你就不能再把0000-00-00写入DATETIME字段。很多从 5.6 升上来的项目旧数据可能包含了大量零日期一旦开启严格模式读不出来、写不进去会非常被动。这时候你要么改应用层逻辑把零日期转为NULL或者DEFAULT CURRENT_TIMESTAMP要么将字段改成NULL允许型用NULL表达“未设置”。千万不要为了图省事把 sql_mode 关掉那是饮鸩止渴。5.5 常见报错与排查速查表报错信息可能原因解决思路Data truncated for column插入的数据超过类型长度或精度检查字符集、字节长度、DECIMAL 精度开启严格模式定位Out of range value for column数值超出范围升级为 BIGINT 或调整业务规则Specified key was too long; max key length is 3072 bytes索引字段太长缩短字段长度或使用前缀索引Incorrect datetime value: 2021-13-01非法日期值校验应用层输入的日期格式检查 sql_modeInvalid utf8mb4 character string客户端编码不对统一连接串 charsetutf8mb4JSON column cannot be used in key specification对 JSON 建普通索引改用虚拟列生成可索引的类型6. 我给新人的几条实操建议看到这你应该已经理解了数据类型的选择逻辑。最后分享几个我平时在代码审查里反复念叨的点不要盲信“最大范围”。范围大不代表好你的业务十年后到底能到多大是可以用流量预估、注册量预估来推算的。BIGINT自增主键看起来无敌但如果你用INT加UNSIGNED可以到 42 亿对绝大多数系统足够何必为了“安全感”多付出 4 字节一行呢当然如果你们公司本身就是亿级 DAU直接用BIGINT我不反对。给字段加注释这是数据类型的一部分。我见过无数张表status字段存 0 和 1但没人知道 0 代表什么。这跟数据类型有什么关系因为TINYINT本身不携带语义注释就是将这些数字翻译成人话的唯一途径。写 SQL 之前先检查类型对齐。尤其在跑数据分析、导出报表时WHERE条件里的参数类型、联表字段的类型是否一致直接决定查询能否走索引。半秒钟的检查能省下你排查慢查询的一整个下午。踩过的坑多了你会发现数据类型不是冷冰冰的语法规则它是你对业务理解的一种映射。把“这个字段到底代表什么”想清楚了MySQL 数据类型的学习其实就完成了大半。剩下的就是日常积累是看到DECIMAL第一反应别写成FLOAT是看到VARCHAR下意识想一下字符集是看到TIMESTAMP提前问自己“我这个业务 2038 年会怎样”。这种肌肉记忆才是学数据库最值钱的东西。