一个做小电商的朋友,前半年订单都记在 Excel 里。第一个 sheet 是客户,第二个 sheet 是订单,订单里直接写客户名字。等到第 300 单,问题集中爆发:有个客户把昵称从「老王」改成了「王哥」,订单 sheet 里有 40 多行没跟着改,对账时对不上;有人按颜色筛选时误删了两行,客户打电话来问订单去哪了;两个人同时编辑,后保存的覆盖了前一个。
这些不是 Excel 的错。Excel 是电子表格,它没打算管「同一个客户只能有一条记录」「订单必须能找到一个真实存在的客户」这类规则,也不管两个人同时改同一行会发生什么。关系数据库就是把这些规则变成系统能执行的东西。今天这篇不背概念,先把表、行、列、主键、外键这五个词落到那个 Excel 场景里,再写第一条能跑起来的 SQL。
场景走查:一张表管一类东西
把 Excel 的两个 sheet 拆成数据库里的两张表,思路就很清楚了:customers 只放客户,orders 只放订单。订单表里不再抄客户名字,只留一个 customer_id,指向客户表里的那一行。客户改名只改客户表的一处,订单表不用动。
这就是「关系」两个字的来源:数据不是孤立的,两张表通过一个共同的字段建立对应关系。客户和订单是「一对多」——一个客户可以有多张订单,一张订单只属于一个客户。订单和商品通常是「多对多」,因为一张订单里有多个商品,一个商品也会出现在多张订单里,这时候需要第三张中间表,比如 order_items,记录「哪张订单买了哪个商品、买了几件、单价多少」。
表、行、列,各管一件事
表(table)是一类对象的集合,行(row)是其中一个具体对象,列(column)是对象的某个属性。说人话:一张客户表,每一行是一个客户,每一列是客户的一个信息,比如手机号、姓名、城市。
列不只是一个名字,它带类型和长度。类型决定了能存什么、怎么算、占多少空间。手机号用 VARCHAR(20) 而不是整数,因为它可能带区号、可能有前导零,而且我们不会对它做加减乘除。金额用 DECIMAL(10,2) 而不是 FLOAT,因为浮点数存 0.1 会变成 0.100000001,几十万笔累加之后差出一顿饭钱。时间用 DATETIME 或 TIMESTAMP,二者语义不同,后面建表那篇会细说。
长度也别拍脑袋。VARCHAR(255) 是很多人的默认值,但 MySQL 在 utf8mb4 下每字符最多 4 字节,行格式和索引前缀长度都会受影响;手机号 VARCHAR(20) 就够,昵称按业务上限给 VARCHAR(50)。定长了浪费空间,定短了上线后改表要重建,两头都要付代价。
新手最容易犯的错是「一张大表装下所有东西」。订单表里塞客户姓名、客户手机号、商品名、商品分类,看起来查询方便,实际上是把自己推进了 Excel 那个坑:同一个客户的信息在几十行里重复,改一处漏一处。判断标准很简单:如果某个信息会跟着另一个对象变,它就不该待在这张表里。
主键:一行到底靠什么身份
Excel 里删掉一行再插一行,你没法确认「还是不是原来那条记录」。数据库用主键(primary key)解决这件事:主键是这一行的唯一身份证,不能重复,也不能为空。有了它,UPDATE ... WHERE id = 7 才能准确改到你想改的那一行,而不是「大概第三行」。
主键有两种常见选择。一种是业务主键,比如身份证号、手机号,本身有业务含义;另一种是代理主键,比如自增整数 id,只负责标识,不承载业务。绝大多数业务表用代理主键更稳:手机号会换、会复用,长度也长;拿它当主键,所有引用它的地方都要跟着改,索引也更大。
还有复合主键:两个列一起构成唯一身份。比如订单明细表用 (order_id, product_id),表示「同一张订单里同一个商品只出现一次」。复合主键在关联表里很常见,但业务表一般还是用单列自增,写起来和排查都简单。
自增主键还有两个常被问到的细节。第一,删掉的行不会让后面的 id 回填,所以 id 有空洞是正常的,不要拿它当「用户数」或「订单量」统计,那个数字只代表「曾经插入过多少行」。第二,自增 id 在不同库、不同实例上可能重复,分库分表后要换成全局唯一 ID,不能直接拿单库自增当全局主键。
还有一个副作用:批量插入时如果开了 innodb_autoinc_lock_mode=2,多个事务拿到的 id 可能不连续,甚至交错。这不是 bug,而是为了并发插入效率做的取舍。业务上不要假设「id 小的订单一定先创建」,要排序就按 created_at。
id,但团队里统一叫 id 能省很多沟通成本。真正重要的是:它是整型、自增、不为空、有索引。MySQL 的 InnoDB 会把主键作为数据的物理组织方式,这个细节在索引原理那篇展开。外键:把两张表接起来
订单表里的 customer_id 存的是客户表的主键值。这个「指向」关系可以交给数据库来管,叫外键(foreign key)。建表时声明 FOREIGN KEY (customer_id) REFERENCES customers(id),之后插入一条 customer_id = 999999 的订单,数据库会直接拒绝;删除一个还有订单的客户,也会被拦住。
但外键不是必选项。很多互联网团队不在数据库里建外键,而是在应用层校验,原因有三:分库分表之后跨库没法约束;高频写入时外键检查会增加锁和开销;历史数据迁移时外键顺序很麻烦。代价是脏数据要靠自己兜底,比如定时跑脚本找「订单里的 customer_id 在客户表里不存在」的孤儿数据。
这是典型的取舍,不是对错。小项目、数据一致性要求高、单库单表,外键省心;大流量、分片、频繁批量导入,应用层校验加对账更现实。第 5 篇会把两种做法和实测报错都摆出来。
除了外键,还有一种「隐式关系」:订单表里存 product_name,商品表里也有商品名,两边靠名字对上。这种关系数据库管不了,改一个商品名就可能对不上,查询也没法用索引关联。能存 id 就存 id,别用名字做关联键。
订单里冗余存客户名
查询列表不用关联,单表就能出结果,快。但客户改名要批量更新几十万行,还容易漏;同一个客户的历史订单显示不同名字。
订单只存 customer_id
客户信息只有一份,改名只改一处。列表要显示客户名时得 JOIN 客户表,多一次关联;但数据一致,长期维护成本低。
第一条能跑的 SQL
下面这段可以直接在 MySQL 8.0 里执行。注意 ENGINE=InnoDB 要显式写:有的环境默认引擎是 MyISAM,它不支持事务,后面会吃大亏。
CREATE DATABASE IF NOT EXISTS shop
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;
CREATE TABLE customers (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
phone VARCHAR(20) NOT NULL,
name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL DEFAULT '',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_phone (phone)
) ENGINE=InnoDB;
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
status VARCHAR(16) NOT NULL DEFAULT 'created',
amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_customer_created (customer_id, created_at),
CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;
INSERT INTO customers (phone, name, city) VALUES
('13800000001', '张三', '北京'),
('13800000002', '李四', '上海');
INSERT INTO orders (customer_id, status, amount) VALUES
(1, 'paid', 128.00),
(2, 'created', 59.90),
(1, 'done', 310.50);
SELECT id, customer_id, amount, created_at FROM orders WHERE customer_id = 1;
预期结果:第一条 INSERT 返回「2 rows affected」,订单表返回「3 rows affected」,最后一条 SELECT 查出张三的两张订单,金额分别是 128.00 和 310.50。如果 customer_id 写了 3,会直接报 ERROR 1452,因为客户表里没有 id=3。
再试三条:UPDATE orders SET status = 'shipped' WHERE id = 2; 只改第二张订单;DELETE FROM orders WHERE id = 2; 删掉它;SELECT COUNT(*) FROM orders; 数一下还剩几条。SQL 的读和写都是围绕「表 + 条件」展开的,条件写不准,改错行就是分分钟的事。
UPDATE 或 DELETE 前,先把同样的 WHERE 放进 SELECT 看一眼会命中哪些行。忘了写 WHERE 的 UPDATE 会改全表,DELETE 会删全表,而且很多数据库默认没有「撤销」。翻车现场:同一笔订单扣了两次款
支付回调重复推送不是罕见事:网络抖动、网关重试、用户连点,都可能让同一个回调进来两次。某次大促后财务对账发现 17 笔重复扣款,每笔都有两条订单记录,order_no 完全相同。开发第一反应是「代码里判断过 order_no 是否已存在」,但和注册那个故事一样,先查再插不是原子的。
根本原因在建表:order_no 只是一个普通 VARCHAR 列,没有唯一约束。实测两次插入同一个单号,两条都成功写入:
CREATE TABLE t_dup (
id INT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) NOT NULL,
amount DECIMAL(10,2)
);
INSERT INTO t_dup (order_no, amount) VALUES
('SO20261106001', 99.00),
('SO20261106001', 99.00);
SELECT COUNT(*) AS rows_total, COUNT(DISTINCT order_no) AS uniq
FROM t_dup;
-- rows_total = 2, uniq = 1
事后想补唯一索引,又撞上已有重复数据:
ALTER TABLE t_dup ADD UNIQUE KEY uk_order_no (order_no);
-- ERROR 1062 (23000): Duplicate entry 'SO20261106001' for key 'uk_order_no'
事故之后连「加约束」这一步都要先清理数据,线上修复窗口被拉长。教训不是「代码要写得原子」,而是:能在数据库里表达的规则,就不要只写在应用层。应用层的「先查再插」在并发下一定有窗口,唯一约束是数据库层面的原子判定。
主键和唯一约束也不是一回事:主键是自增 id,保证「一行一个身份」,永远不会重复;业务单号是否重复,得靠 UNIQUE 单独管。给 order_no 加上唯一索引后,第二个回调会直接报 1062,应用层把 1062 翻译成「已处理」返回成功即可,既不重复扣款,也不用加分布式锁。
关系数据库和 NoSQL,不是站队
「NoSQL」是个很宽的筐,里面至少有三类东西:键值存储(Redis 这类,按 key 取 value)、文档数据库(MongoDB 这类,一条记录是一整块 JSON)、宽列存储(HBase、Cassandra 这类,按行键和海量列组织)。它们共同点是:不强求固定表结构,通常牺牲一部分关联查询和事务能力,换取写入吞吐或水平扩展。
关系数据库的强项是结构稳定、约束明确、事务可靠、多表关联方便。订单、库存、账务这类「不能算错」的场景,它仍然是默认选择。NoSQL 的强项是结构多变、单条记录自包含、写入量大、能分片。用户行为日志、商品详情快照、购物车、排行榜这类场景,用它更顺手。
关系数据库(MySQL / PostgreSQL)
适合:交易、订单、账务、权限,需要多表关联和事务。代价是表结构变更要迁移,单机容量有上限,分片复杂。
文档 / 键值数据库
适合:结构不固定、按 key 读写、海量写入、可接受最终一致。代价是关联查询弱,事务能力有限,数据容易重复。
实际项目里最常见的不是二选一,而是各干各的:交易数据放 MySQL,会话和热点缓存放 Redis,搜索放 Elasticsearch,日志放 ClickHouse。选型的依据是访问模式和一致性要求,不是「听说哪个快」。
现象对照:这些问题都指向基础概念
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 同一个客户在表里出现两条 | 手机号列没有唯一约束,两次注册都写进去了 | SHOW CREATE TABLE customers 看有没有 UNIQUE;再按手机号分组数重复 |
| 订单列表显示「客户不存在」 | 订单里的 customer_id 指向一条已被删掉的客户 | 用 LEFT JOIN 找 c.id IS NULL 的孤儿订单 |
| 对账金额差了几分钱 | 金额列用了 FLOAT/DOUBLE,累加产生精度误差 | SHOW CREATE TABLE 看列类型,改成 DECIMAL |
| 客户昵称里的 emoji 存不进去 | 库或表用的是三字节的 utf8,不是 utf8mb4 | 查 information_schema 里的字符集,连接字符集也要查 |
| 订单时间比实际早 8 小时 | 用了 TIMESTAMP,会话时区不是东八区 | SELECT @@time_zone, @@system_time_zone |
| 两个人同时改同一订单,一个改动消失 | 应用层「先读后写」,没有事务或条件更新 | 看更新语句有没有带版本号或原值条件 |
| 同一业务单号出现两条订单 | 业务列没有唯一约束,主键只保证 id 不重复 | SHOW CREATE TABLE 看有没有对应的 UNIQUE KEY |
| 换手机号后登录进了别人的账号 | 把手机号当主键或关联键,旧号被回收后又分配给新用户 | 查主键定义和各表关联字段,确认没有用业务字段做关联 |
边界与代价
关系数据库不是免费的。约束要在每次写入时检查,索引要在每次写入时维护,JOIN 要读多张表,事务要占锁和日志空间。数据量上来之后,单机写入和存储都会碰到天花板,这时候才需要读写分离、分库分表、归档,而不是一开始就上。
MySQL 和 PostgreSQL 在细节上也有差异,别把一套经验无脑套过去:MySQL 标识符大小写看系统配置,PostgreSQL 默认把未加引号的标识符转小写;MySQL 自增用 AUTO_INCREMENT,PostgreSQL 用 GENERATED ... AS IDENTITY 或 serial;MySQL 的 utf8 是三字节历史遗留,必须写 utf8mb4,PostgreSQL 的 UTF8 就是完整的。本文默认讲 MySQL 8.0 / InnoDB,遇到 PostgreSQL 差异会单独点出来。
还有一个常被忽略的边界:NULL 不等于空字符串,也不等于 0。它表示「没有值」,参与比较时结果既不是真也不是假,而是未知。这个坑会贯穿查询、聚合、唯一约束好几篇,第 2 篇会专门拆。
「一张表管一类东西」也有反面:拆得太碎,一个列表要 JOIN 五六张表,查询和事务都变重。判断标准不是范式第几层,而是「哪些数据总是一起读写」。订单和订单明细通常一起出现,拆表也要在同一个事务里写;头像、签名这类只在详情页用的字段,拆出去能减少主表的宽度和扫描量。
选型还有一个现实边界:关系数据库的单机写入上限通常在几千到上万 TPS 量级,具体取决于硬件和事务复杂度。到这个量级之前,问题多半出在索引、SQL 或连接池,而不是「MySQL 不行了」。真要分片,先想清楚分片键、跨片查询和事务怎么办——这三件事会比写入瓶颈更早折磨你。
小结
表是一类对象,行是一个实例,列是带类型的属性;主键给每一行一个稳定身份;外键(或用应用层校验)把两张表的关系固定下来。关系数据库的核心价值不是「能存数据」,而是「能替你守住规则」,并且允许多人并发操作而不互相覆盖。
如果只记三句话:表用来装一类对象,主键用来定位一行,唯一约束用来防重复。外键是可选的关系保障,NoSQL 是特定场景的工具,不是「更新的数据库」。
当天可以自己验证的练习:
- 把上面的建表语句跑一遍,故意插入一条
customer_id = 3的订单,记下完整报错号。 - 去掉
uk_phone唯一索引,插两条相同手机号的客户,再用GROUP BY phone HAVING COUNT(*) > 1把重复找出来。 - 把
amount改成FLOAT,插入 0.1 一百次再求和,对比DECIMAL的结果。 - 删掉一个还有订单的客户,观察外键报错;再删订单、删客户,确认顺序。
- 用
SELECT * FROM orders和SELECT id, amount FROM orders对比,想一下为什么生产代码不该随便写SELECT *。
下一篇讲查询基础:筛选、排序与分页。表建好了,接下来就是怎么把想要的那几行准确、稳定、快地取出来——先从一条普通的订单列表查询开始,看看 WHERE、ORDER BY 和 LIMIT 里的坑是怎么把 20 毫秒拖成 3 秒的。