MySQL 也算是老熟人了但每次帮人看库表设计翻来覆去就那几个问题金额用什么类型存、状态字段用 tinyint 还是 enum、时间到底选 datetime 还是 timestamp。这些看着都是基础可真到线上就闹笑话。我之前接手过一个项目订单表金额用 float 存跑了一段时间对不上账查了半天是浮点精度丢了分。所以这篇就把 MySQL 常见数据类型系统地捋一遍从底层存储逻辑到实际选型建议争取让你看完就能直接用在建表上。1. 数值类型整数与小数背后的存储博弈数值类型是 MySQL 里用得最多、也最容易出问题的一类。很多人建表时随手写个int(11)或者decimal(10,2)实际上根本没搞明白括号里的数字是什么意思更没搞清楚每种类型在磁盘上到底占多大空间、能存多大的数。这一块我拆开讲。1.1 整数类型别再把 int(11) 当字符宽度先看整数族tinyint、smallint、mediumint、int、bigint。它们之间的区别就是存储字节数和可表示范围不同这一点几乎所有人都知道但有一个误区流传特别广int(11)里的 11 不是能存 11 位数字这个括号只是配合 zerofill 用的显示宽度跟存储范围一毛钱关系没有。类型字节数有符号范围无符号范围tinyint1-128 ~ 1270 ~ 255smallint2-32768 ~ 327670 ~ 65535mediumint3-8388608 ~ 83886070 ~ 16777215int4-2147483648 ~ 21474836470 ~ 4294967295bigint8±9.22×10^180 ~ 1.84×10^19选型逻辑也很简单主键 ID 直接上 bigint别抠那点空间省得以后数据量大了换类型折腾死人。状态字段、开关字段用 tinyint比如 0/1 表示是否删除、是否上架一个字节搞定。年龄、枚举数值这种小范围数据也用 tinyint。当你预计数量级在几万到几亿之间时用 int超过 int 范围就换 bigint。有个细节int(1) 和 int(11) 在存储上完全一样都能存 21 亿这个量级只是显示宽度不同。如果开了 zerofill不足位数前面补零比如int(4) zerofill存 12 会显示 0012。但说实话这功能日常开发基本用不到反而容易误导人我一般不建议用 zerofill。1.2 小数类型decimal 才是存钱的正解小数类型是重灾区。float 和 double 是浮点数底层用二进制存储很多十进制小数没法精确表示。0.1 在二进制里就是无限循环小数存进去再算出来尾数就漂了。你拿 float 存金额累计加减几次对不上账非常正常。decimal 是定点数按十进制位存储可以精确表示。语法是decimal(M, D)M 是总位数D 是小数位数。比如decimal(10, 2)表示总共 10 位小数点后占 2 位整数部分最多 8 位能存的最大值是 99999999.99。金额、单价、费率这类对精度敏感的数据一律用 decimal这是铁律。float 和 double 只适合用在科学计算、坐标、温度这类对精度不敏感、更看重取值范围的场景。有人问decimal 会不会占空间很大确实decimal 每 4 个字节存 9 个数字比 int 费地方但金融场景正确性优先空间成本排在后面。那 decimal 能做加减法吗当然可以而且比 float 更安全。1.3 布尔值tinyint(1) 的真相MySQL 没有专门的 boolean 类型boolean和bool都是 tinyint(1) 的别名存 0 表示 false非 0 表示 true。平时用 0/1 就够了别存其他值不然查询条件写起来很别扭。有些 ORM 框架会自动把 tinyint(1) 映射成 Java 的 Boolean但 tinyint(2) 不会这点在团队协作时要统一约定。2. 字符串类型定长与变长的空间换时间字符串类型看着简单但选错带来的性能问题很阴险。核心要搞清楚 char、varchar、text 三兄弟在磁盘和内存里的行为差异。2.1 char 与 varchar一个用空间换速度一个用速度换空间char 是定长字符串。你声明char(10)不管存一个字符还是十个字符它都固定占用 10 个字符的存储空间不够的右边补空格。读取时 MySQL 会把尾随空格去掉。因为定长MySQL 可以非常快速地定位到某一行某一列的数据不用去算偏移量所以等值比较和频繁访问的场景下 char 更快。varchar 是变长字符串varchar(10)表示最多存 10 个字符实际存储占用是“真实数据长度 变长长度标记”这个长度标记占 1~2 个字节。它省空间但每行数据长度不同定位数据时要额外计算稍微慢一点。怎么选一个经验法则长度基本固定的用 char比如 MD5 摘要32位、手机号11位虽然建议用 varchar 但 char 也合理、身份证号18位注意尾号可能带 X、性别、状态码这种。长度波动大的用 varchar比如用户名、邮箱、地址、备注等。varchar 的最大长度还受行大小限制。MySQL 单行最大 65535 字节不包括 text/blob 这类大字段比如一个varchar(65535)在 utf8mb4 字符集下最多能存约 16383 个字符但因为还要算变长标记和字节数实际到不了得留余量。一个容易被坑的点varchar 括号里写的是字符数不是字节数。在 utf8mb4 下一个汉字占 4 字节一个英文字母占 1 字节。varchar(255)最多存 255 个汉字或 255 个英文字母但占用的磁盘空间差四倍。2.2 text 家族大字段的代价你算过吗text、mediumtext、longtext 用来存长文本。text 最大 65535 字节mediumtext 最大 16MBlongtext 最大 4GB。但注意text 字段有个特性表里其余字段加上 text 的指针一共占 65535 字节的行空间text 的实际内容存在行外。也就是说即使 text 里有好几 MB 的数据行内只存一个 12 字节的指针。这就带来两个影响第一查询时如果用select *把 text 字段带上而数据量又大会把行外数据读出来IO 开销极大第二text 不能有默认值不能直接建普通索引只能建前缀索引比如index (content(50))或者全文索引。所以别再动不动就用 text 存一切了。产品描述、文章正文这种确实要用的单独拆表存业务查询时避免select *明确指定需要的字段。如果只是存个 JSON 配置或序列化对象可以试试 json 类型它比 text 多了校验和索引能力后面我会专门讲。3. 日期时间类型时区与精度一个都不能马虎日期时间类型是另一个容易埋雷的地方。MySQL 提供 date、time、datetime、timestamp、year 五种最常用的就是 datetime 和 timestamp很多人分不清建表时凭感觉选导致线上出现时间差八小时、2038 年问题之类的奇葩故障。3.1 datetime vs timestamp差的不只是范围datetime 存的是“墙上时间”范围是 1000-01-01 到 9999-12-31跟时区无关。你存什么查出来就是什么。它占 8 字节精度可以到微秒datetime(3) 表示毫秒datetime(6) 表示微秒。timestamp 存的是 UTC 时间戳范围是 1970-01-01 00:00:01 UTC 到 2038-01-01 03:14:07 UTC。它占 4 字节在查询时 MySQL 会根据当前会话的时区把时间戳转换成当地时间。这意味着同一张表里存的时间戳在不同时区的客户端查出来显示结果会不一样。看到这里你应该明白选型逻辑了如果业务是全球化的比如面向海外用户的系统要按用户本地时区展示时间用 timestamp 配合会话时区设置很省事。如果业务就是国内业务客户端和服务器都在同一时区或者需要存历史数据供跨时区审计用 datetime 更直接不会因为服务器时区配置错误导致时间偏移。之前遇到过一个问题服务器 time_zone 设置成 SYSTEM系统时区是 UTC结果表里的 created_at 显示的时间比北京时间慢了 8 小时。排查半天发现是部署时没设置default-time-zone 08:00。后来统一改成 datetime 或者显式指定时区问题才消停。3.2 时间精度与默认值建表时的细节决定成败日期时间类型的精度设置要结合业务。用户注册时间、订单创建时间到秒就够用datetime即可。秒杀活动、计费系统这类高并发场景用datetime(3)或者datetime(6)保留毫秒微秒否则同一秒内的多条记录排序结果不确定分页会出现数据跳动。默认值方面MySQL 5.6.5 之后支持DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。建表时给 created_at 设置DEFAULT CURRENT_TIMESTAMP给 updated_at 设置DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这样插入时自动写当前时间更新时自动刷新省去业务代码里手动 set 的麻烦。注意timestamp 的默认值只能是 CURRENT_TIMESTAMPdatetime 在 5.6 之前不能直接用函数做默认值只能靠代码写入。如果你还在用老版本 MySQ L建表时要特别当心别想当然。4. 特殊类型枚举、JSON 与空间数据的正确姿势除了上面几大类MySQL 还有几个特色类型很多人要么不用要么理解有偏差。这里挑最常用的三个讲讲。4.1 enum 与 set省空间但有性格enum 是枚举类型定义时列出所有允许的值比如enum(pending, paid, refunded)。它内部用数字索引存储实际只占 1~2 个字节比 varchar 省空间而且数据合法性在数据库层就校验了应用层不用额外判断。但 enum 有几个让人头疼的地方。第一排序时不是按字母排是按定义顺序排。第二如果枚举值要加新的执行 alter table 改 enum 定义在数据量大时可能锁表代价不小。第三因为内部存的是索引你在代码里查出来的是字符串但如果直接写 SQL 用数字比较where status 1也可能匹配到很容易搞混。我之前有个项目用 enum 存订单状态后来业务加了一个“已取消”alter 表结构时线上锁了近一分钟流量直接抖动。如果当初用 tinyint 代码层字典表加个映射就行根本不用动表结构。所以我的建议是枚举值极其稳定、打死不变、且数量少的场景才用 enum否则老老实实 tinyint 注释。set 和 enum 类似区别是 set 可以组合多个值适合做标签、权限位这种多选场景。但实际开发中遇到多选需求我更推荐用单独的关联表或者 JSON可扩展性和可读性都更好。set 的维护成本和 enum 一样高慎用。4.2 JSON 类型灵活背后的隐藏成本MySQL 5.7 开始原生支持 JSON 类型。它比 text 存 JSON 字符串强在哪一是插入时会做 JSON 格式校验格式错误的会被直接拒绝二是 MySQL 会对 JSON 做二进制编码查询时解析更快三是支持对 JSON 字段内部属性建立虚拟列索引性能比“取出来全表 LIKE”好一个量级。但代价也很明显JSON 字段无法设默认值8.0 之前修改 JSON 内容时 MySQL 会重写整个字段哪怕只改其中一个键代价跟整条更新差不多。另外JSON 类型让“表结构”这层约束变得模糊查询条件里有 WHERE json_col - $.status x 这种写法时索引和优化的难度直线上升。JSON 适合存什么样的数据就是“结构会变、查询不频繁、以展示为主”的扩展信息。比如用户扩展属性、第三方回调的原始报文。反之如果 JSON 里的某个字段要频繁参与 WHERE 过滤、JOIN 或统计就老老实实拆成独立列不要为了图省事塞进 JSON。4.3 空间与二进制类型用到时别用错空间类型 point、linestring、polygon 等在地图、LBS 业务里会用到。平时开发用不到但如果你做的是门店定位、骑手轨迹之类的功能要会用ST_GeomFromText和ST_Distance_Sphere这两个函数。MySQL 8.0 对空间索引的支持完善多了直接用SPATIAL INDEX建空间索引。二进制类型 binary、varbinary、blob 主要用于存图片、文件原文或者加密后的字节流。如果你只是存文件路径用 varchar 就够如果确实要存二进制内容优先考虑对象存储数据库里只存 URL。把大量文件塞进 MySQL把数据库当网盘用后面备份、同步、性能都会教做人。5. 实操一张表的数据类型选型全流程这一节我拿一个典型的电商订单表来演示完整选型过程。虽然看起来简单但每一步都有讲究而且我会把容易出错的地方重点标出来。需求是这样的订单表要记录订单号、用户ID、商品ID、下单数量、订单金额、订单状态、收货地址、下单时间、支付时间、备注信息、扩展属性。5.1 逐字段设计从业务语义倒推类型先看订单号业务上一般要求唯一、可读常见做法是“时间戳 随机数”或者“日期 序列”。因为不是自增主键我用 varchar(32) 存订单号加唯一索引字符集用 utf8mb4。如果订单号是纯数字也可以用 bigint但纯数字订单号可读性不如带前缀的字符串好而且订单号往往还带业务含义varchar 更灵活。用户ID 和商品ID这两类关联查询很频繁用 bigint 无符号。可能有人觉得用户量没那么大用 int 就够了。但问题是一旦以后数据量涨上来或者从别的系统合并用户数据int 可能撞顶到那时再改类型就牵一发动全身。主键和外键这种“会长期增长”的字段直接 bigint这是花最少的钱买最稳的保险。下单数量和商品单价数量用 int 还是 decimal数量固定是整数用 int 无符号就行。单价涉及金额精度必须 decimal(10, 2)。有人问“单价 decimal(10,2) 最大 99999999.99够不够”如果单件商品单价超千万那不是表设计问题是业务模式问题。金额字段合计也要算好总位数别把单个字段范围卡太死。订单状态我上面说了不用 enum用 tinyint 加注释。0 待支付、1 已支付、2 已发货、3 已完成、4 已取消、5 退款中再加一个索引查询状态统计都很方便。收货地址这个字段很有意思。很多人直接 varchar(255) 存整条地址。我的建议是拆成省市区和详细地址或者至少把详细地址 varchar(255) 存。但要注意地址长度地域差异很大有的地址很长varchar(255) 够用吗一般来说够但个别国际地址可能超。稳妥做法是拆省市区 code 详细地址。如果业务不要求按省市区筛选只做展示直接 varchar(255) 存整条简单省事。下单时间和支付时间datetime 就够。但如果后续要做“同一秒内订单先后顺序”的判断比如限购逻辑最好用 datetime(3)。支付时间允许为空不设默认值插入后再更新。注意updated_at 字段最好统一用 ON UPDATE CURRENT_TIMESTAMP这个习惯能省很多排查功夫。备注信息用 varchar(500) 还是 text备注一般没人会写超过几百字但如果有人从别的系统导入超长备注varchar(500) 会报错。稳妥做法是 varchar(1000) 或 text。不过 text 不能设默认值也不能直接索引所以如果只是展示用text 没问题。我个人的习惯是存业务备注用 varchar(1000)因为绝大多数场景 1000 字符以内足够还能保留一些操作上的便利。扩展属性这个用 JSON 类型存物流信息、发票信息、来源渠道这些结构不固定的数据。但记住如果“物流单号”要在查询里频繁过滤就别只放 JSON复制一份到独立 varchar 列再建索引。5.2 字符集与排序规则的连带影响类型选好之后字符集也得统一。MySQL 现在的主流是 utf8mb4它兼容标准 UTF-8能存 emoji 和生僻字。老项目里那种 utf8mb3就是旧版 utf8最多存 3 字节存 emoji 会报错遇到这种库建议尽早迁移。排序规则collation也要注意。utf8mb4_general_ci 比较快但精度不够utf8mb4_unicode_ci 更准确utf8mb4_0900_ai_ci 是 MySQL 8.0 的默认。如果是新项目直接用默认的 utf8mb4_0900_ai_ci 就行不用折腾。但有一点得记住如果表之间的 join 涉及不同字符集或排序规则MySQL 可能无法用索引甚至报错所以建库时统一规范后面少很多破事。下表是我个人在项目里常用的“字段类型速查表”建表时可以直接对照着参考业务场景推荐类型原因自增主键bigint unsigned避免 int 上限长期使用安全用户、商品 IDbigint关联外键统一类型避免类型不匹配订单状态tinyint空间小扩展灵活加注释维护方便金额decimal(12,2)精度准确支持大额数量int unsigned整数场景足够手机号varchar(20)考虑国际化和未来扩展邮箱varchar(64)极少有超长邮箱IP 地址varchar(45)兼容 IPv6状态开关tinyint(1)0/1 布尔语义描述/备注varchar(1000)比 text 更灵活长文章mediumtext远超 text 上限又不至于到 longtext创建时间datetime与时区无关直观更新时间datetime ON UPDATE自动维护省代码JSON 扩展JSON结构化校验支持索引文件路径varchar(255)存 URL别存文件本身5.3 建表 SQL 示例与优化点说了这么多直接上一条完整的建表语句你可以拿去改改用CREATE TABLE t_order ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 主键ID, order_no varchar(32) NOT NULL COMMENT 订单号, user_id bigint unsigned NOT NULL COMMENT 用户ID, sku_id bigint unsigned NOT NULL COMMENT 商品ID, quantity int unsigned NOT NULL DEFAULT 1 COMMENT 下单数量, unit_price decimal(12,2) NOT NULL COMMENT 商品单价, total_amount decimal(12,2) NOT NULL COMMENT 订单总金额, status tinyint NOT NULL DEFAULT 0 COMMENT 订单状态0-待支付 1-已支付 2-已发货 3-已完成 4-已取消 5-退款中, province_code varchar(10) NOT NULL COMMENT 省CODE, city_code varchar(10) NOT NULL COMMENT 市CODE, district_code varchar(10) NOT NULL COMMENT 区CODE, detail_address varchar(255) NOT NULL COMMENT 详细地址, remark varchar(1000) DEFAULT NULL COMMENT 用户备注, ext_info json DEFAULT NULL 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_order_no (order_no), KEY idx_user_created (user_id, created_at), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单表;这条 SQL 里有几个优化点值得说一下。uk_order_no唯一索引保证订单号不重复idx_user_created是联合索引专门服务“查某个用户的订单列表并按时间排序”这个高频查询idx_status是单列索引服务后台按状态筛选订单。索引不是越多越好每个索引都要占空间、影响写入性能建的时候一定要对应到具体查询场景。联合索引有个细节要理解idx_user_created的字段顺序是 user_id 在前、created_at 在后因为等值查询 user_id 在前才能让 created_at 走索引排序。如果反过来写成(created_at, user_id)那查用户订单时联合索引的左前缀失效created_at 只能做普通排序过滤效率差很多。6. 常见问题与类型选择需要避开的坑即使是老手也难免在数据类型上踩坑这里把我在实际项目中遇到频率最高、也比较典型的问题整理出来做个速查你如果也踩了类似的坑直接对号入座就行。6.1 问题速查表从现象到根因问题现象根因解决方式金额计算后小数尾数对不上用 float/double 存金额改为 decimal禁止用浮点存钱插入中文变成问号字符集不是 utf8mb4库/表/列统一 utf8mb4时间显示比北京晚 8 小时服务端时区配置为 UTC设置 default-time-zone08:002038 年之后数据报错timestamp 范围上限改用 datetimevarchar 字段存超长文本报错长度算少了评估业务上限必要时用 textenum 排序结果不对enum 按定义顺序排改用 tinyint 字典表JSON 内容更新很慢JSON 整字段重写拆高频字段为独立列int 主键到上限插入失败int 4 字节上限改 bigintemoji 插入报错字符集用了 utf8mb3迁移到 utf8mb46.2 关于隐式转换类型不一致的代价还有一类问题特别隐蔽就是隐式类型转换。MySQL 在比较不同数据类型时会自动把一侧转成另一侧。这个机制经常会让索引失效。最典型的例子字段是 varchar但查询条件里传的是数字比如where phone 13800138000MySQL 会把字符串字段转成数字再比较导致 phone 上的索引用不上全表扫描。反过来也一样字段是 int条件里写字符串where id 1MySQL 会把字符串转成数字虽然也能命中索引但额外多一次转换开销。更严谨的做法是代码里传参时类型要和字段一致避免数据库做隐式转换。这个坑在建表选型时就要心里有数字符串和数字不要混用。6.3 字段扩展的代价建表时多想一步数据库表一旦上线后续再修改数据类型的成本非常高。varchar 改大一点可能触发表重建数据量大时直接锁表text 改 longtext 在某些条件下也要复制数据enum 加枚举值也可能锁表。我在之前的项目里吃过亏评论表 content 一开始用 varchar(500)后来产品说要支持 2000 字长评论alter 表时 500 万行数据锁了近一分钟线上请求积压。后来学乖了给这类“未来可能变长”的字段直接留足空间varchar(2000) 或 text。varchar(255) 以内和 varchar(256) 以上在行存储格式上还有一些差异所以能用 255 就 255要超就直接给大点别卡在边界上反复改。7. 我的实测心得与扩展建议数据类型这件事说到底是一个 trade-off空间、性能、可维护性、扩展性四个维度互相牵制。真正的高手不是背下所有类型的语法而是在建表前把业务读透把每个字段未来三五年内的变化趋势想清楚。有一个我最近在尝试的新方向是把类型选择和监控体系打通。给表增加元数据管理把每个字段的类型、用途、责任人维护到数据字典里用 CI 流水线做 SQL Review自动拦截 float 存金额、int 做关联字段这类不符合规范的表结构。这种团队的工程化积累比单纯靠个人经验靠谱得多。最后再分享一个我自己的土办法每次建完表用SHOW CREATE TABLE仔细看一遍再对着 EXPLAIN 跑几条核心查询确认索引没有失效。这一步确实花不了几分钟但能提前发现很多类型选择上的隐患。等表上线了、数据进去了再想改就不是几分钟的事了。
MySQL数据类型选型指南：从底层原理到建表实战
MySQL 也算是老熟人了但每次帮人看库表设计翻来覆去就那几个问题金额用什么类型存、状态字段用 tinyint 还是 enum、时间到底选 datetime 还是 timestamp。这些看着都是基础可真到线上就闹笑话。我之前接手过一个项目订单表金额用 float 存跑了一段时间对不上账查了半天是浮点精度丢了分。所以这篇就把 MySQL 常见数据类型系统地捋一遍从底层存储逻辑到实际选型建议争取让你看完就能直接用在建表上。1. 数值类型整数与小数背后的存储博弈数值类型是 MySQL 里用得最多、也最容易出问题的一类。很多人建表时随手写个int(11)或者decimal(10,2)实际上根本没搞明白括号里的数字是什么意思更没搞清楚每种类型在磁盘上到底占多大空间、能存多大的数。这一块我拆开讲。1.1 整数类型别再把 int(11) 当字符宽度先看整数族tinyint、smallint、mediumint、int、bigint。它们之间的区别就是存储字节数和可表示范围不同这一点几乎所有人都知道但有一个误区流传特别广int(11)里的 11 不是能存 11 位数字这个括号只是配合 zerofill 用的显示宽度跟存储范围一毛钱关系没有。类型字节数有符号范围无符号范围tinyint1-128 ~ 1270 ~ 255smallint2-32768 ~ 327670 ~ 65535mediumint3-8388608 ~ 83886070 ~ 16777215int4-2147483648 ~ 21474836470 ~ 4294967295bigint8±9.22×10^180 ~ 1.84×10^19选型逻辑也很简单主键 ID 直接上 bigint别抠那点空间省得以后数据量大了换类型折腾死人。状态字段、开关字段用 tinyint比如 0/1 表示是否删除、是否上架一个字节搞定。年龄、枚举数值这种小范围数据也用 tinyint。当你预计数量级在几万到几亿之间时用 int超过 int 范围就换 bigint。有个细节int(1) 和 int(11) 在存储上完全一样都能存 21 亿这个量级只是显示宽度不同。如果开了 zerofill不足位数前面补零比如int(4) zerofill存 12 会显示 0012。但说实话这功能日常开发基本用不到反而容易误导人我一般不建议用 zerofill。1.2 小数类型decimal 才是存钱的正解小数类型是重灾区。float 和 double 是浮点数底层用二进制存储很多十进制小数没法精确表示。0.1 在二进制里就是无限循环小数存进去再算出来尾数就漂了。你拿 float 存金额累计加减几次对不上账非常正常。decimal 是定点数按十进制位存储可以精确表示。语法是decimal(M, D)M 是总位数D 是小数位数。比如decimal(10, 2)表示总共 10 位小数点后占 2 位整数部分最多 8 位能存的最大值是 99999999.99。金额、单价、费率这类对精度敏感的数据一律用 decimal这是铁律。float 和 double 只适合用在科学计算、坐标、温度这类对精度不敏感、更看重取值范围的场景。有人问decimal 会不会占空间很大确实decimal 每 4 个字节存 9 个数字比 int 费地方但金融场景正确性优先空间成本排在后面。那 decimal 能做加减法吗当然可以而且比 float 更安全。1.3 布尔值tinyint(1) 的真相MySQL 没有专门的 boolean 类型boolean和bool都是 tinyint(1) 的别名存 0 表示 false非 0 表示 true。平时用 0/1 就够了别存其他值不然查询条件写起来很别扭。有些 ORM 框架会自动把 tinyint(1) 映射成 Java 的 Boolean但 tinyint(2) 不会这点在团队协作时要统一约定。2. 字符串类型定长与变长的空间换时间字符串类型看着简单但选错带来的性能问题很阴险。核心要搞清楚 char、varchar、text 三兄弟在磁盘和内存里的行为差异。2.1 char 与 varchar一个用空间换速度一个用速度换空间char 是定长字符串。你声明char(10)不管存一个字符还是十个字符它都固定占用 10 个字符的存储空间不够的右边补空格。读取时 MySQL 会把尾随空格去掉。因为定长MySQL 可以非常快速地定位到某一行某一列的数据不用去算偏移量所以等值比较和频繁访问的场景下 char 更快。varchar 是变长字符串varchar(10)表示最多存 10 个字符实际存储占用是“真实数据长度 变长长度标记”这个长度标记占 1~2 个字节。它省空间但每行数据长度不同定位数据时要额外计算稍微慢一点。怎么选一个经验法则长度基本固定的用 char比如 MD5 摘要32位、手机号11位虽然建议用 varchar 但 char 也合理、身份证号18位注意尾号可能带 X、性别、状态码这种。长度波动大的用 varchar比如用户名、邮箱、地址、备注等。varchar 的最大长度还受行大小限制。MySQL 单行最大 65535 字节不包括 text/blob 这类大字段比如一个varchar(65535)在 utf8mb4 字符集下最多能存约 16383 个字符但因为还要算变长标记和字节数实际到不了得留余量。一个容易被坑的点varchar 括号里写的是字符数不是字节数。在 utf8mb4 下一个汉字占 4 字节一个英文字母占 1 字节。varchar(255)最多存 255 个汉字或 255 个英文字母但占用的磁盘空间差四倍。2.2 text 家族大字段的代价你算过吗text、mediumtext、longtext 用来存长文本。text 最大 65535 字节mediumtext 最大 16MBlongtext 最大 4GB。但注意text 字段有个特性表里其余字段加上 text 的指针一共占 65535 字节的行空间text 的实际内容存在行外。也就是说即使 text 里有好几 MB 的数据行内只存一个 12 字节的指针。这就带来两个影响第一查询时如果用select *把 text 字段带上而数据量又大会把行外数据读出来IO 开销极大第二text 不能有默认值不能直接建普通索引只能建前缀索引比如index (content(50))或者全文索引。所以别再动不动就用 text 存一切了。产品描述、文章正文这种确实要用的单独拆表存业务查询时避免select *明确指定需要的字段。如果只是存个 JSON 配置或序列化对象可以试试 json 类型它比 text 多了校验和索引能力后面我会专门讲。3. 日期时间类型时区与精度一个都不能马虎日期时间类型是另一个容易埋雷的地方。MySQL 提供 date、time、datetime、timestamp、year 五种最常用的就是 datetime 和 timestamp很多人分不清建表时凭感觉选导致线上出现时间差八小时、2038 年问题之类的奇葩故障。3.1 datetime vs timestamp差的不只是范围datetime 存的是“墙上时间”范围是 1000-01-01 到 9999-12-31跟时区无关。你存什么查出来就是什么。它占 8 字节精度可以到微秒datetime(3) 表示毫秒datetime(6) 表示微秒。timestamp 存的是 UTC 时间戳范围是 1970-01-01 00:00:01 UTC 到 2038-01-01 03:14:07 UTC。它占 4 字节在查询时 MySQL 会根据当前会话的时区把时间戳转换成当地时间。这意味着同一张表里存的时间戳在不同时区的客户端查出来显示结果会不一样。看到这里你应该明白选型逻辑了如果业务是全球化的比如面向海外用户的系统要按用户本地时区展示时间用 timestamp 配合会话时区设置很省事。如果业务就是国内业务客户端和服务器都在同一时区或者需要存历史数据供跨时区审计用 datetime 更直接不会因为服务器时区配置错误导致时间偏移。之前遇到过一个问题服务器 time_zone 设置成 SYSTEM系统时区是 UTC结果表里的 created_at 显示的时间比北京时间慢了 8 小时。排查半天发现是部署时没设置default-time-zone 08:00。后来统一改成 datetime 或者显式指定时区问题才消停。3.2 时间精度与默认值建表时的细节决定成败日期时间类型的精度设置要结合业务。用户注册时间、订单创建时间到秒就够用datetime即可。秒杀活动、计费系统这类高并发场景用datetime(3)或者datetime(6)保留毫秒微秒否则同一秒内的多条记录排序结果不确定分页会出现数据跳动。默认值方面MySQL 5.6.5 之后支持DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。建表时给 created_at 设置DEFAULT CURRENT_TIMESTAMP给 updated_at 设置DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP这样插入时自动写当前时间更新时自动刷新省去业务代码里手动 set 的麻烦。注意timestamp 的默认值只能是 CURRENT_TIMESTAMPdatetime 在 5.6 之前不能直接用函数做默认值只能靠代码写入。如果你还在用老版本 MySQ L建表时要特别当心别想当然。4. 特殊类型枚举、JSON 与空间数据的正确姿势除了上面几大类MySQL 还有几个特色类型很多人要么不用要么理解有偏差。这里挑最常用的三个讲讲。4.1 enum 与 set省空间但有性格enum 是枚举类型定义时列出所有允许的值比如enum(pending, paid, refunded)。它内部用数字索引存储实际只占 1~2 个字节比 varchar 省空间而且数据合法性在数据库层就校验了应用层不用额外判断。但 enum 有几个让人头疼的地方。第一排序时不是按字母排是按定义顺序排。第二如果枚举值要加新的执行 alter table 改 enum 定义在数据量大时可能锁表代价不小。第三因为内部存的是索引你在代码里查出来的是字符串但如果直接写 SQL 用数字比较where status 1也可能匹配到很容易搞混。我之前有个项目用 enum 存订单状态后来业务加了一个“已取消”alter 表结构时线上锁了近一分钟流量直接抖动。如果当初用 tinyint 代码层字典表加个映射就行根本不用动表结构。所以我的建议是枚举值极其稳定、打死不变、且数量少的场景才用 enum否则老老实实 tinyint 注释。set 和 enum 类似区别是 set 可以组合多个值适合做标签、权限位这种多选场景。但实际开发中遇到多选需求我更推荐用单独的关联表或者 JSON可扩展性和可读性都更好。set 的维护成本和 enum 一样高慎用。4.2 JSON 类型灵活背后的隐藏成本MySQL 5.7 开始原生支持 JSON 类型。它比 text 存 JSON 字符串强在哪一是插入时会做 JSON 格式校验格式错误的会被直接拒绝二是 MySQL 会对 JSON 做二进制编码查询时解析更快三是支持对 JSON 字段内部属性建立虚拟列索引性能比“取出来全表 LIKE”好一个量级。但代价也很明显JSON 字段无法设默认值8.0 之前修改 JSON 内容时 MySQL 会重写整个字段哪怕只改其中一个键代价跟整条更新差不多。另外JSON 类型让“表结构”这层约束变得模糊查询条件里有 WHERE json_col - $.status x 这种写法时索引和优化的难度直线上升。JSON 适合存什么样的数据就是“结构会变、查询不频繁、以展示为主”的扩展信息。比如用户扩展属性、第三方回调的原始报文。反之如果 JSON 里的某个字段要频繁参与 WHERE 过滤、JOIN 或统计就老老实实拆成独立列不要为了图省事塞进 JSON。4.3 空间与二进制类型用到时别用错空间类型 point、linestring、polygon 等在地图、LBS 业务里会用到。平时开发用不到但如果你做的是门店定位、骑手轨迹之类的功能要会用ST_GeomFromText和ST_Distance_Sphere这两个函数。MySQL 8.0 对空间索引的支持完善多了直接用SPATIAL INDEX建空间索引。二进制类型 binary、varbinary、blob 主要用于存图片、文件原文或者加密后的字节流。如果你只是存文件路径用 varchar 就够如果确实要存二进制内容优先考虑对象存储数据库里只存 URL。把大量文件塞进 MySQL把数据库当网盘用后面备份、同步、性能都会教做人。5. 实操一张表的数据类型选型全流程这一节我拿一个典型的电商订单表来演示完整选型过程。虽然看起来简单但每一步都有讲究而且我会把容易出错的地方重点标出来。需求是这样的订单表要记录订单号、用户ID、商品ID、下单数量、订单金额、订单状态、收货地址、下单时间、支付时间、备注信息、扩展属性。5.1 逐字段设计从业务语义倒推类型先看订单号业务上一般要求唯一、可读常见做法是“时间戳 随机数”或者“日期 序列”。因为不是自增主键我用 varchar(32) 存订单号加唯一索引字符集用 utf8mb4。如果订单号是纯数字也可以用 bigint但纯数字订单号可读性不如带前缀的字符串好而且订单号往往还带业务含义varchar 更灵活。用户ID 和商品ID这两类关联查询很频繁用 bigint 无符号。可能有人觉得用户量没那么大用 int 就够了。但问题是一旦以后数据量涨上来或者从别的系统合并用户数据int 可能撞顶到那时再改类型就牵一发动全身。主键和外键这种“会长期增长”的字段直接 bigint这是花最少的钱买最稳的保险。下单数量和商品单价数量用 int 还是 decimal数量固定是整数用 int 无符号就行。单价涉及金额精度必须 decimal(10, 2)。有人问“单价 decimal(10,2) 最大 99999999.99够不够”如果单件商品单价超千万那不是表设计问题是业务模式问题。金额字段合计也要算好总位数别把单个字段范围卡太死。订单状态我上面说了不用 enum用 tinyint 加注释。0 待支付、1 已支付、2 已发货、3 已完成、4 已取消、5 退款中再加一个索引查询状态统计都很方便。收货地址这个字段很有意思。很多人直接 varchar(255) 存整条地址。我的建议是拆成省市区和详细地址或者至少把详细地址 varchar(255) 存。但要注意地址长度地域差异很大有的地址很长varchar(255) 够用吗一般来说够但个别国际地址可能超。稳妥做法是拆省市区 code 详细地址。如果业务不要求按省市区筛选只做展示直接 varchar(255) 存整条简单省事。下单时间和支付时间datetime 就够。但如果后续要做“同一秒内订单先后顺序”的判断比如限购逻辑最好用 datetime(3)。支付时间允许为空不设默认值插入后再更新。注意updated_at 字段最好统一用 ON UPDATE CURRENT_TIMESTAMP这个习惯能省很多排查功夫。备注信息用 varchar(500) 还是 text备注一般没人会写超过几百字但如果有人从别的系统导入超长备注varchar(500) 会报错。稳妥做法是 varchar(1000) 或 text。不过 text 不能设默认值也不能直接索引所以如果只是展示用text 没问题。我个人的习惯是存业务备注用 varchar(1000)因为绝大多数场景 1000 字符以内足够还能保留一些操作上的便利。扩展属性这个用 JSON 类型存物流信息、发票信息、来源渠道这些结构不固定的数据。但记住如果“物流单号”要在查询里频繁过滤就别只放 JSON复制一份到独立 varchar 列再建索引。5.2 字符集与排序规则的连带影响类型选好之后字符集也得统一。MySQL 现在的主流是 utf8mb4它兼容标准 UTF-8能存 emoji 和生僻字。老项目里那种 utf8mb3就是旧版 utf8最多存 3 字节存 emoji 会报错遇到这种库建议尽早迁移。排序规则collation也要注意。utf8mb4_general_ci 比较快但精度不够utf8mb4_unicode_ci 更准确utf8mb4_0900_ai_ci 是 MySQL 8.0 的默认。如果是新项目直接用默认的 utf8mb4_0900_ai_ci 就行不用折腾。但有一点得记住如果表之间的 join 涉及不同字符集或排序规则MySQL 可能无法用索引甚至报错所以建库时统一规范后面少很多破事。下表是我个人在项目里常用的“字段类型速查表”建表时可以直接对照着参考业务场景推荐类型原因自增主键bigint unsigned避免 int 上限长期使用安全用户、商品 IDbigint关联外键统一类型避免类型不匹配订单状态tinyint空间小扩展灵活加注释维护方便金额decimal(12,2)精度准确支持大额数量int unsigned整数场景足够手机号varchar(20)考虑国际化和未来扩展邮箱varchar(64)极少有超长邮箱IP 地址varchar(45)兼容 IPv6状态开关tinyint(1)0/1 布尔语义描述/备注varchar(1000)比 text 更灵活长文章mediumtext远超 text 上限又不至于到 longtext创建时间datetime与时区无关直观更新时间datetime ON UPDATE自动维护省代码JSON 扩展JSON结构化校验支持索引文件路径varchar(255)存 URL别存文件本身5.3 建表 SQL 示例与优化点说了这么多直接上一条完整的建表语句你可以拿去改改用CREATE TABLE t_order ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 主键ID, order_no varchar(32) NOT NULL COMMENT 订单号, user_id bigint unsigned NOT NULL COMMENT 用户ID, sku_id bigint unsigned NOT NULL COMMENT 商品ID, quantity int unsigned NOT NULL DEFAULT 1 COMMENT 下单数量, unit_price decimal(12,2) NOT NULL COMMENT 商品单价, total_amount decimal(12,2) NOT NULL COMMENT 订单总金额, status tinyint NOT NULL DEFAULT 0 COMMENT 订单状态0-待支付 1-已支付 2-已发货 3-已完成 4-已取消 5-退款中, province_code varchar(10) NOT NULL COMMENT 省CODE, city_code varchar(10) NOT NULL COMMENT 市CODE, district_code varchar(10) NOT NULL COMMENT 区CODE, detail_address varchar(255) NOT NULL COMMENT 详细地址, remark varchar(1000) DEFAULT NULL COMMENT 用户备注, ext_info json DEFAULT NULL 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_order_no (order_no), KEY idx_user_created (user_id, created_at), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单表;这条 SQL 里有几个优化点值得说一下。uk_order_no唯一索引保证订单号不重复idx_user_created是联合索引专门服务“查某个用户的订单列表并按时间排序”这个高频查询idx_status是单列索引服务后台按状态筛选订单。索引不是越多越好每个索引都要占空间、影响写入性能建的时候一定要对应到具体查询场景。联合索引有个细节要理解idx_user_created的字段顺序是 user_id 在前、created_at 在后因为等值查询 user_id 在前才能让 created_at 走索引排序。如果反过来写成(created_at, user_id)那查用户订单时联合索引的左前缀失效created_at 只能做普通排序过滤效率差很多。6. 常见问题与类型选择需要避开的坑即使是老手也难免在数据类型上踩坑这里把我在实际项目中遇到频率最高、也比较典型的问题整理出来做个速查你如果也踩了类似的坑直接对号入座就行。6.1 问题速查表从现象到根因问题现象根因解决方式金额计算后小数尾数对不上用 float/double 存金额改为 decimal禁止用浮点存钱插入中文变成问号字符集不是 utf8mb4库/表/列统一 utf8mb4时间显示比北京晚 8 小时服务端时区配置为 UTC设置 default-time-zone08:002038 年之后数据报错timestamp 范围上限改用 datetimevarchar 字段存超长文本报错长度算少了评估业务上限必要时用 textenum 排序结果不对enum 按定义顺序排改用 tinyint 字典表JSON 内容更新很慢JSON 整字段重写拆高频字段为独立列int 主键到上限插入失败int 4 字节上限改 bigintemoji 插入报错字符集用了 utf8mb3迁移到 utf8mb46.2 关于隐式转换类型不一致的代价还有一类问题特别隐蔽就是隐式类型转换。MySQL 在比较不同数据类型时会自动把一侧转成另一侧。这个机制经常会让索引失效。最典型的例子字段是 varchar但查询条件里传的是数字比如where phone 13800138000MySQL 会把字符串字段转成数字再比较导致 phone 上的索引用不上全表扫描。反过来也一样字段是 int条件里写字符串where id 1MySQL 会把字符串转成数字虽然也能命中索引但额外多一次转换开销。更严谨的做法是代码里传参时类型要和字段一致避免数据库做隐式转换。这个坑在建表选型时就要心里有数字符串和数字不要混用。6.3 字段扩展的代价建表时多想一步数据库表一旦上线后续再修改数据类型的成本非常高。varchar 改大一点可能触发表重建数据量大时直接锁表text 改 longtext 在某些条件下也要复制数据enum 加枚举值也可能锁表。我在之前的项目里吃过亏评论表 content 一开始用 varchar(500)后来产品说要支持 2000 字长评论alter 表时 500 万行数据锁了近一分钟线上请求积压。后来学乖了给这类“未来可能变长”的字段直接留足空间varchar(2000) 或 text。varchar(255) 以内和 varchar(256) 以上在行存储格式上还有一些差异所以能用 255 就 255要超就直接给大点别卡在边界上反复改。7. 我的实测心得与扩展建议数据类型这件事说到底是一个 trade-off空间、性能、可维护性、扩展性四个维度互相牵制。真正的高手不是背下所有类型的语法而是在建表前把业务读透把每个字段未来三五年内的变化趋势想清楚。有一个我最近在尝试的新方向是把类型选择和监控体系打通。给表增加元数据管理把每个字段的类型、用途、责任人维护到数据字典里用 CI 流水线做 SQL Review自动拦截 float 存金额、int 做关联字段这类不符合规范的表结构。这种团队的工程化积累比单纯靠个人经验靠谱得多。最后再分享一个我自己的土办法每次建完表用SHOW CREATE TABLE仔细看一遍再对着 EXPLAIN 跑几条核心查询确认索引没有失效。这一步确实花不了几分钟但能提前发现很多类型选择上的隐患。等表上线了、数据进去了再想改就不是几分钟的事了。
