1. 建表前必做的一件事先想清楚这张表要回答什么问题1.1 从业务对象到表结构的翻译过程很多刚接触 SQL 的人都会犯同一个错误拿到需求就打开编辑器噼里啪啦把 CREATE TABLE 写出来字段名随手拍脑袋类型一看差不多就填上去。结果表建好了数据也插进去了等到三个月后要统计报表时才发现用户表中没有手机号字段、订单金额用的是 FLOAT 导致对不上账、同一个用户重复录了三遍。我带的实习生第一次建表时我让他先别写代码用一张纸把下面几个问题回答完这张表描述的是哪个业务对象用户、订单、商品、日志……这个对象有哪些属性需要被记录哪些属性是必填的哪些属性要求不能重复未来你会用哪些条件去查找这张表的数据回答完这五个问题再打开 SQL 编辑器。你会发现建表这件事百分之八十的工作量在动笔之前就已经完成了。CREATE TABLE 只是把你脑子里的设计翻译成数据库能懂的语法而已。1.2 先想查询再建表是效率最高的设计路径大多数人建表时只关心数据怎么存进去却很少想数据以后怎么查出来。这恰恰是本末倒置。数据库存在的意义是读取存储只是手段。你要在哪个字段上过滤、在哪个字段上排序、哪几个字段组合起来唯一确定一条记录——这些问题必须在建表阶段就给出答案。举个例子你现在要做一张用户表。假设未来你最频繁的查询是按手机号登录查用户信息那么 phone 字段就必须建唯一索引而查某天注册的用户数会用到 created_at。可如果你建表时压根没把这个字段设计进去后面想加就得 ALTER TABLE 改表结构表里几百万行数据时这个操作的代价足够让你后悔很久。我的经验是先列出未来可能出现的 5 条 SELECT 语句再去设计表结构。这比直接想字段清单更符合直觉因为查询条件就是你最需要关心的字段。2. CREATE TABLE 语法骨架拆解每一段是什么、能省略什么2.1 语法全貌不过三个部分CREATE TABLE 的基本语法用一句话就能概括CREATE TABLE 表名 (列定义列表) [表选项];。看上去很简单但括号里面的内容才是重头戏。每个列定义由三部分构成列名、数据类型、约束。CREATE TABLE 表名 ( 列名1 数据类型 [约束], 列名2 数据类型 [约束], ..., [表级约束] ) [表选项];这里要注意一个很多人忽略的点约束既可以写在列定义的后面列级约束也可以在所有列定义完之后单独写表级约束。两者的区别在于列级约束只管当前这一列而表级约束可以在同一行里约束多列的组合。比如你要确保同一个用户不能对同一个商品重复评价那就需要联合唯一约束这种需求必须用表级约束来实现CREATE TABLE review ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键, user_id INT NOT NULL COMMENT 用户ID, product_id INT NOT NULL COMMENT 商品ID, content VARCHAR(500) COMMENT 评价内容, UNIQUE KEY uk_user_product (user_id, product_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;2.2 表名和列名的命名规则不注意会被狠狠上一课命名这种东西看着不起眼但踩坑的概率极高。先说硬性规则表名和列名不能以数字开头不能包含空格不能使用数据库的保留字比如 order、group、select 这些词在很多数据库里都是保留字直接用作表名会报语法错误。然后是软性规范。我建议一律使用小写字母加下划线的方式比如order_item而不是OrderItem、user_phone而不是UserPhone。原因有两个第一MySQL 在 Linux 上表名是区分大小写的在 Windows 上不区分这种不一致会让你在迁移环境时遇到诡异的问题第二团队协作时只要看一眼命名风格就知道是一个体系里的代码。如果你的表名实在撞上了保留字比如非要用order来表示订单表那就必须加反引号MySQL或方括号SQL Server把表名包起来。但我个人不建议这么做——宁可改成orders加个复数也别给自己埋这个雷。2.3 表选项不同数据库的差异从这里开始括号部分写完之后真正的分水岭就来了。MySQL 里最常用的两个表选项是ENGINE和DEFAULT CHARSETCREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;ENGINEInnoDB是 MySQL 默认的存储引擎支持事务、外键、行级锁绝大多数场景都选它。DEFAULT CHARSETutf8mb4指定字符集这个我在后面专门讲因为它直接决定了你的表能不能存下中文、emoji 表情。而在 PostgreSQL 里这两个选项对应的写法完全不同通常不需要指定引擎字符集继承数据库级别的配置。SQL Server 则用ON [文件组]来控制物理存储位置一般默认即可。JSON 里没有这些概念它是直接在CREATE TABLE后通过AS SELECT或LIKE建表的。提示刚学 SQL 的人先掌握 MySQL 的一套写法把语法结构记牢其他数据库迁移时只要对照差异表改几下就行。这篇文章后面会给一份常见数据库差异对照表。3. 数据类型的取舍决定这张表生死的隐藏因素3.1 数字类型金额永远不要用浮点数数字类型看起来最简单int、bigint、double、float选一个不就得了实际上这里就有第一个大坑——浮点数不能用于金额。FLOAT和DOUBLE是近似存储计算时会有精度丢失。你以为存了 0.1读出来可能变成 0.100000001490116。对账单、购物车金额、工资这种需要精确计算的值必须用DECIMAL定点数。DECIMAL(10,2)表示总长度 10 位、小数点后保留 2 位能精确表示 99999999.99 以内的金额。常用数字类型对比类型占用空间表示范围/特性适用场景TINYINT1字节-128 ~ 127 / 0 ~ 255状态码、开关量INT4字节约 ±21亿用户ID、数量BIGINT8字节约 ±900亿亿订单号、雪花IDDECIMAL(M,D)变长精确保存金额、单价FLOAT/DOUBLE4/8字节近似值温度、比例等非精确值这里有个小规则能用 TINYINT 的事别用 INT能用 INT 的别用 BIGINT。因为每一条记录都在磁盘上占空间一个字段省 3 个字节一亿行数据就省 300MB加上索引还不止。现在硬件便宜了但查询时索引的 IO 量依旧敏感。3.2 字符串类型VARCHAR 长度要按业务上限来而不是按最大能填多少来CHAR和VARCHAR的区别一句话概括CHAR 是固定长度不够用空格补齐VARCHAR 是变长存多少用多少但要额外用 1~2 字节记录长度。所以 CHAR 适合长度固定且短的字段比如性别、国家代码VARCHAR 适合名字、地址这种长度不确定的字段。但很多人给 VARCHAR 定长度时毫无章法上来就是VARCHAR(255)。问题在于VARCHAR 的长度上限会影响索引的效率。以 InnoDB 为例一个索引前缀最大通常是 768 字节历史版本或整行大小有限制如果字段定义过长就无法建立完整索引或者需要走前缀索引。更重要的是VARCHAR(10) 和 VARCHAR(255) 在存hello时占用磁盘相同都是 5 字节长度标记但排序时使用临时表的空间开销会按定义长度来分配字段定义得越长排序越慢。我的习惯是姓名 VARCHAR(50)手机号 VARCHAR(20)地址 VARCHAR(200)文章正文 TEXT。每个字段的长度都从业务需求出发而不是统一 255 图省事。TEXT、BLOB 这类大数据字段要特别注意它们无法直接加默认值不能作为主键部分数据库能但强烈不建议而且由于行大小限制一张表里多个 TEXT 字段会让表变得非常臃肿。如果能拆出去单独存文件就别往表里塞。3.3 日期时间DATETIME 和 TIMESTAMP 的时区陷阱日期类型看着简单两个选择DATE只存年月日DATETIME和TIMESTAMP存年月日时分秒。它们的核心区别在于TIMESTAMP占 4 字节范围从 1970 年到 2038 年且会跟随数据库时区自动转换DATETIME占 8 字节范围大得多但存进去是什么就是什么不带时区信息。如果你做的是国际化业务用户分布在多个时区用 TIMESTAMP 能省心一些如果只是国内业务用 DATETIME 也不会出大问题。但有个细节值得注意很多公司规定订单时间统一存零时区时间展示层再转本地时间。这种约定下 DATETIME 反而更安全因为不会因为数据库时区配置变了一下历史数据全部偏移。3.4 其他值得了解的类型BOOLEANMySQL 其实没有真正的布尔型TINYINT(1) 配合 0/1 来模拟。PostgreSQL 和 SQL Server 有原生 BIT/BOOLEAN。JSONMySQL 5.7 和 PostgreSQL 支持 JSON 类型适合存储结构不固定的扩展属性。但要注意JSON 字段无法走普通索引查询要靠虚拟列或函数索引别把它当成万能筐什么都往里装。ENUMMySQL 独有的枚举类型看上去很美但后续加枚举值需要 ALTER TABLE而且排序规则容易踩坑。能用 TINYINT 加注释替代就替代。UUID用 CHAR(36) 存但性能远不如 BIGINT 自增主键后面讲主键时会展开。4. 约束设计让数据库替你守住数据底线4.1 五大约束各司其职约束是 CREATE TABLE 里最值得琢磨的部分它比你想象中的限制高级得多——它是在声明一种业务规则。如果规则在数据库层面就守住那么在任何一个应用入口里都钻不了空子。NOT NULL该字段必须有值。适合姓名、手机号、状态码这类业务必需字段。UNIQUE该字段值不能重复。适合手机号、身份证号、订单号。PRIMARY KEY主键唯一且非空的组合一张表只能有一个。FOREIGN KEY外键限制当前表字段的取值必须存在于另一张表的主键中。CHECK自定义范围检查比如年龄必须大于 0。4.2 主键设计自增整数、业务字段、UUID 三选一主键的选择我建议用一句话定方向能用自增整数就不要用业务字段用了 UUID就要接受它在索引上的代价。先说为什么不建议用业务字段做主键。最常见的反面案例是用手机号或身份证号当主键。表面上看这些字段确实唯一但只要业务发展到一个人有两个手机号注销后再注册这类场景主键就会变得非常难改。而且业务字段长度通常比整数大很多InnoDB 的二级索引叶子节点存的是主键值主键越长二级索引的体积就越大查询性能随之下降。自增整数主键是最稳妥的选择配合AUTO_INCREMENTMySQL、IDENTITY(1,1)SQL Server、SERIALPostgreSQL使用。比如CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL );那 UUID 什么时候用分布式场景、多个数据库实例需要独立生成 ID 不冲突时才值得考虑。如果真选了 UUID记得用CHAR(32)去掉横杠存储或者 MySQL 8.0 的内置UUID() 二进制转换来做避免直接用 VARCHAR(36)。4.3 外键加还是不加别被两张极端观点带偏很多人一聊外键就进入南北极模式老派 DBA 说必须加保证数据完整性互联网派说能不用就不用影响写入性能。我的看法是看场景。事务性系统比如订单、支付、库存这些核心业务外键应该加。理由很实在它能防止你写错数据。例如订单明细表里的order_id加了外键指向订单表主键后任何指向不存在订单的明细记录都插不进去这个保障靠应用层代码要多写多少判断才能等价实现高并发写入、日志型系统、或者正在频繁做分库分表的场景外键是累赘。因为外键会让每次 INSERT、UPDATE 都去校验关联表且 InnoDB 对关联操作会加锁并发量上来后容易成为瓶颈。如果你的团队里有人对数据库不熟悉我更倾向于保留外键。因为外键还有一个隐藏价值它本身是一种活的文档。任何人打开订单明细表的建表语句一眼就能看出它依赖哪张表不用再去翻业务文档。4.4 正确命名约束排查问题时没人能难住你约束可以显式起名字也可以用默认规则生成。默认名字的格式通常是表名_ibfk_1、表名_chk_1这种看了等于没看。想要在删除外键、禁用约束时快速定位最好都显式命名CREATE TABLE order_item ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, CONSTRAINT fk_order_item_order FOREIGN KEY (order_id) REFERENCES orders(id), CONSTRAINT fk_order_item_product FOREIGN KEY (product_id) REFERENCES products(id) ) ENGINEInnoDB;命名格式我用得很统一约束类型缩写加表名加字段名fk_开头是外键uk_开头是唯一约束ck_开头是检查约束。这样后来的人删约束、排故障时不用靠猜。5. 从零建一张订单表的完整过程设计取舍全公开5.1 拿到需求后的第一版设计为了把前面的知识串起来我带大家完整走一遍订单表的设计过程。假设需求很简单电商系统的订单需要记录下单用户、收货人信息、订单状态、商品总价、下单时间同时每笔订单有多个商品条目。第一步先画字段清单。订单表的核心字段如下订单ID主键自增整数用户ID关联用户表必填加普通索引订单编号对外展示的单号业务上唯一加唯一索引收货人姓名VARCHAR(50)必填收货人电话VARCHAR(20)必填订单状态TINYINT必填默认 00待支付、1已支付、2已发货、3已完成、4已取消商品总金额DECIMAL(10,2)必填下单时间DATETIME必填默认当前时间订单明细表字段明细ID主键自增整数订单ID外键关联订单表必填商品ID关联商品表必填商品快照名称VARCHAR(100)必填商品单价快照DECIMAL(10,2)必填购买数量INT必填小计金额DECIMAL(10,2)必填5.2 完整的建表 SQLCREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 订单表主键, user_id INT NOT NULL COMMENT 下单用户ID, order_no VARCHAR(32) NOT NULL COMMENT 订单编号, receiver_name VARCHAR(50) NOT NULL COMMENT 收货人姓名, receiver_phone VARCHAR(20) NOT NULL COMMENT 收货人电话, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态: 0待支付 1已支付 2已发货 3已完成 4已取消, total_amount DECIMAL(10,2) NOT NULL COMMENT 商品总金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单主表; CREATE TABLE order_item ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 明细主键, order_id INT NOT NULL COMMENT 所属订单ID, product_id INT NOT NULL COMMENT 商品ID, product_name VARCHAR(100) NOT NULL COMMENT 商品名称快照, product_price DECIMAL(10,2) NOT NULL COMMENT 商品单价快照, quantity INT NOT NULL COMMENT 购买数量, subtotal DECIMAL(10,2) NOT NULL COMMENT 小计金额, CONSTRAINT fk_order_item_order FOREIGN KEY (order_id) REFERENCES orders(id), KEY idx_product_id (product_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;5.3 这段设计里有哪些值得抄作业的细节很多人看不出上面这段代码里的门道我来逐条解释为什么订单编号要单独建字段还用唯一索引而不是直接拿主键当订单号因为订单号要给用户看还要在客服、物流等外部系统间传递。自增主键一旦暴露别人能通过订单ID差值推测出你的订单量这是安全隐患。所以主键和业务单号必须分离。为什么商品名称和商品单价要存快照而不是通过商品ID实时关联查询因为商品可能改名、改价。你下单时的华为手机 128G 黑色和三个月后商品表里那条记录可能完全不是一个名字。订单作为交易凭证必须冻结下单那一刻的商品信息这就是快照字段的意义。为什么状态字段用 TINYINT 不用 VARCHAR数字占用空间小可扩展而且配合注释写代码时status 1一眼就能看出是已支付。如果你用 VARCHAR 存PAIDWAIT_PAY每次判断都要比较字符串浪费空间也没带来任何好处。为什么除了唯一索引还给 user_id 和 created_at 建了普通索引因为订单表最常见的查询是我的订单列表按 user_id 查和某时间段的订单统计按 created_at 查。这就是前面说的先想查询再建索引。5.4 建完表后立刻做一次完整性验证表建完别急着走用下面几条 SQL 验收一下SHOW CREATE TABLE orders; -- MySQL 查看实际建表语句 DESC orders; -- 查看表结构 -- 验证唯一约束插入重复订单号应该报错 INSERT INTO orders (user_id, order_no, receiver_name, receiver_phone, status, total_amount) VALUES (1, NO20240001, 张三, 13800138000, 0, 99.00); INSERT INTO orders (user_id, order_no, receiver_name, receiver_phone, status, total_amount) VALUES (2, NO20240001, 李四, 13900139000, 0, 88.00); -- 这里应报 Duplicate entry -- 验证外键约束插入一个不存在的订单ID明细应该报错 INSERT INTO order_item (order_id, product_id, product_name, product_price, quantity, subtotal) VALUES (9999, 1, 测试商品, 10.00, 1, 10.00); -- 这里应报外键失败一套验证下来你就能确认主键自增、唯一约束、外键约束、默认值全部符合预期。很多人建完表直接就不管了等生产环境跑挂了才发现约束没生效那种代价远比你多花这两分钟大得多。6. 建表后必然要面对的三种改动ALTER、DROP 和字符集问题6.1 改表 ALTER TABLE不是所有改动都那么轻松表结构建完之后需求变更是常态。ALTER TABLE 常用的操作也就那么几个但每个都暗藏风险-- 添加字段 ALTER TABLE orders ADD COLUMN payment_time DATETIME NULL COMMENT 支付时间; -- 修改字段类型 ALTER TABLE orders MODIFY COLUMN receiver_name VARCHAR(100) NOT NULL; -- 重命名字段MySQL 8.0 ALTER TABLE orders RENAME COLUMN receiver_name TO contact_name; -- 删除字段 ALTER TABLE orders DROP COLUMN payment_time; -- 添加索引 ALTER TABLE orders ADD INDEX idx_status (status);这里最需要警惕的是MODIFY COLUMN和DROP COLUMN。在 MySQL 5.6 之前ALTER TABLE修改字段会导致整表重建表里几百万行时可能锁表几个小时业务直接停摆。现在 InnoDB 支持了在线 DDLOnline DDL但依然会消耗大量 IO 和空间。所以我的经验是字段类型一开始就尽量定准别指望反正以后能改来兜底真需要改时选在业务低峰期操作先在一张副本表上演练一遍。6.2 DROP TABLE别手滑先备份再动手删表是最危险的操作没有之一。DROP TABLE orders; -- 表和数据一起没了 TRUNCATE TABLE orders; -- 清空数据保留表结构且自增ID归零 DELETE FROM orders; -- 逐行删除可用 WHERE 条件三者区别很大DROP连表结构带数据一起销毁TRUNCATE保留表结构但清空所有数据而且不能加 WHEREDELETE可以带条件精确删除。生产环境我有一条铁律任何 DROP 或 TRUNCATE 操作执行前必须先把表做一次备份CREATE TABLE orders_bak LIKE orders; INSERT INTO orders_bak SELECT * FROM orders;备份做完再动手。别迷信自己手速人在紧张情况下点错命令的概率比你想象中高得多。6.3 字符集乱码utf8 和 utf8mb4 的恩怨纠葛中文乱码是老生常谈但很多人到现在都不明白为什么自己建表时明明写了utf8存 emoji 表情还是报错。答案一句话MySQL 的utf8是残缺版它最多存 3 字节的字符而 emoji 是 4 字节必须用utf8mb4才能存下。这也是为什么我前面所有示例的DEFAULT CHARSET都写utf8mb4而不是utf8。在 SQL Server 里对应的是排序规则Collation比如Chinese_PRC_CI_ASPostgreSQL 则通过数据库级别的UTF8编码解决。如果你正在用 ORM 自动建表也要检查一下框架默认生成的建表语句很多老项目的默认字符集还是utf8等线上出现保存 emoji 失败的反馈时再迁移涉及的远不止一张表。6.4 一张对照表看清主流数据库的 CREATE TABLE 差异如果要在多种数据库之间切换下面这张表能省你不少查资料的时间功能MySQLPostgreSQLSQL ServerOracle自增主键AUTO_INCREMENTSERIAL / IDENTITYIDENTITY(1,1)序列触发器12c可用IDENTITY字符串VARCHAR(n)VARCHAR(n)VARCHAR(n) / NVARCHAR(n)VARCHAR2(n)注释COMMENT xxxCOMMENT ON COLUMN用扩展属性COMMENT ON COLUMN表选项位置括号后 ENGINE/CHARSET括号后一般无需括号后 ON 文件组括号后 TABLESPACE反引号支持反引号支持双引号支持方括号支持双引号检查约束8.0.16 前不生效支持支持支持其中最容易踩的坑是同样的建表语句从 MySQL 迁到 Oracle连分号都不用改就能跑通的概率几乎为零。所以如果你的项目在未来可能要换数据库要么从一开始就用 ORM 管理表结构要么专门留一个人负责维护跨数据库的 DDL 脚本。6.5 一个小技巧建表前加上 IF NOT EXISTS最后分享一个我写建表脚本时的习惯在 CREATE TABLE 后面加IF NOT EXISTS。CREATE TABLE IF NOT EXISTS orders ( ... );这个前缀在首次部署时没区别但在重复执行脚本、自动化发布、或者 CI 流程里就非常好用——它保证了脚本可以安全地多次执行而不会报错。与之配套的还有 MySQL 的CREATE DATABASE IF NOT EXISTS整个初始化脚本从头到尾都加这个前缀你会少接到无数个上线失败表已存在的半夜电话。过了这些坑之后再回头看 CREATE TABLE 会发现它其实不复杂想清楚要表达的业务对象、选对类型、定好约束后面所有查询、统计、扩容才能站得稳。我自己带人时最常说的一句话是会写 SELECT 只能证明你学会了 SQL 的皮能把 CREATE TABLE 设计得干净利落才算真正开始懂数据。