数据类型与表约束从一次查不出数据的事故说起几个月前我接手过一个让人头疼的线上问题。业务方反馈说后台按订单号查订单明明数据就在库里SQL语句也执行成功可结果就是空的。我第一反应是索引失效结果EXPLAIN一看走了主键连扫描行数都是1。排查到最后才发现问题出在建表时的数据类型上——订单号字段用的是VARCHAR但代码里查询时传的是整数类型转换把索引路径彻底绕乱了。类似的坑我在MySQL里踩过不止一次。这篇文章想系统的聊一聊MySQL的数据类型和表约束这两个最基础、也最容易被忽视的东西。它适合初级开发把概念吃透也适合写惯了业务代码、很少回头审视DDL的老手查漏补缺。这里不谈花哨的优化技巧只把怎么选字段类型、怎么设计约束、为什么这样做讲清楚顺带分享一些我实际踩过、也帮别人排查过的真实案例。1. 数据类型选型先想清楚字段的生命周期再动手很多人在建表时有个习惯看到数字就INT看到文字就VARCHAR(255)日期就DATETIME。这套三件套能应付大部分场景但应付不了所有场景。选数据类型真正要回答的问题是这个字段会存什么、存多大、要不要参与计算、会不会成为查询条件、未来三到五年会不会超范围。想清楚这几点再落笔写DDL。1.1 整数类型别再一上来就INTMySQL的整数类型是按存储字节数划分的从TINYINT到BIGINT每一档都有明确的取值范围。类型存储占用有符号范围无符号范围TINYINT1字节-128 ~ 1270 ~ 255SMALLINT2字节-32768 ~ 327670 ~ 65535MEDIUMINT3字节-8388608 ~ 83886070 ~ 16777215INT4字节-2147483648 ~ 21474836470 ~ 4294967295BIGINT8字节大约 ±922亿亿大约 0 ~ 1844亿亿这里我多说一句无符号UNSIGNED并不是多一位存更大那么简单。它的本质是把负数那半边让给正数。比如订单量、访问量、年龄这类业务上本来就不可能为负的字段用UNSIGNED能白赚一倍上限。但如果你加上了UNSIGNED后面做减法运算就很容易踩BIGINT UNSIGNED value is out of range的报错因为结果一旦变负就溢出了。我在几个项目里都遇到过这个问题后来干脆统一约定业务上可能做差值计算的字段一律用有符号省得在SQL层处理烦人。再聊一个常见的混淆点我们常说的INT(11)里的11。MySQL 8.0之前这个数字叫显示宽度配合ZEROFILL可以补零展示但它不限制存储范围。INT(1)和INT(11)能存的值一模一样都是21亿。MySQL 8.0.17之后显示宽度语法已经废弃了看到老文档里写INT(11)直接忽略前面那个括号就行。真正限制范围的只有类型本身。实际选型建议业务表主键用BIGINT UNSIGNED别问为什么等你哪天INT主键涨到21亿就知道疼了状态码、性别、删除标记用TINYINT地区编码、端口号用SMALLINT订单金额如果按分存用BIGINT或者DECIMAL都行但永远不要用INT存金额的元算上折扣和退款很容易就爆了。1.2 字符类型VARCHAR(255)的由来与澄清字符类型这里最容易被误解的就是VARCHAR和CHAR的取舍。VARCHAR是变长存储实际存多少字符就占用相应字节加一点额外字节记录长度CHAR是定长存储不管存没存满都占用声明长度。MySQL对CHAR的存储做了优化尾部空格会被去掉这有个坑后面讲。很多人习惯把字符串都写成VARCHAR(255)理由是够用。但255这个数字其实来源于一个历史限制VARCHAR存储长度前缀需要额外字节当长度小于等于255时只需1字节前缀超过则需要2字节。这个说法本身没问题可它被过度使用了。你要存一个URL动辄几百字符存一个手机号是11位存一个邮箱最多也就两三百。全都用VARCHAR(255)除了浪费内存排序空间还让MySQL无法对列长度做更精细的优化。这里有必要讲清楚VARCHAR(N)的N到底指什么N是字符数不是字节数。在utf8mb4字符集下一个汉字可能占4字节比如生僻字英文字符占1字节。VARCHAR(255)在utf8mb4下最坏情况能占1020字节而InnoDB的一行数据有约65535字节的行大小限制。所以如果你有多个VARCHAR(255)字段行长度可能叠加爆掉报错会提示Row size too large。实际项目里VARCHAR长度的设计原则是够用且留出20%~30%的余量。比如手机号列VARCHAR(20)就足够了VARCHAR(255)纯属浪费商品名称按经验最长的可能到80字那VARCHAR(120)就是合理值。CHAR的场景反而更简单存那些长度恒定的值。比如身份证号18位、手机号11位、固定格式的编号、MD5摘要32位十六进制。CHAR定长有它的优势——行格式更紧凑某些场景下查询效率略高但别指望这点收益能带来质变。拿CHAR存变长文本是反面教材我曾经在报修系统里见过有人用CHAR(500)存备注每行不满500的都用空格补齐查询结果还得TRIM纯属自找麻烦。1.3 TEXT与BLOB能不用就不用的大字段TEXT和BLOB系列TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXTBLOB对应二进制版是MySQL里著名的隐形性能杀手。它们的共同问题是行数据会存储在单独的页面里InnoDB需要用额外指针去关联。如果你把一篇长文的正文存在TEXT里再频繁SELECT *每次都要额外IO去读这些页外数据性能自然好不了。我的建议很直接能不用TEXT/BLOB就不用长文本优先考虑拆表存对象存储数据库里只留对象地址。真要用也绝对不要SELECT *只查询需要的列。对TEXT/BLOB列做索引需要指定前缀长度例如INDEX(comment(50))搜索时只能按前缀匹配别指望全字段索引。如果你在建表时发现业务里详情内容备注这类字段特别多先停下来想个问题这些内容是否真的一定要跟着主表走很多场景其实是独立的附件表或内容表关系靠主表ID关联这样主表行大小变小查询性能会明显改善。2. 小数与日期时间最容易出讹误的高危区整型和字符型选错了顶多是浪费存储或者未来扩容麻烦。小数和日期时间选错了那是直接出错的级别——钱对不上、时间差8小时、排序乱套每个都是能引起事故的严重Bug。2.1 货币和精确计算为什么绕不开DECIMAL先说资金。MySQL里的FLOAT和DOUBLE是浮点数本质是二进制近似存储。举例0.1在二进制里是无限循环小数存进FLOAT后实际值是0.100000001490116119384765625打印出来可能再被四舍五入成0.1。单看一个值好像没问题但一旦累加、相乘误差会累积。金额模块里1分钱对不上账这种事十有八九就是FLOAT/DOUBLE惹的祸。解决精确计算用的是DECIMAL也叫NUMERIC它是按十进制存储的定点数能精确表示任何小数。格式是DECIMAL(M, D)M是总位数D是小数位。比如DECIMAL(10, 2)表示最多8位整数、2位小数能存到99999999.99。DECIMAL的M最大可以到65D最大30实际存储根据位数用变长字节存不是简简单单按定长算的但可以肯定的是DECIMAL越往大走越占空间、计算代价也越高。因此DECIMAL的M和D都应该按业务上限来设不要随手写一个DECIMAL(30, 10)。我说过金额按分存BIGINT也是方案之一也就是把所有金额换算成最小货币单位分存整数展示时再除以100。这种方法在纯计算场景下非常好用省去DECIMAL的额外计算开销但代价是代码里到处要做换算而且一旦多人协作很容易有人忘了除以100。折中方案是订单金额、账户余额这种强一致字段用DECIMAL埋点统计、点击率这种不需要绝对精确的指标用DOUBLE。业务上够精确和必须精确是两个层级选型时先分清楚。2.2 DATETIME与TIMESTAMP的抉择日期时间类型的选择是MySQL面试常问、实际项目也常错的一个点。核心就两个候选DATETIME和TIMESTAMP。两者的区别可以从存储、范围、时区三个维度看DATETIME8字节支持范围从1000-01-01 00:00:00到9999-12-31 23:59:59跟时区无关。你存进去是什么值查出来就是什么值。TIMESTAMP4字节存储范围到2038-01-19就不行了著名的2038年问题而且它受时区影响。MySQL会把客户端传入的时间先按会话时区转成UTC存储查询时再转回当前时区。也就是说如果应用和数据库的时区设置不一致你又用了TIMESTAMP查出来的时间可能凭空多了8小时。我的个人经验是新表一律默认DATETIME。理由很简单TIMESTAMP的2038年上限对很多业务系统来说就是一颗定时炸弹尤其涉及会员有效期、合同到期日这种可能要跨越2040年的场景时TIMESTAMP直接阵亡。DATETIME没有这个顾虑范围宽、语义明确、调试时看到什么就是什么。那TIMESTAMP就一无是处吗也不是。它有自动初始化和更新的能力DEFAULT CURRENT_TIMESTAMP配合ON UPDATE CURRENT_TIMESTAMP可以在行更新时自动刷时间戳做最后修改时间很顺手。DATETIME在8.0之前不支持默认值用函数8.0之后也支持DEFAULT CURRENT_TIMESTAMP了这个区别其实已经缩小了。另一个常见做法是用INT UNSIGNED存Unix时间戳。我不太推荐除非你有明确的跨库迁移或排序需求。因为直接看库里的1690000000根本不知道对应哪天排查问题还得先转心算太反人类。与其用INT不如用DATETIMESQL里还能直接范围比较、按天分组。2.3 隐式类型转换查询性能的隐形杀手我开头说的那个查不出数据的事故本质就是隐式类型转换。MySQL的规则是如果比较的两边一边是字符串一边是数字MySQL会把字符串转成数字再比。举例字段是VARCHAR存的是1001查询条件写成WHERE order_no 1001MySQL会把字段里的每一个字符串都转成数字再跟1001比。只要对字段做了函数或运算索引就失效了全表扫描在所难免。更隐蔽的是字符串转数字的规则不是完整转换而是从开头解析到第一个非数字字符为止。1001abc会被解析成10011001abc 1001居然能查出数据来。这埋的雷有多深遇到一次终生难忘。所以建表和写查询的时候我给自己定了一条铁律字段是什么类型查询参数就必须是什么类型。前缀相同、类型不同也会出问题。3. 表约束五类约束如何协作才不打架数据类型解决的是这列能存什么表约束解决的是这一行能不能存、列之间什么关系。MySQL里的约束主要有非空约束、默认值、唯一约束、主键约束、外键约束以及从MySQL 8.0.16开始才真正生效的检查约束。逐一看配合起来才完整。3.1 PRIMARY KEY一张表只能有一个真主键主键约束是全表数据的坐标轴。InnoDB是索引组织表数据物理上就是按主键顺序组织的主键选得好不好直接决定写入性能和查询路径。主键设计的常见误区业务ID当主键。比如用户表用身份证号当主键。看着好像天然唯一可一旦业务规则变化比如支持护照、支持临时证件主键就无法满足需求了。业务字段会变物理主键不能随便变。UUID当主键。UUID是随机字符串插入时主键索引频繁随机分裂InnoDB的聚簇索引性能会急剧下降。大表实测下来UUID主键的写入性能可能只有自增主键的一半左右。多个字段联合主键。能不用就不用除非是明确的多对多关联表。稍微改一下关联业务联合主键就成了改表结构的噩梦。最稳妥的方案是自增整数主键配合一个独立的UNIQUE约束去保证业务字段的唯一性。数据定位交给主键业务查重交给唯一索引各管各的。3.2 外键吃性能但保一致用不用外键约束FOREIGN KEY保证两张表之间的引用完整性。比如订单表的用户ID必须存在于用户表里否则不允许写入。外键的好处是一致性由数据库兜底不用业务代码逐行检查代价是每次INSERT/UPDATE都要额外查一次父表高并发写入场景下锁竞争会更明显。我在实际项目里见过两种极端一种是所有表都加外键结果批量导入数据的时候被外键检查拖到怀疑人生另一种是干脆完全不建外键连逻辑关联都没人维护后来出现大量孤儿数据。我的做法是分层判断。核心交易链路订单、支付、库存尽量用外键保底或者用触发器和应用层双重校验读写频繁的日志、流水、分析表不要外键用应用层逻辑保证写入来源可靠。外键的ON DELETE选项也值得注意使用CASCADE级联删除要特别小心从表的数据量一大一次级联删除可能把整个库拖垮。生产环境我对级联删除的态度是宁可先标记删除状态再跑定时清理任务。3.3 UNIQUE、NOT NULL、DEFAULT、CHECK的组合实践这几类约束看起来简单组合起来才是真正见效的地方。NOT NULL与DEFAULT是一对。建议每个字段都给出明确的默认值要么默认0、要么默认空串、要么CURRENT_TIMESTAMP尽量不要允许NULL。因为NULL在SQL里有三值逻辑NULL NULL都不是真COUNT(column)会跳过NULLWHERE col IS NULL和WHERE col 是两回事。允许NULL的列在排序、分组、统计时都会制造惊喜。如果业务语义真的允许空值我更推荐用空串或者0来表达无再配一个注释字段解释含义。UNIQUE约束的本质是建一个唯一索引。它除了防止重复还能被优化器用来做查询路径选择。两个细节一是UNIQUE约束允许NULL存在而且多个NULL不算重复因为每个NULL互不相等如果你要的唯一连NULL都不能重复那得用生成列加唯一索引二是联合唯一索引要注意字段顺序区分度高的字段放前面查询里常用等值条件的字段也放前面。CHECK约束在MySQL 8.0.16之前只是个摆设——语法能过但不生效。从8.0.16开始才真正强制校验。比如CREATE TABLE product ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, price DECIMAL(10, 2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, stock INT UNSIGNED NOT NULL DEFAULT 0, CONSTRAINT chk_price CHECK (price 0), CONSTRAINT chk_stock CHECK (stock 0) );如果你的MySQL版本还没有真正支持CHECK比如5.7可以用BEFORE INSERT/BEFORE UPDATE触发器来实现同等校验效果。用CHECK约束的场景我推荐用于范围校验例如折扣系数必须在0到1之间、月份必须在1到12之间。这类约束写在数据库里任何入口进来的数据都跑不掉比应用层校验更可靠。3.4 AUTO_INCREMENT的隐藏属性AUTO_INCREMENT与主键配合使用是绝大多数MySQL表的标准搭配。但关于它的几个隐藏行为很多人并不清楚。第一AUTO_INCREMENT不保证连续。删除中间行后自增值不会回退事务回滚后已分配的自增ID也不会还回去。如果你在业务里假设主键ID是连续的迟早出Bug。主键ID只能当作标识不能当作序号。第二自增列必须建索引通常就是主键。如果你在InnoDB表里把一个非索引列设为AUTO_INCREMENTMySQL会直接报错。第三关于批量插入时的自增行为。InnoDB在8.0之前是按批预分配自增值的一个事务拿了5个ID可能最后只用1个剩下的就跳过去了。8.0开始重构了自增锁批量插入时会预分配足够大的区间但依然不代表最终数据ID连续。所以看表里AUTO_INCREMENT1020而实际最大行ID只有1005是完全正常的不用疑神疑鬼。4. 建表之后才是真正的考验类型与约束的演进管理建表容易改表难。尤其是线上表已经积累了大量数据之后你再想去调整字段类型或约束那是实打实的生产运维活。这一章讲几个我经历过的改表场景和对应的血泪经验。4.1 ALTER TABLE改类型不是改一行配置那么简单很多人以为ALTER TABLE ... MODIFY COLUMN ...很快犯了个认知错误。在MySQL 8.0之前的默认行为里修改表结构经常需要复制整张表的数据COPY算法期间会锁表写入直接卡住。哪怕8.0支持了INSTANT算法也只在加列等特定操作上生效改类型这种操作依然要重建表。举一个真实案例某后台管理表的备注字段从VARCHAR(100)改成VARCHAR(500)表里也就三百万行数据。执行ALTER TABLE的时候业务直接报警数据库卡死慢查询堆积成山。当时用的是5.7版本整个过程持续了十几分钟期间表被锁住外部无法写入。所以生产库改字段前请一定先看数据量。十行百行的表随便改百万行以上就要认真评估。如果表特别大稳妥的做法是用在线DDL工具gh-ost或者pt-online-schema-change后台慢慢执行或者走双写迁移方案。不要在生产环境直接敲ALTER TABLE赌它秒级完成。另外一个细节把VARCHAR(100)改成VARCHAR(500)大概率是COPY算法因为行长度变长可能触发行格式变化。但把一个已经很大的VARCHAR改小MySQL反而还要求先确认当前没有超过新长度的数据否则直接报错。所以把长度改大容易改小要看数据有没有超。4.2 约束的变更先清理数据再动约束加CHECK约束、加UNIQUE约束、加NOT NULL约束这三类加约束的操作生产环境有一个通用前置流程先查数据是否满足新约束再动DDL。比如你想给email字段加UNIQUE约束但库里已经有几万条重复邮件。直接加约束会立刻报错Duis vietas artículos并且整条DDL失败。正确做法分两步-- 先找出重复项 SELECT email, COUNT(*) FROM user GROUP BY email HAVING COUNT(*) 1; -- 清理重复数据后再添加唯一约束 ALTER TABLE user ADD UNIQUE KEY uk_email (email);给字段加NOT NULL约束同理。如果该字段当前存在NULL数据直接执行ALTER TABLE ... MODIFY也会失败。我曾经帮一个合作团队排查过一次线上执行DDL报错他们以为是语法问题其实就是NULL数据没处理。这类问题多花5分钟查一遍数据能省下后面几小时的恢复时间。4.3 从一次事故看类型与约束的连带效应最后分享一个综合性案例把前面聊的类型和约束串起来。有张积分流水表建表时主键是自增BIGINT积分变更值用了INT。某天业务方上线一个新的签到活动要求每人每天最多签到一次产品经理要求并发请求下也不能重复。开发在应用层做了判断但高并发下还是漏了。最终处理方式是给用户ID和签到日期加了联合唯一索引ALTER TABLE sign_log ADD UNIQUE KEY uk_user_date (user_id, sign_date);这本身没错但执行的时候表里已经有历史脏数据了——同一用户同一天多条签到记录。加索引直接失败。最后写SQL把重复数据合并再补索引才解决问题。这个案例给我的启示是约束不是建完表就结束的设计而是伴随数据增长持续演进的治理手段。过了半年再把UNIQUE改掉或者把类型从INT改成BIGINT都是常态。所以建表时数据类型和约束宁可一开始想宽一步、想多一步也不要等数据量上去了再改那是最贵的返工。再分享一个建表时的小习惯。我现在无论建什么表都会顺手写上一句ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT业务表说明。字符集统一utf8mb4是全项目的基本功不然接入表情符号时乱码的锅甩都甩不掉。排序规则我常选utf8mb4_unicode_ci它比utf8mb4_general_ci对多语言字符的排序识别更准确虽然性能略低一点点但现代硬件上完全感知不到。表注释和列注释也一定要写。数据库表两年后没人记得清楚每个字段是干嘛的注释就是这个表最好的文档。尤其是一些靠约定俗成表达的字段值比如status的0、1、2分别是什么注释里写清楚了后人接手时能少很多吐槽。最后我的经验就一句话把数据类型和表约束当成API设计来对待。字段类型就是方法的入参类型约束就是入参校验规则。你设计API的时候有多谨慎就应该用同样的谨慎去对待每一张表的DDL。前期多花5分钟想清楚后期能省下5个小时在凌晨处理故障。