MySQL数据类型与表约束详解：从订单表设计到建表规范

1. 为什么数据类型和表约束必须放在一起理解做MySQL开发这些年我面试见过不少候选人对索引优化、事务隔离级别聊得头头是道可一旦问到底层的数据类型和表约束就卡壳了。这也难怪这两块内容在教科书里往往被一句话带过但实际业务里字段类型选错或者约束设计不严后期几乎都是拿血泪来填。今天我就从一张真实的生产订单表出发把MySQL的数据类型和表约束一次讲透顺便聊聊每个选择背后的为什么以及我踩过的坑。如果你是刚接触MySQL的初学者这篇文章能帮你建立正确的建表习惯如果你已经写过几年SQL也希望这些选型思路能在你下次写CREATE TABLE时多一份底气。1.1 建表之前先回答三个问题每次设计表结构我都会强迫自己回答三个问题这个字段要存什么取值范围有多大能否为空这三个问题的答案直接决定了用什么类型和加什么约束。很多线上故障比如订单金额对不上、用户手机号查不到、时间字段出现0000-00-00提前想清楚这三个问题基本都能避免。举个例子手机号字段看起来是个数字但如果你用INT去存问题就大了。INT的最大值是2147483647根本放不下11位的手机号一旦插入超过范围的数字MySQL会报错或者在高版本下直接写入失败。即使你改成BIGINT也会遇到另一个问题手机号前导零。虽然中国手机号目前没有前导零但类似业务字段如果存在前导零存成整数会悄悄丢掉。更合理的方式是用VARCHAR(20)加唯一约束。所以类型选型不是拍脑袋而是从业务形态反推出来的。1.2 类型与约束一个管存什么一个管存得对不对数据类型解决的是存储格式问题整数、小数、字符串、日期各自占用多少字节支持什么运算。表约束解决的是数据合法性问题不能为空、不能重复、必须满足某个条件、删除主表时子表怎么办。两者缺一不可。类型选得再好没有约束脏数据照样能进来。约束设计得再严类型不对数据该错还是错。我习惯把约束看成“数据库的最后一道防线”。业务代码里可以写各种判断但多人协作时总有漏网之鱼。特别是从外部导入数据、跑存储过程批量写入、接第三方接口数据时约束能在第一时间拦住问题。等到数据入库之后再清洗成本要高得多。1.3 存储引擎带来的隐藏差异MySQL 8.0默认是InnoDB这也是绝大多数业务应该用的引擎。InnoDB支持事务、行级锁、外键约束而且聚簇索引依赖主键。MyISAM虽然快但不支持事务也不支持外键现在已经很少用了。这里想提醒大家外键约束只有InnoDB才真正生效如果你在建表时发现外键加不上去先看看存储引擎是不是MyISAM。另外InnoDB的主键选择会影响物理存储结构。如果没有显式主键InnoDB会生成一个隐藏的6字节rowid作为聚簇索引这会导致数据分散、写入性能不稳定。所以不管是业务主键还是逻辑主键每张表都要有主键这是我在所有项目中强制要求的。2. MySQL数据类型细节拆解数值、字符串、日期时间三大类2.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.22e18 ~ 9.22e180 ~ 1.84e19大整数、主键选整数类型的一个实用原则能用TINYINT就不要用INT能用INT就不要用BIGINT。不要觉得存储空间无所谓一张表几千万行时字段宽度直接影响内存和索引大小。我见过有人把订单状态设计成INT其实0到9就够用了TINYINT完全能承担。小数类型是重灾区。FLOAT和DOUBLE是浮点数二进制无法精确表示某些十进制小数比如0.1在计算机里其实是无限循环的近似值。做金额计算时如果字段用FLOAT一条两条数据看不出来累计多了就会差几分几毛。金额一律用DECIMAL比如DECIMAL(10,2)支持最大10位整数2位小数精确存储。DECIMAL(N,M)中N是总位数M是小数位数要提前按业务最大值规划好。虽然DECIMAL计算性能略低于FLOAT但数据准确性远比那点性能差重要。BIGINT还有一个常见用法主键自增。自增主键用INT在单表不超过20亿行时通常够但现在流量稍微大点的业务表很容易触及上线建议直接BIGINT。另外BIT类型不常用BOOL/BOOLEAN在MySQL里其实映射为TINYINT(1)只能存0和1并且0会被当成false非0值会被转换成1。2.2 字符串类型CHAR、VARCHAR、TEXT怎么选才不出错字符串类型是日常开发用得最多的。CHAR是定长字符串VARCHAR是变长字符串。CHAR(N)会占用固定N个字符的存储VARCHAR(N)则根据实际长度加上一两个字节的长度前缀。由于CHAR会去掉尾随空格所以如果你要存带缩进或精确空格的场景就要慎用了。VARCHAR的长度设置大有讲究。在utf8mb4字符集下一个汉字占4个字节VARCHAR(255)最多能存255个字符占用的字节数可以到1020字节。超过一定行大小限制时MySQL会自动把字段转成TEXT。这会导致某些隐式行为。更关键的是VARCHAR(255)在某些旧版本或特定场景下会影响索引前缀长度。现在新项目里一般建议字符串字段长度不要超过255如果确实需要存大段文本直接TEXT。TEXT类型有几个点要注意。第一TEXT字段不能有默认值至少MySQL 8.0之前是这样你写DEFAULT 会被直接忽略或报错。第二TEXT字段在InnoDB中可能存储在溢出页读取性能比VARCHAR差。第三给TEXT加索引需要用前缀索引不能直接整字段索引。所以能用VARCHAR解决的别图省事上TEXT。字符集和排序规则同样影响数据比较。MySQL中排序规则以collation出现常用的utf8mb4_0900_ai_ci是MySQL 8.0默认支持Unicode 9.0不区分大小写。utf8mb4_unicode_ci在老版本中也很流行。如果不同的表用了不同collationJOIN时可能会报collation冲突这也是个常见坑。排序规则还会影响WHERE比较和ORDER BY排序所以建表时就要统一。ENUM和SET也归在字符串类型里但我不建议在业务中轻易使用。ENUM有定义列表数据校验直观但每次加一个枚举值都要ALTER TABLE在高并发上线窗口极短的项目里很痛苦。现在的趋势是用TINYINT保存业务状态码在应用层做枚举映射灵活度更高。真正要存一组固定选项且数量很小的时候才考虑ENUM或SET。2.3 日期时间类型DATETIME与TIMESTAMP的时区之争MySQL日期时间相关类型有DATE、TIME、DATETIME、TIMESTAMP、YEAR。DATE存年月日TIME存时分秒DATETIME和TIMESTAMP都能存完整日期时间。DATETIME和TIMESTAMP的区别经常被面试官问起。DATETIME的存储范围是1000-01-01到9999-12-31与时区无关你存进去是什么就是什么。TIMESTAMP的范围从1970年开始到2038年结束存储时会根据数据库服务器的时区转换成UTC读取时再转回当前时区。也就是说如果服务器时区变了TIMESTAMP查询结果会跟着变DATETIME不会。早期TIMESTAMP有一个诱人的特性可以自动初始化和更新比如DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。MySQL 5.6之后DATETIME也支持自动初始化和更新了所以现在不用纠结。我自己的建议是如果需要表达业务发生时刻而且希望不受时区干扰就用DATETIME如果需要记录数据行最后修改时间且能接受时区影响可以用TIMESTAMP。不管哪个都建议加上毫秒精度比如DATETIME(3)方便排查问题和排序。关于时间默认值常见的报错是Invalid default value。这是因为旧版本DATETIME不能直接DEFAULT CURRENT_TIMESTAMP或者某些版本TIME不支持。如果你遇到这个问题先确认MySQL版本再确认字段定义语法。MySQL 8.0下写DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP是没问题的。2.4 其他常用类型JSON、二进制和空间类型MySQL 5.7版本开始支持JSON类型8.0还增加了JSON索引优化。JSON类型非常灵活适合存储不规则的扩展属性。但要注意不要对JSON字段里的内部属性建约束因为约束只能作用在列本身。如果后续要根据JSON字段做条件查询可以用生成列GENERATED COLUMN把内部字段提取出来加索引。BINARY和VARBINARY用于存二进制字节串比如哈希值、加密串但现实中大多数文件、图片都走对象存储数据库只存URL所以这类字段用得不多。空间数据类型比如GEOMETRY、POINT等主要用于地理坐标普通业务用不到。3. 表约束的完整图谱主键、外键、唯一、非空、默认、CHECK3.1 主键约束InnoDB聚簇索引的根主键约束是表约束里最重要的。它保证一行数据的唯一标识同时也是InnoDB聚簇索引的索引键。InnoDB表数据本身就按主键顺序存储在聚簇索引的叶子节点上所以主键选择会影响所有查询和写入性能。主键设计有几个经验。优先用单列自增整数比如BIGINT AUTO_INCREMENT。自增主键插入顺序基本有序能减少页分裂和随机IO。如果业务需要使用UUID或者雪花ID作为主键建议不要直接用字符型主键而是一方面保持自增列作为物理主键另一方面给业务ID建唯一索引。这样既保住聚簇索引的有序性又满足业务标识需求。复合主键不是不能用但慎用。复合主键会生成多列聚簇索引索引长度变长占用空间大而且子表引用外键时也要关联多列非常麻烦。大多数情况下用自增主键加唯一约束来代替复合主键更稳妥。3.2 外键约束保证参照完整性但不一定是万能药外键约束用来保证子表里的引用一定存在于主表。比如订单表的user_id引用用户表的id如果没有外键代码写错了就可能插入一个不存在的用户ID。外键的ON DELETE和ON UPDATE可以设置CASCADE、SET NULL、RESTRICT等行为。级联删除很方便比如删除用户时自动删除他的订单但也很危险。生产环境如果误删一个用户连带订单、日志全没了。我倾向于在关键业务表上不用物理外键而是通过应用层事务和唯一索引来保证一致性。这不是说外键不好而是高并发、分库分表场景下外键的耦合和高成本会放大。如果你决定用外键字段类型必须和主表引用字段一致。比如主表id是BIGINT子表user_id也得是BIGINT否则会报外键列类型不匹配。外键列还要有索引MySQL会自动创建但你最好提前设计好索引顺序。3.3 唯一约束、非空约束、默认值日常三件套唯一约束保证列或列组合的值不重复。它与主键的区别是唯一约束允许NULL而且在MySQL中多个NULL值不会被视为重复。这个特性有时很坑比如某业务要求身份证号唯一但允许没填的情况NULL就可以存在多行。如果要求必须填就加上NOT NULL再配合唯一约束数据质量才有保证。唯一约束和唯一索引在MySQL中基本是一回事CREATE UNIQUE INDEX和ADD UNIQUE CONSTRAINT效果相同。唯一约束对查询有加速作用但写入时每次都要做唯一性检查索引越多写入越慢。所以不是所有字段都值得加唯一约束只有真正有业务唯一语义的字段才加。非空约束NOT NULL是很多人忽略的。有些设计喜欢把字段默认设为NULL表示“未知”但这会给开发带来很多麻烦。NULL在WHERE比较时不能用等号只能用IS NULL在聚合函数中NULL会被忽略在应用层还会出现NullPointerException。我的原则是字段能不为空就尽量NOT NULL加上合理的DEFAULT值只有真正可选的值才允许NULL。默认值DEFAULT和NOT NULL是黄金搭档。比如status TINYINT NOT NULL DEFAULT 0insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP。MySQL 8.0还支持表达式默认值比如DEFAULT (UUID())但尽量别滥用。3.4 CHECK约束从摆设到真正生效MySQL很早就有CHECK语法但直到MySQL 8.0.16之前CHECK约束不会真正生效只是建表时不报错数据照样能插入。MySQL 8.0.16之后CHECK约束才会被强制执行这是和旧版本行为差异非常大的地方。所以升级到8.0后如果线上有存量表依赖老逻辑要小心新插入数据突然被CHECK拦住。CHECK约束适合简单的行级校验比如quantity 0price 0。它可以和枚举语义互补但不要放太复杂的子查询或跨表判断否则性能和维护都是问题。要给CHECK约束命名否则修改时报错时你都不知道是哪个约束比如CONSTRAINT chk_quantity_positive CHECK (quantity 0)。3.5 约束的修改与删除在线DDL的注意点业务迭代中经常要加约束。加的时候MySQL会做表重建或在线修改不同版本支持程度不一样。8.0支持ALGORITHMINPLACE但有些修改还是需要COPY。执行ALTER TABLE之前建议先评估数据量。数据量很大的表加约束尤其是加唯一约束时会做全表扫描可能锁表或拖垮主库。约束命名规范能省不少事。我一般用前缀区分主键用pk_外键用fk_唯一约束用uq_CHECK用chk_。比如uq_user_mobile表示用户手机号唯一约束。这样在ERROR 1062重复键、ERROR 3812外键冲突时能一眼看出是哪个字段的问题。4. 实操从零设计一张用户订单表的完整流程4.1 需求分析与字段清单理论说再多不如完整走一遍。接下来我就用订单表作为例子从需求到SQL一步步展开。假设业务要求如下订单号系统生成需要全局唯一。用户ID来自用户中心不能为空。商品名称要保存下单时的快照不能为空。商品单价是金额精确到分。购买数量必须是正整数。订单状态用数字表示0待支付1已支付2已发货3已完成4已取消默认0。创建时间自动生成支付时间允许为空。备注允许为空但长度有限。根据这些需求我整理出字段清单字段名类型约束说明idBIGINT自增主键物理主键order_noVARCHAR(32)唯一约束非空业务订单号user_idBIGINT非空普通索引用户IDproduct_nameVARCHAR(200)非空商品快照product_priceDECIMAL(10,2)非空CHECK price 0商品单价quantityINT非空CHECK quantity 0购买数量total_amountDECIMAL(12,2)非空订单总金额statusTINYINT非空默认0订单状态remarkVARCHAR(500)允许NULL备注create_timeDATETIME(3)非空默认CURRENT_TIMESTAMP(3)创建时间pay_timeDATETIME(3)允许NULL支付时间4.2 完整建表SQL与字段详解根据上面的设计MySQL 8.0下的完整建表语句如下CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 物理主键, order_no VARCHAR(32) NOT NULL COMMENT 业务订单号, user_id BIGINT NOT NULL COMMENT 用户ID, product_name VARCHAR(200) NOT NULL COMMENT 商品名称快照, product_price DECIMAL(10,2) NOT NULL COMMENT 商品单价, quantity INT NOT NULL COMMENT 购买数量, total_amount DECIMAL(12,2) NOT NULL COMMENT 订单总金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态:0待支付 1已支付 2已发货 3已完成 4已取消, remark VARCHAR(500) DEFAULT NULL COMMENT 备注, create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 创建时间, pay_time DATETIME(3) DEFAULT NULL COMMENT 支付时间, PRIMARY KEY (id), UNIQUE KEY uq_order_no (order_no), KEY idx_user_id (user_id), CONSTRAINT chk_product_price_nonnegative CHECK (product_price 0), CONSTRAINT chk_quantity_positive CHECK (quantity 0) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单表;这个表的核心思想是用自增BIGINT当物理主键保证InnoDB聚簇索引有序业务订单号order_no建唯一约束保证业务上的唯一性user_id建普通索引用于按用户查询。amount字段全部用DECIMAL避免金额误差。状态字段用TINYINT加默认值满足后续扩展。两个CHECK约束是MySQL 8.0.16之后真正生效的如果线上库低于这个版本需要评估后使用。4.3 验证约束生效插入测试数据建好表后别急着跑业务先用SQL验证约束是否符合预期。我会插入五条测试数据覆盖正常和异常场景。正常插入一个订单INSERT INTO orders (order_no, user_id, product_name, product_price, quantity, total_amount) VALUES (2024001, 1001, 手机, 1999.00, 2, 3998.00);这条能成功没有问题。接下来验证非空约束故意不传order_noINSERT INTO orders (user_id, product_name, product_price, quantity, total_amount) VALUES (1002, 耳机, 99.00, 1, 99.00);执行后会报错Field order_no doesnt have a default value。这说明非空约束和默认值设计正常工作。再验证CHECK约束插入数量为0的订单INSERT INTO orders (order_no, user_id, product_name, product_price, quantity, total_amount) VALUES (2024002, 1003, 数据线, 39.00, 0, 0.00);MySQL 8.0.16以上会直接报错Check constraint chk_quantity_positive is violated。旧版本则能插入成功这也是我在升级版本时特别小心的地方。最后验证唯一约束重复插入order_no为2024001的订单会报ERROR 1062 Duplicate entry 2024001 for key orders.uq_order_no。看到这个报错我就知道唯一约束生效了。4.4 类型选择对后续操作的影响排序、事务、应用层映射建表只是开始。字段类型会影响后续几乎所有SQL操作。比如排序订单号如果存成VARCHAR按order_no排序是字典序会出现20240010排在2024002前面的情况。如果不需要这种字典序最好用BIGINT保存纯数字订单号或者用自增主键排序。金额字段排序用DECIMAL也很稳定不会有浮点数精度干扰。事务场景下InnoDB的行锁依赖索引如果where条件里的字段没有索引很可能退化成表锁。所以在做金额更新时尽量走主键或唯一索引。上述orders表虽然没写外键但可以通过事务保证数据一致性比如在支付事务里同时更新订单状态和用户余额这才是OLTP的正确姿势。应用层的类型映射也要对齐。Java JDBC读DECIMAL会得到BigDecimalPython pymysql读DECIMAL会得到Decimalpandas读出来可能是object类型。如果你的代码把Decimal直接强转成float再计算精度就丢了。C语言用MySQL Connector/C时MYSQL_FIELD的type字段会对应该列的枚举类型也需要做转换。这一层不处理好数据库再精确到了应用层还是会变形。5. 常见问题与排查技巧建表阶段最容易踩的坑5.1 隐式类型转换导致索引失效这个坑太常见了mobile字段是VARCHAR查询时却写WHERE mobile 13812345678。MySQL会尝试把字符串字段转换成数字再比较结果就是无法有效地使用索引甚至查出来的结果不对。正确写法是WHERE mobile 13812345678。反过来如果字段是整数类型条件里的字符串不要紧MySQL也能转成数字。但为了统一规范最好让应用层和SQL条件里的类型都明确一致避免依赖隐式转换。5.2 字符串比较和排序“莫名其妙”用VARCHAR存数字编码时排序是字典序而不是自然序。如果你发现1、10、2这种排列先查一下字段是不是字符串。解决办法是让字段类型和业务语义一致或者使用ORDER BY CAST(field AS UNSIGNED)。字符集和排序规则影响更大。utf8mb4_0900_ai_ci对英文大小写不敏感所以查询abc会匹配ABC。如果业务需要区分大小写可以使用utf8mb4_bin排序规则或者在字段上加BINARY属性。还有一个容易被忽视的点CHAR比较会忽略尾随空格VARCHAR不会。所以存用户输入的密码时别用CHAR而用VARCHAR否则密码后面的空格可能被悄悄吃掉。5.3 时间默认值总是设置失败常见报错是Invalid default value for create_time。原因通常是MySQL版本较老DATETIME字段不允许直接DEFAULT CURRENT_TIMESTAMP另一个原因是DDL里写着DEFAULT NOW()但版本不支持。升级到MySQL 8后这类问题少多了但仍有不少存量项目在5.6、5.7上。建议统一使用DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)既支持毫秒又有默认值。还有一个时区坑如果MySQL连接串没有指定时区客户端和服务器的会话时区可能不一致导致查询出来的TIMESTAMP和预期差8个小时。DATETIME没有这个问题这也是我偏好DATETIME的原因。5.4 DECIMAL精度丢失FLOAT却看不出来在测试环境用FLOAT存金额十几条数据怎么算都对但一到线上几百万条累计就出现一分钱的误差。这是浮点数的天然缺陷。任何涉及金额的字段都别用FLOAT和DOUBLE请用DECIMAL。同理在存储过程中做金额计算也要保证变量类型是DECIMAL不要中途转成DOUBLE否则精度在计算过程中就丢了。5.5 NULL带来的三个坑第一个坑唯一约束允许多个NULL导致你明明觉得该唯一的数据不唯一。解决办法是字段加NOT NULL。第二个坑WHERE总金额 NULL永远查不出来必须用IS NULL。第三个坑COUNT(字段)会忽略NULL如果统计时没意识到数据量会有偏差。这些跟表约束看起来关系不大却都是建表时选了允许NULL带来的后续痛苦。5.6 连接和乱码问题别都归咎于表结构有时数据读出来乱码不是表结构问题而是连接字符集没配好。SET NAMES utf8mb4或者连接串里指定characterEncodingutf8能解决大部分问题。另一个和连接相关的报错是MySQL 8默认使用caching_sha2_password认证老客户端连不上时会报Authentication plugin异常。升级客户端驱动即可和表约束无关但因为建表后要配置连接很多人混淆。5.7 表结构迁移到TDengine时的类型映射思路如果你有MySQL表结构自动转TDengine超级表和子表的需求类型映射大概可以这样参考MySQL的INT对应TDengine的INTBIGINT对应BIGINTDECIMAL在TDengine里没有对应精确小数类型通常用DOUBLE或者拆分成整数部分和小数部分存储VARCHAR在TDengine里可以用BINARY或NCHAR注意TDengine中BINARY是字节长度NCHAR是字符长度中文场景直接用NCHAR更省心。MySQL里的非空、唯一、外键这些约束TDengine不会像关系数据库那样强制执行约束逻辑需要上移到写入侧或者应用侧。这种迁移不是简单替换类型而是要从关系模型转向时序模型的思维转换。6. 建表规范的从业者总结把问题拦截在建表之前建表是数据工程里最靠前的一步也是返工成本最高的一步。字段类型和表约束的设计直接决定了后续查询、统计、迁移、运维的复杂度。我个人的习惯是每张表建完都要过一遍“四问”这个字段存什么最大多大能不能为空要不要唯一四问都能明确回答建表SQL就不会跑偏。这里也想分享一个被验证的规范主键用BIGINT自增业务号单独加唯一约束金额用DECIMAL整数用INT/TINYINT字符串长度固定上限时间统一DATETIME(3)加非空默认能加NOT NULL的字段都加能加默认值的都加CHECK约束在MySQL 8.0.16以上可以放心用版本更低就靠应用层校验。从工程角度说约束不是限制人而是保护人。它牺牲了一点点写入灵活度换来的是长期的数据可信。每次看到几百行的ALTER TABLE回滚脚本我都会想如果当初建表时多想十分钟后面能省太多事。建表这件事永远是越想省事后面越麻烦。