数据类型这东西说简单也简单无非就是建表时给字段选个类型说复杂也复杂我见过太多项目后期因为类型选错隐式转换导致索引失效、金额对不上账、自增主键逼近上限、字符集乱码然后加班排查。MySQL 第八章专门讲数据类型恰恰是很多人最容易跳过的章节但这一章几乎决定了你后续所有 SQL 的性能底子和数据可靠性。我写这篇文章不打算按教科书的方式平铺直叙而是把我在实际项目里踩过的坑、总结出的取舍逻辑结合 MySQL 8.0 的现状一次说清楚。内容适合刚学完 SQL 基础、准备自己设计数据库表的新手也适合已经写了几年 SQL 但从来没认真复盘过类型的同学。咱们直接进入正题。1. 数据类型为什么值得单独拿出“一章”来讲先把底层逻辑摆出来MySQL 里的数据类型表面上只在建表语句里占据一次位置实际上它同时决定了三件事——存储空间、比较/排序/计算的方式、以及索引能否高效工作。存储空间很好理解一个 TINYINT 占 1 字节一个 BIGINT 占 8 字节。单行差距只有 7 字节听着毫不起眼但一张一亿行的表光这一个字段就差了 700MB 空间。再加上二级索引对这个字段的冗余存储差距还会再翻倍。数据量越大类型选择的影响越放得开。比较方式则更隐蔽。CHAR、VARCHAR、TEXT 在比较时有字符集和排序规则参与整数比较用的是二进制补码DECIMAL 则是定点数精确比较FLOAT/DOUBLE 因为本身是近似值比较时稍不留神就会出现 0.1 0.2 不等于 0.3 的经典闹剧。还有一个杀手锏——隐式转换。当你把一个字符串类型的字段和数字做比较时MySQL 必须先把字符串转成数字这个过程会直接让字段上的索引失效查询从索引命中变成全表扫描。这属于典型的“建表时候埋雷上线后爆雷”。还有一个很多初学者没意识到的问题表结构的修改成本比改代码高一个数量级。改代码发个版就完事改字段类型要做 ALTER TABLE大表上动不动锁表几十分钟甚至几小时操作稍有不慎就会影响线上读写。所以类型选择的关键点不在“能不能跑”而在于“未来几年跑得稳不稳”。理解到这层就能明白这一章为什么值得认真看它不只是在教你“填什么类型”而是教你“如何把未来的数据库运维风险在第一天就规避掉”。2. 数值类型整数、定点数、浮点数之间差的不只是字节数2.1 整数类型从 TINYINT 到 BIGINT 的选择逻辑MySQL 提供了 5 种整数类型差别主要就是存储大小和可表示范围。我直接列一张表方便和官方文档对照类型存储字节有符号范围无符号范围TINYINT1-128 ~ 1270 ~ 255SMALLINT2-32768 ~ 327670 ~ 65535MEDIUMINT3-8388608 ~ 83886070 ~ 16777215INT4-2147483648 ~ 21474836470 ~ 4294967295BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615选型的核心逻辑其实就一句话按业务量的量级去推断未来的取值范围再留出足够的余量。如果你存的是用户年龄段TINYINT UNSIGNED 绰绰有余存订单状态、逻辑删除标记TINYINT 也够了。但如果是主键自增我的建议是至少用 BIGINT原因后面专门讲。这里先提一个很多人容易误会的点以前建表时经常看到int(11)这种写法括号里的 11 是“显示宽度”它不影响存储范围只影响零填充时补多少个零。MySQL 8.0 里这个显示宽度其实已经被废弃了写上int(11)和int在存储和计算上没有任何区别所以现在没必要再纠结要不要带括号。2.2 DECIMAL、FLOAT、DOUBLE精度才是核心差异整数之外带小数位的数值类型有三个FLOAT、DOUBLE、DECIMAL。它们之间最大的区别不是“谁的位数多”而是“存的是不是精确值”。FLOAT 和 DOUBLE 走的是 IEEE 754 浮点数标准它们是近似值。因为计算机内部用二进制存储小数很多十进制小数比如 0.1在二进制里是无限循环的所以最终存下来的是一个非常接近的近似数。DECIMAL 是定点数它把小数部分和整数部分分开存储完全按十进制处理不存在二进制近似问题。所以 DECIMAL 能精确表示小数适合金额、税率、百分比这类不容误差的数据。这个区别在实际项目里会引发什么后果我用一个真实场景说明某订单系统用 DOUBLE 存金额刚开始数据量小看不出问题跑了三个月财务对账发现有一分钱的差距怎么查都查不出来。最后定位到根源——DOUBLE 在累加过程中累积了微小的误差SUM 出来的结果和预期值差了 0.01。金额这种东西一分钱都不能错所以我现在的原则很简单凡是和钱相关的字段一律 DECIMAL绝不妥协。DECIMAL 定义的时候有一个格式坑要特别注意DECIMAL(M, D)M 是总位数精度D 是小数点后的位数。比如DECIMAL(10, 2)表示最多 8 位整数 2 位小数范围上限是 99999999.99。M 最大支持 65D 最大支持 30。如果超出定义的位数MySQL 在严格模式下会直接报错而不是悄悄帮你四舍五入这点在表设计评审时要预先算好。2.3 UNSIGNED 与 ZEROFILL不是所有修饰符都建议用UNSIGNED 把范围整体往正数方向平移适合明确不会出现负数的字段比如年龄、数量、积分。AUTO_INCREMENT 主键搭配 UNSIGNED 也确实能再扩大一倍上限。但这里有个隐蔽的坑多表关联时主键和外键的类型必须完全一致如果一张表主键用了 UNSIGNED另一张表外键忘了加JOIN 时两边类型不一致会触发隐式转换索引失效性能直线下降。还有一个更反直觉的问题两个都用了 UNSIGNED 的字段做减法比如id_a - id_b如果id_a id_b结果会是一个超大的无符号数而不是负数因为无符号数根本没有“负数”这个概念。这种隐蔽错误在生成报表、计算差值时特别容易踩到所以我个人现在对 UNSIGNED 的态度是能不用就不用尤其是需要参与运算的字段。ZEROFILL 就更不建议用了。它的作用是在数值前补零纯粹是显示层面的功能MySQL 8.0 里已经标记为废弃。如果你确实需要等宽的编码比如 000123 这样的编号应该用字符串类型去存而不是靠 ZEROFILL 硬凑。3. 字符串类型CHAR、VARCHAR、TEXT 和 ENUM 的取舍3.1 CHAR 与 VARCHAR一个空格都能引发血案CHAR(n) 和 VARCHAR(n) 里的 n 指的都是字符数不是字节数。区别在于存储方式CHAR(n) 是定长不论实际内容多短都要占 n 个字符的空间。检索快但浪费空间。如果内容不满末尾会用空格补齐而读取时又会把末尾空格去掉。VARCHAR(n) 是变长实际内容有多长就存多长额外用 1~2 个字节记录长度。节省空间但行数据更新时可能出现“页分裂”或“行迁移”的情况。这里有一个很多新手都不知道的细节CHAR 会截断末尾空格VARCHAR 不会。比如CHAR(5)存了abc取出时是abc但VARCHAR(5)存了abc abc 后面带两个空格取出时两个空格还在。如果你在应用层做字符串精确匹配或者用前后端做签名校验这俩空格就能让你疯掉。选 CHAR 还是 VARCHAR我一般按这个规则来长度固定且比较短比如手机号虽然是 11 位但国号前面的地方远古时期有过歧义、身份证号、订单号、MD5、固定编码用 CHAR。长度差异比较大比如姓名、地址、标题、备注用 VARCHAR。值更新频繁的字段固定长度较稳。InnoDB 表上VARCHAR 的变长特性配合 Dynamic 行格式对大字段表现更好。3.2 VARCHAR 与 TEXT超过容量该怎么选MySQL 里 TEXT 家族有 4 个成员TINYTEXT最大 255 字节、TEXT最大 64KB、MEDIUMTEXT最大 16MB、LONGTEXT最大 4GB。注意单位是字节而不是字符。所以如果是 utf8mb4 字符集一个中文字符占 4 字节TEXT 能存的中文字符数量要除以 4。VARCHAR 的最大长度理论上受行大小限制InnoDB 行最大 65535 字节所以VARCHAR(16383)在 utf8mb4 下差不多就是极限了。超过这个量级或者你要存的内容长度不可控就应该考虑 TEXT/MEDIUMTEXT。TEXT 和 VARCHAR 的差异不止长度还有几个实操层面必须知道的点TEXT 类型不能有默认值MySQL 8.0 之前8.0.13 开始有了默认值的支持但限制仍多插入时必须显式给值否则行为不太直观。TEXT 在查询时往往需要走“外部存储”读取大文本会引发磁盘 IO性能比 VARCHAR 差一截。TEXT 字段不能直接建普通索引只能建前缀索引比如INDEX idx_text(content(20))这会导致检索时无法使用覆盖索引。对 TEXT 字段做排序经常需要创建临时表尤其是配合 GROUP BY 或 DISTINCT 时临时表很吃内存。所以我的建议很直接能存 VARCHAR 就不要用 TEXT。文章正文、长评论这类非用不可的场景才上 TEXT/MEDIUMTEXT一般业务字段用 VARCHAR 就够了。3.3 ENUM 与 SET用对了省空间用错了锁表ENUM 是单选枚举SET 是多选集合。它们内部存储的值不是字符串本身而是整数索引所以非常省空间ENUM 最多占 2 字节SET 最多 8 字节。典型应用场景性别、状态机、角色权限的多选。我实际用下来ENUM 有两个坑值得提前说第一排序时不是按字母排而是按定义顺序排。如果你把ENUM(b, a, c)做 ORDER BY结果顺序是 b、a、c而不是 a、b、c。这会让很多开发者在排序结果出错时百思不得其解。第二修改 ENUM 的定义需要改表结构。虽然 MySQL 8.0 对部分 ALTER 支持在线 DDL但修改 ENUM 成员仍然涉及到全表数据的逻辑校验大表上执行时会触发表重建产生不小的 IO 压力和锁等待风险。如果你不确定枚举值未来会不会动不动变慎用 ENUM宁可多写一层关联表。SET 同理内部是按位存多选值最多 64 个选项。查询时用FIND_IN_SET或LIKE但这俩都不好走索引。数据量大且需要频繁筛选某个“包含选项”时SET 其实是灾难还不如拆成多张关联表或者用 JSON。3.4 字符集与排序规则utf8mb4 是不后悔的选择字符串类型和字符集是绑定的这里单独讲一下因为乱码和查询结果反直觉十次有八次是字符集问题。MySQL 里的utf8其实是一个历史遗留坑它指代的是 utf8mb3只支持 1~3 字节的 UTF-8 编码存不下 4 字节的 emoji 表情和部分生僻字。真正完整的 UTF-8 实现是utf8mb4。所以我现在建库建表字符集一律utf8mb4排序规则默认utf8mb4_unicode_ci或utf8mb4_0900_ai_ciMySQL 8.0 默认。还有一点容易忽略连接层的字符集也要统一。就算表和字段都设了 utf8mb4如果 JDBC 连接串里没有指定 characterEncodingutf8mb4老版本是 characterEncodingUTF-8写入的数据照样乱码。开发环境偶尔正常生产环境偶尔乱码这种玄学问题往往就是连接层没对齐。4. 日期时间类型TIMESTAMP、DATETIME、DATE 的选择要看清时区4.1 四种类型范围各异别混着用MySQL 里跟日期时间相关的类型有 5 个DATE、TIME、DATETIME、TIMESTAMP、YEAR。直接看表类型字节范围说明DATE31000-01-01 ~ 9999-12-31只存日期TIME3-838:59:59 ~ 838:59:59只存时间可带小数秒DATETIME81000-01-01 00:00:00 ~ 9999-12-31 23:59:59日期时间不随时区变化TIMESTAMP41970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC日期时间随时区自动转换YEAR11901 ~ 2155旧业务偶尔用4.2 最关键的差异TIMESTAMP 的时区行为TIMESTAMP 字段内部存的是 UTC 时间戳展示时会根据当前会话的time_zone参数自动转换成本地时间。也就是说同一个 TIMESTAMP 值在不同时区的客户端里查询看到的字符串可能不一样。DATETIME 则完全按你存的字面值返回不做任何时区转换。这个差异对业务的影响很实际如果做全球化的系统比如跨境电商、海外版 App统一用 TIMESTAMP 或统一存 UTC 时间展示时在应用层转时区会比 DATETIME 舒服得多。如果只是国内单时区业务比如后台管理系统用 DATETIME 会更直接因为库里的值就是开发人员看到的值排查问题时不用做时区换算。还有一个必须说的限制TIMESTAMP 的范围到 2038 年就截止了32 位时间戳的经典上限。新系统如果会存出生日期、预售日期这类远期时间直接 DATETIME 一了百了避免十年后二次迁移。4.3 别忘了默认值和自动更新建表时常见的操作是CREATE TABLE user_login ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );其中ON UPDATE CURRENT_TIMESTAMP会在每次 UPDATE 时自动把字段更新为当前时间省掉了应用层手动塞时间的代码。这是非常实用的做法但注意一个细节如果你更新某行时新值和旧值完全相同MySQL 默认不会触发ON UPDATE的逻辑如果想强制触发还得配合UPDATE ... SET updated_at NOW()或者在 JDBC 用ON DUPLICATE KEY UPDATE时显式处理。4.4 千万别用字符串存日期我见过不少项目为了“查询方便”把日期存成VARCHAR(19)或者直接用INT存时间戳。表面看好像没毛病实际坑到不行字符串排序是按字典序2024-1-05会排在2024-01-02前面结果完全错误。做日期函数运算DATE_ADD、DATE_DIFF时要先 CAST麻烦且容易出错。无法利用日期范围的条件索引优化扫描行数翻倍。最致命的是你用字符串存日期等于放弃了数据库对日期合法性的校验专门造出2024-02-30这种数据。我现在的原则就一条凡是日期必须用 DATE/DATETIME 类型。真到了需要把时间戳展示成特定格式那是应用层的活不该让数据库来迁就。5. 二进制、JSON、空间与位类型冷门但关键时刻很好用5.1 BINARY 与 VARBINARY存 MD5、UUID 的实际选择BINARY(n) 和 VARBINARY(n) 和 CHAR/VARCHAR 的定长变长逻辑完全一样但存的是原始字节比较也是逐字节比较不涉及字符集概念。比如一个 32 位的 MD5 值传统做法是CHAR(32)存十六进制字符串体积 32 字节如果转成二进制可以压缩到 16 字节UNHEX(md5_string)。省一半存储还更高效。同理UUID 可以用BINARY(16)去存配合触发器或应用层转换。但我不建议新手一上来就上二进制调试时全是乱码很痛苦。只有当表的行数足够大、存储节省能带来明显收益时才值得做这种“压缩”。5.2 BLOB 家族图片和文件到底该不该放数据库BLOB 家族TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB和 TEXT 家族体积对应但存的是二进制数据。最常见的应用迷思就是“把图片直接存数据库”。我的态度非常明确别把图片、音视频等大文件放 MySQL。原因有三数据库的备份恢复会变得更慢二进制大对象会占用大量 Buffer Pool 之外的空间而且每次读取都会把大文件从磁盘拉到内存再传输网络 IO 压力巨大。业界更通用的做法是文件存对象存储阿里云 OSS、腾讯云 COS、AWS S3 或自建的 MinIO数据库只存文件路径或对象键。当然也有例外某些对事务一致性要求极高的场景比如法律存证、票据留档必须把原始文件的二进制内容和业务记录放在同一事务里这时候用 LONGBLOB 是合理的但设计时要估算好单个文件的上限防止把数据库拖垮。5.3 JSON 类型MySQL 同时兼任文档数据库MySQL 5.7 开始引入原生 JSON 类型8.0 里已经相当成熟。最大的优势是插入时会自动校验 JSON 合法性从根上杜绝了“语法错误但被当成字符串存进去”的情况。查询用JSON_EXTRACT或者运算符-、-都行索引方面可以使用“生成列 索引”的方式ALTER TABLE product ADD COLUMN price_d DECIMAL(10,2) GENERATED ALWAYS AS (JSON_EXTRACT(attrs, $.price)) STORED, ADD INDEX idx_price (price_d);这个技巧让我在处理“配置类字段”时轻松了不少——可以随时扩展属性还能命中索引。但 JSON 类型也不是万能药它的缺点是更新 JSON 字段时是整个文档重写不是只改其中某个键JSON 子查询在很多场景下没法走普通索引存储上内部有额外的 JSON 二进制格式占空间比 VARCHAR 更大。所以我的使用边界是日志数据、配置快照、业务侧低频读取的弹性扩展字段用 JSON。需要高频查询、参与 JOIN 的核心业务字段老老实实拆成普通列。5.4 位类型与空间类型BIT 类型一般用来存标志位比如BIT(1)表示 0/1BIT(8)表示 8 个位。某些做 IoT 的项目喜欢把大量布尔标志位打包成一个 BIT 字段省空间但代码可读性会下降。MySQL 8.0 里 ANSI SQL 风格的BOOLEAN只是 TINYINT(1) 的别名并不是原生布尔类型这一点经常被误读。空间类型GEOMETRY、POINT、LINESTRING、POLYGON用于 GIS 场景比如地图围栏、最近门店查询。用的前提是你愿意引入空间索引和一系列空间函数。非 GIS 项目根本用不上知道存在即可。6. 表设计实战我踩过的 5 个真实教训帮你提前避坑6.1 主键自增为什么我一律建议 BIGINT很多教程为了节省存储教大家用 INT 做主键。诚然 INT 有四字节BIGINT 八字节但在现在这个业务体量下很多表三天两头就有几百万行数据加上流水、订单、日志类表的增长INT 的 21 亿上限真的会摸到。我不是在危言耸听。有一次线上告警某个订单表主键到了 21 亿的临界点AUTO_INCREMENT 直接无法写入业务大面积报错。运维连夜开会想办法切割表、重建主键。过程极其痛苦。教训就是主键这种低频更新、高频查询、涉及所有索引的字段别省那 4 个字节起步 BIGINT 是最稳的选择。6.2 DECIMAL 精度留够小数点后的折腾空间金额字段我见过DECIMAL(8, 2)和DECIMAL(10, 2)这类定义。看似够用但如果业务涉及积分、折扣、汇率、拼单返现小数位很容易超过两位。更典型的是报表对账SUM 出来的值离预期差个几分钱最后发现是类型精度问题。我的建议是金额用 DECIMAL(12~18, 4)多存两位“余量”需要展示时在应用层做四舍五入。多存四位不会让数据库崩掉但能让你免于“差一分钱查一整晚”。6.3 隐式转换慢查询里最常见的隐形杀手我在分析慢查询日志时几乎每周都能撞见“隐式转换”。最典型的表里字段是VARCHAR但查询条件传入的是数字。SELECT * FROM user WHERE phone 13800138000;如果phone是 VARCHARMySQL 会把phone字段整体转成数字再比较导致字段上的索引无法使用全表扫描。这个问题的修复方式很简单查询时带引号phone 13800138000。但线上代码经常忽略这个细节。还有一种情况是关联字段类型不一致A 表 user_id 是 BIGINTB 表 user_id 是 VARCHARJOIN 时必然有一端被隐式转换。排查时用EXPLAIN能看到关联键上出现Using where或全表扫描但具体原因往往要手动检查两张表的字段定义才能确认。所以表设计评审时多花一分钟检查关联字段类型是否完全一致能省掉未来无数慢查询优化时间。6.4 大字段与行溢出的连锁反应InnoDB 的行格式如果是 DYNAMIC8.0 默认VARCHAR、TEXT、BLOB 这些大字段在行内只存一个 20 字节的指针实际数据放在溢出页里。看起来挺美但查询时如果需要读出大字段内容就要额外访问溢出页磁盘 IO 增加。更麻烦的是大字段越多行迁移越频繁。当一行数据更新后长度增加原本的页放不下会把行搬到新的页产生碎片。表越来越大碎片率越来越高查询越来越慢。我的实操经验是把大字段单独拆到一张附表主表只保留核心高频字段附表通过主键关联。这种设计既保证了数据完整性又能让主表的小查询走内存命中。另外一个从建表就要想好的事别用SELECT *去取大字段。SQL 只 select 需要的列否则大字段会把整个查询拖成“慢查询”。6.5 预留一点“改造空间”类型设计要留退路最后一个经验其实是心态问题。见过太多同学建表时把类型卡得非常死比如用户名只设VARCHAR(10)、状态只设为ENUM(正常,冻结)、日期存成INT时间戳貌似精准合理结果业务一扩展全部不够用。类型设计我给的建议是在当前业务预估基础上再放宽一档。例如用户名在需求文档里说是“最多 6 个字”那表字段就设 VARCHAR(32)别卡在 20。状态机准备加一个“待审核”如果一开始就用了 ENUM光改定义就能让你脱一层皮不如直接 TINYINT 存状态码映射关系放应用层。还有一点如果真的需要 ALTER TABLE强烈建议用pt-online-schema-change这类工具操作而不是直接执行原生 ALTER。原生 ALTER 在数据量大的表上会锁写操作窗口内的所有写入都会阻塞。这类工具通过创建临时表、增量触发器同步数据的方式能做到在线变更虽然听起来绕但确实是运维保命的方案。最后分享一个我自己的设计习惯每张新表出来前把所有字段列个清单注明业务含义、预估值、是否需要索引、未来三到五年的增长预期然后逐项和类型对照一遍。这套流程看着繁琐但能挡住绝大多数因为类型选错而导致的线上事故。MySQL 数据类型这门课真正到你手上能用出来的价值不在背定义而在于每一列都经得起业务增长和时间流逝的考验。
