当前位置：首页 > news >正文

《MySQL表的创建与约束：定义结构化数据的存储载体》

news 2026/3/26 19:50:49

《MySQL表的创建与约束：定义结构化数据的存储载体》

MySQL 中的表（Table）是关系型数据库最核心的存储单元，它定义了数据的结构、类型、约束和关系，是结构化数据的物理载体。

本篇从最基础的建表语法开始，逐步深入到各种约束（Constraint）的定义、使用场景、常见写法和生产注意事项，帮助你系统掌握“如何正确地定义一张表”。

1. 基本建表语法（CREATE TABLE）

最简洁的建表语句：

CREATETABLEusers(idBIGINTNOTNULLAUTO_INCREMENTCOMMENT'主键ID',usernameVARCHAR(50)NOTNULLCOMMENT'用户名',emailVARCHAR(100)UNIQUECOMMENT'邮箱',ageINTDEFAULT18COMMENT'年龄',statusTINYINTDEFAULT1COMMENT'状态：1正常 0禁用',created_atDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',updated_atDATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ciCOMMENT='用户表';

关键组成部分拆解

部分	说明	2025–2026 推荐写法
表名	建议使用小写 + 下划线风格（users、order_items）	避免使用 MySQL 关键字、避免驼峰
列定义	列名 + 数据类型 + 约束 + 默认值 + COMMENT	每列都加 COMMENT（文档化）
主键	几乎所有业务表都应该有主键（通常是自增 ID）	BIGINT + AUTO_INCREMENT
存储引擎	InnoDB（事务、行锁、外键） vs MyISAM（只读快照）	99% 场景选 InnoDB
字符集 & 排序规则	utf8mb4（支持 emoji） + unicode_ci（不区分大小写）	utf8mb4_unicode_ci（主流）
表注释	COMMENT=‘xxx’	必须写，方便后期维护

2. MySQL 常用数据类型速查（业务最常用）

分类	类型	字节数	适用场景	推荐替代（8.0+）
整数	TINYINT	1	状态、性别、是否删除（0/1）	—
INT / INTEGER	4	普通 ID、数量	—
BIGINT	8	主键、自增 ID、雪花算法 ID	—
浮点数	DECIMAL(p,s)	—	金额、汇率（绝对不要用 FLOAT/DOUBLE）	DECIMAL(18,4) 或更高精度
字符串	VARCHAR(n)	n+1~n+4	用户名、标题、邮箱	—
CHAR(n)	n	固定长度（如身份证号、邮编）	很少用
TEXT / MEDIUMTEXT	可变	文章正文、富文本	—
时间	DATETIME	8	创建/更新时间（精确到秒）	—
TIMESTAMP	4	带时区、自动更新	8.0+ 推荐 DATETIME
DATE / TIME	3/3	只存日期/时间	—
其他	JSON	可变	非结构化扩展字段	8.0+ 非常常用
ENUM(‘男’,‘女’)	1~2	性别、状态（枚举值少时）	建议用 TINYINT + 注释代替

金额字段强烈建议：

amountDECIMAL(18,4)COMMENT'金额，精确到4位小数'

3. 表级约束 vs 列级约束

约束类型	语法位置	作用	是否允许 NULL	典型写法示例
NOT NULL	列级	该列不允许为空	否	username VARCHAR(50) NOT NULL
DEFAULT	列级	默认值	—	status TINYINT DEFAULT 1
AUTO_INCREMENT	列级	自增（必须是主键或唯一键）	—	id BIGINT AUTO_INCREMENT
UNIQUE	列级 / 表级	值唯一（允许 NULL，但 NULL 算不同）	允许 1 个 NULL	email VARCHAR(100) UNIQUE
PRIMARY KEY	列级 / 表级	主键（唯一 + 非空）	否	PRIMARY KEY (id)
FOREIGN KEY	表级	外键约束	—	FOREIGN KEY (user_id) REFERENCES users(id)
CHECK	表级（8.0+）	自定义校验	—	CHECK (age >= 18)

推荐生产级建表模板（2025–2026 主流写法）

CREATETABLEorders(idBIGINTNOTNULLAUTO_INCREMENTCOMMENT'订单ID',user_idBIGINTNOTNULLCOMMENT'用户ID',order_noVARCHAR(32)NOTNULLUNIQUECOMMENT'订单号',amountDECIMAL(18,4)NOTNULLDEFAULT0.0000COMMENT'订单金额',statusTINYINTNOTNULLDEFAULT0COMMENT'订单状态：0待支付 1已支付 2已发货 3已完成 -1已取消',created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',updated_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(id),UNIQUEKEYuk_order_no(order_no),INDEXidx_user_id_status(user_id,status),-- 复合索引CONSTRAINTfk_orders_userFOREIGNKEY(user_id)REFERENCESusers(id)ONDELETERESTRICTONUPDATECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ciCOMMENT='订单主表';

4. 约束行为总结表（非常重要）

约束	INSERT 空值	UPDATE 违反	DELETE 主表记录	推荐场景
NOT NULL	报错	报错	—	必填字段
UNIQUE	允许 NULL	报错	—	业务唯一键（如订单号、邮箱）
PRIMARY KEY	报错	报错	—	表的主标识
FOREIGN KEY	允许（若从表允许 NULL）	报错（若违反引用完整性）	可配置（RESTRICT / CASCADE / SET NULL）	强关联关系（8.0+ 推荐）
CHECK	报错	报错	—	年龄、状态范围（8.0+）

5. 生产环境常见约束最佳实践（2025–2026）

每张表必须有主键（绝大多数情况用 BIGINT 自增）
业务唯一约束用 UNIQUE KEY（而非靠应用层控制）
外键约束谨慎使用（大型系统建议用逻辑外键 + 应用层保证）
字段必须加 COMMENT（团队协作必备）
统一字符集：utf8mb4_unicode_ci（支持 emoji、不区分大小写）
金额字段永远用 DECIMAL（绝不用 FLOAT/DOUBLE）
时间字段用 DATETIME（TIMESTAMP 有 2038 年问题，且时区敏感）
状态字段用 TINYINT + 注释（比 ENUM 更灵活）

6. 快速自测题（巩固）

下列哪种写法可以让status字段默认值为 1 且不允许为空？
A. status TINYINT DEFAULT 1
B. status TINYINT NOT NULL DEFAULT 1
C. status TINYINT DEFAULT 1 NOT NULL
想让email字段唯一但允许为空，应该怎么写？
UNIQUE KEY uk_email (email)
想在删除用户时自动删除该用户的所有订单，应该在外键上写什么？
ON DELETE CASCADE

答案：1-B，2-正确（UNIQUE 允许一个 NULL），3-ON DELETE CASCADE

如果你想继续深入某个部分，比如：