把业务装进数据库：建库、建表与准备数据

上一章里，我们把关系型数据库理解成一组互相关联的表，也看到了主键、外键和 SQL 大致在做什么。这一章要往前走一步：把“小满商店”真正装进 MySQL。课程后面的查询、连接、分组、子查询、事务和性能分析，都会继续使用这里创建的数据。

先别急着敲 CREATE TABLE。建表最容易犯的错，不是少写一个逗号，而是把还没想清楚的业务直接固化成列。比如“订单里存商品名称和当前价格”看起来省事，可商品改名、调价以后，旧订单究竟该跟着变，还是保留成交当时的信息？如果这个问题没想明白，SQL 就算一次执行成功，表也可能从第一天起就埋下矛盾。

这一章会完成九张表：customers、categories、products、orders、order_items、payments、departments、employees 和 inventory_movements。它们分别保存客户、分类、商品、订单、订单明细、支付、部门、员工和库存流水。我们会从数据库和账号的安全边界开始，接着选择数据类型、定义约束、创建表、填充一组可复用的数据，再练习怎样安全地修改和删除。

先把业务边界画清楚

九张表分别回答什么问题

数据库设计可以从问题开始。每张表都应该有一个清楚的职责，看到表名就能大致知道它在回答什么：

这里有两个很容易忽略的拆分。

第一个是 orders 和 order_items。一张订单可以购买多种商品，所以订单概要只存一次，商品明细一行一种。若把多个商品编号塞进 orders 的一个字符串列，例如 '1,3,8'，数据库就无法可靠地检查编号是否存在，也很难统计某件商品被卖了多少。

第二个是 products.stock 和 inventory_movements。stock 是“现在有多少”的快照，读取快；库存流水是“为什么变成这样”的过程，能追溯。只保留快照，库存出错时找不到原因；每次都从全部流水重新求和，又会让常用页面承担不必要的计算。两者同时存在时，写入流程必须保证它们一致，事务章节会继续处理这个问题。

同一个名字，不等于同一份事实

products.unit_price 是商品当前标价，order_items.unit_price 是下单那一刻记录的成交基准价。它们都叫单价，但时间含义不同。假设“白瓷马克杯”今天从 39.90 元涨到 42.00 元，历史订单不应该跟着涨价，所以明细必须保留 39.90 元。

orders.total_amount 也不是多余列。它保存订单确认后的最终应付金额，便于支付、对账和审计。我们当然可以用明细里的数量、单价和折扣算出商品小计，但订单还可能加入满减、运费或优惠券，仅靠现有三列不一定能还原最终金额。保存汇总值的代价是：创建或修改订单时，要按完整计价规则核对，而不是粗暴要求它总等于明细小计。

关系先决定建表顺序

外键引用的目标表必须先存在。按这套模型，比较稳妥的创建顺序是：

1. 先创建没有外部依赖的 customers、categories 和 departments。其中前两张表有自引用外键，但约束可以随表一起创建。
2. 再创建依赖分类的 products，以及依赖部门的 employees。
3. 创建依赖客户的 orders。
4. 最后创建 order_items、payments 和 inventory_movements 这些明细或流水表。

插入数据也遵守同一条原则：父记录先于子记录。删除时通常反过来，从最末端的子记录开始。后面看到外键错误时，你会发现数据库只是在坚持这个顺序。

建库之前先划权限边界

管理员、迁移账号和应用账号不是一回事

很多入门教程会让你用 root 完成所有操作，然后应用也拿着同一套账号运行。这在个人练习里看似方便，却把“安装数据库”“改变表结构”和“读写业务数据”三个完全不同的权限绑在了一起。应用一旦出现注入漏洞，攻击者得到的就不只是某几行数据，甚至可能删除表、创建账号或读取其他数据库。

我们把边界拆成三层：

MySQL 账号由“用户名 + 来源主机”共同确定。'xiaoman_app'@'localhost' 和 'xiaoman_app'@'%' 不是同一个账号。前者只允许从数据库所在机器连接，后者可能接受任意来源地址，暴露面更大。课程环境使用 localhost，是为了把网络边界也写进身份里。

用管理员创建数据库

先用管理员登录。命令里的 -p 会让客户端随后询问密码，密码不会直接出现在命令行参数中。

mysql -u root -p

创建数据库时，明确写出字符集和排序规则：

CREATE DATABASE IF NOT EXISTS xiaoman_shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

utf8mb4 可以保存完整的 Unicode 字符，包括中文和四字节字符。不要再使用含义容易误解的 utf8 写法；在 MySQL 中，它长期对应只能保存最多三字节字符的旧字符集别名。

排序规则决定字符串怎样比较。utf8mb4_0900_ai_ci 中的 ai 表示比较时不区分重音差异，ci 表示不区分字母大小写。因此，在采用这个排序规则的唯一索引里，大小写不同的两个英文邮箱通常会被当作相同值。若某一列需要逐字节区分，可以单独指定二进制排序规则，但不要在没弄清搜索规则前全库切换。

确认数据库定义：

SHOW CREATE DATABASE xiaoman_shop;

结果的核心部分如下：

Database      Create Database
xiaoman_shop  CREATE DATABASE `xiaoman_shop` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */

数据库级设置只是后续表的默认值。客户端连接也必须正确协商字符集，否则“表是 UTF-8”并不能自动修复连接阶段已经被错误解释的字节。可以在登录后检查：

SHOW VARIABLES LIKE 'character_set_connection';
SHOW VARIABLES LIKE 'collation_connection';

若使用官方命令行客户端，可以显式指定：

mysql --default-character-set=utf8mb4 -u xiaoman_migrator -p xiaoman_shop

创建账号，再单独授权

MySQL 8.4 中，创建账号和授予权限是两个动作。下面的密码仅用于隔离的课程实例；在团队或线上环境中，应由密钥管理系统生成和注入密码，不要把真实密码提交到版本库。

CREATE USER IF NOT EXISTS 'xiaoman_migrator'@'localhost'
  IDENTIFIED BY 'CourseOnly_Migrate_2026!';
 
CREATE USER IF NOT EXISTS 'xiaoman_app'@'localhost'
  IDENTIFIED BY 'CourseOnly_App_2026!';

迁移账号只拿到本数据库内建结构与准备数据所需的权限：

GRANT SELECT, INSERT, UPDATE, DELETE,
      CREATE, ALTER, INDEX, REFERENCES
ON xiaoman_shop.*
TO 'xiaoman_migrator'@'localhost';

应用账号不应改变表结构：

GRANT SELECT, INSERT, UPDATE, DELETE
ON xiaoman_shop.*
TO 'xiaoman_app'@'localhost';

检查授权，而不是凭记忆相信它：

SHOW GRANTS FOR 'xiaoman_migrator'@'localhost';
SHOW GRANTS FOR 'xiaoman_app'@'localhost';

你会看到权限作用域被限制在 xiaoman_shop：

GRANT USAGE ON *.* TO `xiaoman_app`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `xiaoman_shop`.* TO `xiaoman_app`@`localhost`

USAGE 并不是“拥有所有使用权”，它表示账号存在但没有额外的全局权限。真正的业务权限在第二行。

权限不足是一种预期中的保护

用应用账号连接后尝试建表：

CREATE TABLE should_not_exist (id INT PRIMARY KEY);

会得到类似错误：

ERROR 1142 (42000): CREATE command denied to user 'xiaoman_app'@'localhost' for table 'should_not_exist'

这不是环境坏了，而是安全边界正在工作。读错误时先抓三件事：错误号 1142、SQLSTATE 42000、动作 CREATE command denied。再核对当前身份和授权：

SELECT CURRENT_USER();
SHOW GRANTS;

如果迁移账号临时需要更高权限，做法是精确授权、完成任务、再撤销。例如确实要执行一次表删除，可以由管理员短时授予并收回：

GRANT DROP ON xiaoman_shop.*
TO 'xiaoman_migrator'@'localhost';
 
REVOKE DROP ON xiaoman_shop.*
FROM 'xiaoman_migrator'@'localhost';

不要把临时权限遗忘成永久权限。

数据类型是在替业务写规则

表中的每一列都要回答两个问题：它是什么，以及它允许出现什么。VARCHAR(100) 不只是在分配空间，也是在宣布“这是一段有长度边界的文本”；DECIMAL(10,2) 则宣布“它是需要精确到分的十进制定点数”。类型选错，后面的查询再漂亮也只能处理一开始就失真的数据。

主键用整数，但别把位数写得过于乐观

本章的业务主表普遍使用 BIGINT UNSIGNED AUTO_INCREMENT，分类和部门这类增长较慢的字典表使用 INT UNSIGNED AUTO_INCREMENT。UNSIGNED 把负数范围让给非负范围；AUTO_INCREMENT 让服务器安全地产生新编号，避免两个并发请求都用“当前最大值 + 1”而发生冲突。

不要因为当前只有十行数据就一律使用 TINYINT。主键一旦被外键引用，扩大类型会牵动多张表。反过来，也没必要把数量、状态都做成 BIGINT。类型要覆盖合理生命周期，而不是越大越专业。

MySQL 中整数类型后的显示宽度不是容量。旧写法里的 INT(11) 不代表只能存 11 位数。我们直接写 INT 或 BIGINT，把真正的业务范围交给 CHECK。

PostgreSQL 没有 UNSIGNED，迁移时通常使用有符号整数再配合 CHECK (id > 0)；自动编号可使用标准的 identity 列。概念相同，语法不要机械替换。

金额必须用 DECIMAL

FLOAT 和 DOUBLE 保存的是近似值。它们适合测量值、科学计算或允许微小误差的场景，不适合直接保存订单金额。小满商店的价格、薪资、支付金额统一使用 DECIMAL：

unit_price   DECIMAL(10,2) NOT NULL,
total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
salary       DECIMAL(10,2) NOT NULL

DECIMAL(10,2) 表示总共最多 10 位十进制数字，其中 2 位在小数点后。它能精确表示 68.00，而浮点数的内部二进制表达可能只能靠近这个十进制值。

折扣率使用 DECIMAL(5,4)：0.0000 表示不打折，0.1000 表示减去原价的 10%，也就是九折。明细折后金额统一按 quantity * unit_price * (1 - discount_rate) 计算。我们没有把折扣存成“9”或“90”，因为那会要求每个查询都猜测单位。单位应在设计阶段固定。

CHAR、VARCHAR 和 TEXT 怎么选

CHAR(n) 适合长度固定的代码，例如固定两位的国家代码。客户姓名、城市、商品名和岗位名称长短不一，适合 VARCHAR(n)。流水备注可能更自由，但本课程仍给出 VARCHAR(200) 的边界，方便校验和页面展示。

VARCHAR(100) 的 100 在 MySQL 8.4 中表示字符数，不是字节数。不过一行能占用的总字节仍有上限；使用 utf8mb4 时，一个字符最多可能需要四个字节。因此，不能把许多列都随手写成 VARCHAR(10000)。

TEXT 适合真正的大段文本，例如文章正文或工单详情。它不是“更高级的字符串”，默认值、索引和内存使用方式也可能与普通短字符串不同。小满商店当前没有需要大文本的字段。

DATE、DATETIME 和 TIMESTAMP 各有边界

员工入职日期 hire_date 只关心哪一天，因此使用 DATE。订单时间、注册时间和库存变动时间需要日期与时分秒，使用 DATETIME。

TIMESTAMP 常用于需要按连接时区转换的时间点，范围也与 DATETIME 不同。课程数据希望无论在哪台机器查看都保持字面值稳定，所以统一使用 DATETIME。真实跨时区系统还应明确：存 UTC、保存时区标识，还是保存用户当地时间。只写一个类型名解决不了业务时区问题。

默认当前时间可以直接写：

registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

这表示插入时省略该列，服务器就填入当前时间。它不会让历史值随时间变化。

状态字段既要可读，也要受约束

订单状态用 VARCHAR(12) 保存中文值，再用 CHECK 限定集合：

status VARCHAR(12) NOT NULL DEFAULT '待支付',
CONSTRAINT chk_orders_status
  CHECK (status IN ('待支付', '已支付', '已发货', '已完成', '已取消', '已退款'))

为什么不用 MySQL 专有的 ENUM？ENUM 能限制候选值，但它把候选集合嵌进类型，修改与跨数据库迁移都更麻烦。VARCHAR + CHECK 的意图更直接，也更接近 PostgreSQL 等数据库的常见写法。MySQL 8.4 会执行 CHECK 约束；把旧版本“只解析、不检查”的经验搬过来，会得到错误判断。

一张选择表比“凭感觉”可靠

NULL、默认值和空字符串不是一回事

你可能会觉得，手机号没填时存 NULL、存空字符串 ''，或者存“暂无”都差不多。真正开始筛选、统计和约束以后，它们会表现得完全不同。

NULL 表示当前没有已知值

NULL 不是一个字符串，也不是数字 0。它表示该列的值未知或不适用。小满商店允许 customers.phone 和 customers.email 为 NULL，因为客户可能只提供一种联系方式。employees.hire_date 不允许为 NULL，因为一名已入职员工应当有确定的入职日期。

这种判断应该落在表定义里：

phone VARCHAR(20) NULL,
hire_date DATE NOT NULL

NULL 还会影响比较。email = NULL 的结果不是 TRUE，所以不能用它寻找缺失邮箱。正确写法是：

SELECT customer_id, customer_name
FROM customers
WHERE email IS NULL;

这个查询会在下一章正式展开。现在只记住：判断值是否缺失，要用 IS NULL 或 IS NOT NULL。

默认值只在省略时接手

DEFAULT 是“调用方没有提供这一列时怎么办”。例如：

stock INT NOT NULL DEFAULT 0,
status VARCHAR(12) NOT NULL DEFAULT '在售'

下面两种插入都会使用默认值：

INSERT INTO products
  (product_id, category_id, product_name, unit_price)
VALUES
  (101, 2, '测试咖啡', 18.00);
 
INSERT INTO products
  (product_id, category_id, product_name, unit_price, stock, status)
VALUES
  (102, 2, '测试挂耳', 22.00, DEFAULT, DEFAULT);

但显式写 NULL 不等于“请使用默认值”：

INSERT INTO products
  (product_id, category_id, product_name, unit_price, stock)
VALUES
  (103, 2, '错误示例', 20.00, NULL);

因为 stock 是 NOT NULL，在严格模式下这条语句会失败：

ERROR 1048 (23000): Column 'stock' cannot be null

空字符串是一个已知的字符串

'' 表示长度为 0 的字符串，它是已知值。NULL 表示不知道。若客户明确说“没有备注”，可以把 note 记录为空字符串；若仓库人员还没填写备注，记录 NULL 更能表达状态。团队也可以规定可选文本一律使用 NULL，关键是规则一致。

下面三个值的语义不要混用：

占位词会污染数据。若把缺失城市写成 '未知城市'，以后统计城市分布时它会像真实城市一样参与分组。除非它就是业务认可的分类，否则应保留 NULL。

CHECK 遇到 NULL 的一个细节

检查约束只在表达式明确为 FALSE 时拒绝数据。表达式结果为 UNKNOWN 时通常可以通过，所以单独写：

CHECK (unit_price > 0)

并不能代替 NOT NULL。如果 unit_price 是 NULL，比较结果是未知，不会因这条检查本身被拒绝。我们要同时表达“必须提供”和“不能为负”：

unit_price DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_products_unit_price CHECK (unit_price > 0)

用约束把表连接成业务模型

主键是行的稳定身份

主键必须唯一且不能为 NULL。我们不用客户姓名做主键，因为姓名会重复、会修改，也可能有不同写法。customer_id 没有展示含义，却稳定得多。

每张表只能有一个主键，但主键可以由多列组成。本章为所有业务表都使用单列代理主键。邮箱、分类名称和部门名称已经确认需要唯一，所以建立唯一约束；“同一商品能否在一张订单里出现两条”则可能受套餐、不同优惠或拆分履约影响，当前不擅自加唯一约束。查询订单商品时，应按 order_item_id 理解每条明细，并在需要时按商品汇总。

外键负责引用完整性

orders.customer_id 指向 customers.customer_id。这句话写成 SQL 是：

CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers (customer_id)
  ON UPDATE RESTRICT
  ON DELETE RESTRICT

有了它，不存在的客户不能凭空拥有订单；已经被订单引用的客户也不能直接删除。RESTRICT 让危险动作立即失败，逼着业务先决定归档、匿名化还是保留。

外键两端的类型要兼容。若父表是 BIGINT UNSIGNED，子表却写成有符号 BIGINT，MySQL 会拒绝创建外键。字符列做外键时，字符集、排序规则和索引条件也要匹配。

RESTRICT、CASCADE 和 SET NULL 代表业务选择

本章不是所有关系都使用同一种删除动作：

• 客户被订单引用时使用 RESTRICT，因为订单是交易记录，不能跟着客户资料一起消失。
• 分类的 parent_id 使用 SET NULL。删除上级分类后，下级分类仍可存在，只是暂时成为顶级分类。
• 订单明细的 order_id 使用 CASCADE。如果一张尚可合法删除的订单真的被删除，明细不应变成孤儿。
• 员工的 manager_id 使用 SET NULL。主管离开后，下属仍是员工，只是需要重新指定主管。
• 库存流水引用商品和员工时使用 RESTRICT，因为流水要能说明哪件商品由谁处理。

CASCADE 不是“省得自己删”的快捷键。它可能从一条父记录扩散到大量子记录，只有当子记录离开父记录就完全失去意义时才适合使用。

自引用外键表达同类对象之间的关系

客户推荐客户，分类包含子分类，员工管理员工。这三种关系都发生在同一张表内部：

referrer_id BIGINT UNSIGNED NULL,
CONSTRAINT fk_customers_referrer
  FOREIGN KEY (referrer_id)
  REFERENCES customers (customer_id)
  ON UPDATE RESTRICT
  ON DELETE SET NULL

自引用不代表可以随便形成环。数据库会保证编号存在，但“甲推荐乙、乙又推荐甲”是否合理，通常还需要应用逻辑或更复杂的检查。约束能守住结构底线，不会自动理解全部业务。

给约束命名是在给错误留线索

我们统一使用这些前缀：pk_ 表示主键，fk_ 表示外键，uq_ 表示唯一约束，chk_ 表示检查约束。看到 chk_inventory_movements_quantity，就能直接定位到库存流水的数量规则，而不用猜 inventory_movements_chk_1 到底检查了什么。

创建小满商店的九张表

下面是一套完整的 MySQL 8.4 建表脚本。先用迁移账号连接 xiaoman_shop，再从头到尾执行。脚本假设这些表尚不存在；若你的数据库里已有同名表，先确认数据是否可以丢弃，不要习惯性删除。

选中数据库并检查严格模式

USE xiaoman_shop;
 
SELECT DATABASE() AS current_database,
       @@SESSION.sql_mode AS session_sql_mode;

结果中的数据库应为 xiaoman_shop，SQL 模式通常包含 STRICT_TRANS_TABLES：

current_database  session_sql_mode
xiaoman_shop      ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

严格模式能把越界数字、无效日期和某些隐式转换变成错误，而不是悄悄修剪后写入。课程示例按严格模式设计。不要为了让错误 SQL “跑过去”而关闭它。

客户、分类与部门

CREATE TABLE customers (
  customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_name VARCHAR(40) NOT NULL,
  phone VARCHAR(20) NULL,
  email VARCHAR(100) NULL,
  city VARCHAR(30) NOT NULL,
  registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

这三张表不依赖其他表。customers.email 允许 NULL，但一旦提供就不能重复。MySQL 的唯一索引允许多行都为 NULL，因为“未知值”彼此不被判定为相等。city 是本课程要求的客户基础资料，因此使用 NOT NULL。

商品与员工

CREATE TABLE products (
  product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id INT UNSIGNED NOT NULL,
  product_name VARCHAR(80) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  status VARCHAR(12) NOT NULL

products 通过 category_id 依赖分类，employees 通过 department_id 依赖部门。员工的 manager_id 又指向同表主键，所以填充员工时先插入主管，再插入下属。

订单、明细与支付

CREATE TABLE orders (
  order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status VARCHAR(12) NOT NULL DEFAULT '待支付',
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  CONSTRAINT

payments 没有对 order_id 加唯一约束，因为一次订单可能先失败、再重新支付。paid_at 保存这次支付尝试被处理的时间，所以即便状态是失败也必须记录；尚未发起支付的订单不创建支付行。订单进度与支付结果各有自己的状态列，不能混成一个概念。

库存流水

CREATE TABLE inventory_movements (
  movement_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NULL,
  movement_type VARCHAR(12) NOT NULL,
  quantity INT NOT NULL,
  moved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  note VARCHAR(200) NULL

库存流水的正负号被写进约束：采购入库和顾客退货为正，销售出库为负，盘点调整只要求非零。这样看到 quantity = -2 就知道库存减少 2，不需要再根据类型把正数二次解释成负数。经手员工允许为 NULL，员工记录删除时流水仍然保留，只把经手人引用置空。

为后续查询准备基础索引

这些索引与后续章节的常见问题一一对应，例如“某位客户最近的订单”和“某件商品的库存流水”：

CREATE INDEX idx_orders_customer_date
  ON orders (customer_id, order_date);
 
CREATE INDEX idx_orders_status_date
  ON orders (status, order_date);
 
CREATE INDEX idx_order_items_product_order
  ON order_items (product_id, order_id);
 
CREATE INDEX idx_payments_order_status
  ON payments (order_id, status);
 
CREATE INDEX idx_movements_product_time
  ON inventory_movements (product_id, moved_at);
 
CREATE INDEX idx_employees_department_salary
  ON employees (department_id, salary);

先把索引名称、列与顺序固定下来，性能章节再通过执行计划判断它们何时被使用。现在不要为每一列都建索引。

确认九张表都在

SHOW TABLES;

结果应包含九行：

Tables_in_xiaoman_shop
categories
customers
departments
employees
inventory_movements
order_items
orders
payments
products

再检查一张表的结构：

SHOW CREATE TABLE products;

SHOW CREATE TABLE 比 DESCRIBE 更适合核对完整定义，因为它会显示表引擎、字符集、唯一约束、外键和检查约束。DESCRIBE products; 更适合快速看列：

Field         Type            Null  Key  Default            Extra
product_id    bigint unsigned NO    PRI  NULL               auto_increment
category_id   int unsigned    NO    MUL  NULL
product_name  varchar(80)     NO         NULL
unit_price    decimal(10,2)   NO         NULL
stock         int             NO         0
status        varchar(12)     NO         在售
created_at    datetime        NO         CURRENT_TIMESTAMP  DEFAULT_GENERATED

ALTER TABLE：结构会变，但不能随手变

真实业务不可能一次设计完。ALTER TABLE 用来添加列、修改列、增加约束和调整索引。它改变的是所有后续数据都要遵守的结构，因此风险比修改一行数据更高。

用一次可回退的练习理解 ALTER

假设产品团队提出“记录商品条码”，可以添加一列：

ALTER TABLE products
  ADD COLUMN barcode VARCHAR(32) NULL AFTER product_name;

查看结果：

SHOW COLUMNS FROM products LIKE 'barcode';

Field    Type         Null  Key  Default  Extra
barcode  varchar(32)  YES        NULL

这个需求还没确认，我们把练习列删掉，让后续章节继续使用锁定的统一字段：

ALTER TABLE products
  DROP COLUMN barcode;

这两条语句都能运行，但不要把它们理解成事务里的普通修改。MySQL 的许多 DDL 会隐式提交，不能指望外层 ROLLBACK 撤销已经完成的结构变化。安全的结构变更至少要先回答：表有多大、是否锁表、旧应用能否兼容、失败怎样恢复、备份是否可用。

给已有数据添加约束要先清理

如果表里已经存在负库存，再执行：

ALTER TABLE products
  ADD CONSTRAINT chk_products_stock_new CHECK (stock >= 0);

数据库会拒绝建立约束，因为旧数据已经违反规则。正确顺序是先查出问题行，确认修复策略，再添加约束：

SELECT product_id, product_name, stock
FROM products
WHERE stock < 0;

这里已经提前用到了 SELECT。你可以把它理解成所有变更前的“照明灯”：先看清会影响谁，再动手。

MySQL 与 PostgreSQL 的改表差异

两者都支持 ALTER TABLE，但具体子句、锁行为、默认值转换和自动编号语法有差异。MySQL 的 AUTO_INCREMENT 迁移到 PostgreSQL 时，通常改写为 identity；MySQL 的 DATETIME 常对应 PostgreSQL 的 timestamp without time zone；UNSIGNED 要改成检查约束。迁移时应保留业务意图，再按目标数据库重写，而不是逐词翻译。

填入一套后续课程都能使用的数据

下面的种子数据刻意保留了多种情况：有客户缺手机号或邮箱，有商品缺货和下架，有订单待支付、取消、退款与完成，有员工上下级关系，也有正负库存流水。后续学习 NULL、连接、分组、子查询和窗口函数时，都不需要临时捏造另一套表。

先插父表

INSERT INTO customers
  (customer_id, customer_name, phone, email, city, registered_at, referrer_id)
VALUES
  (1,  '苏小满', '13800001001', 'xiaoman@example.com',  '杭州', '2024-01-05 09:10:00', NULL),
  (2,  '顾言',   '13800001002', 'guyan@example.com',    '上海', '2024-02-12 14:30:00', 1),
  (3,  '白露',   NULL,          'bailu@example.com'

这三个多行 INSERT 都显式写了列名。即使表以后在末尾增加可空列，脚本也不会因为“值到底对应哪一列”而失去可读性。种子数据显式指定主键，是为了让后续章节的示例编号保持稳定；日常业务写入通常省略自动增长主键。

再插商品和员工

INSERT INTO products
  (product_id, category_id, product_name, unit_price, stock, status, created_at)
VALUES
  (1,  2, '白瓷马克杯',       39.90, 120, '在售', '2024-03-01 09:00:00'),
  (2,  2, '原木托盘',         89.00,  45, '在售', '2024-04-12 09:00:00'),
  (3,  

注意员工插入顺序：编号 1 的总经理林知夏没有主管，先插入；编号 2、3、4 引用林知夏；编号 5 到 8 再引用各部门经理。以唐果为例，她的 manager_id = 3，指向客服经理沈青，所以沈青必须先存在。如果先插入唐果而沈青还没写入，外键会拒绝这行。

插入订单与订单明细

INSERT INTO orders
  (order_id, customer_id, order_date, status, total_amount)
VALUES
  (101, 1,  '2025-06-18 10:05:00', '已完成', 118.90),
  (102, 2,  '2025-07-02 21:10:00', '已完成', 188.00),
  (103, 3,  '2025-08-15 13:20:00', '已取消',  87.

明细折后金额按 quantity * unit_price * (1 - discount_rate) 计算。orders.total_amount 保存订单最终金额，它还可能包含当前模型没有单独建列的订单级优惠或运费，因此不要强行要求它永远等于明细计算之和。保留四位折扣率能让中间计算有稳定精度，最终展示金额再按货币规则保留两位。

插入支付和库存流水

INSERT INTO payments
  (payment_id, order_id, paid_at, amount, payment_method, status)
VALUES
  (1,  101, '2025-06-18 10:07:00', 118.90, '微信',   '成功'),
  (2,  102, '2025-07-02 21:12:00', 188.00, '支付宝', '成功'),
  (3,  104, '2025-09-09 09:21:00', 

流水只记录课程时间窗内发生的关键变化，products.stock 是当前库存快照，两者不强行相加相等。比如商品 1 当前库存为 120，而这里展示的流水净额为 99，说明课程数据开始前还有未展开的库存历史。后续聚合时必须先说清统计口径，不能把“部分期间流水”误当成“从商品诞生起的全部流水”。

用行数检查数据是否齐全

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL SELECT 'categories', COUNT(*) FROM categories
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT

结果应当是：

table_name           row_count
customers                   10
categories                   5
products                    12
orders                      13
order_items                 22
payments                    11
departments                  5
employees                    8
inventory_movements         12

这只能确认数量，不能确认内容。初始化脚本还可以检查关键口径：有效销售默认只统计 已支付、已发货、已完成，不把 已取消 和 已退款 算进销售额；订单总额可能含订单级优惠或运费，也不能被明细合计粗暴覆盖。后面的连接和聚合章节会逐步拆开这些问题。现在先把数据基线保存好，不要在每个练习后重新造一套互不相干的数据。

INSERT：先把一行数据说完整

日常插入应省略自动编号

种子数据为了固定示例编号而显式写主键。业务写入时，让 MySQL 产生 AUTO_INCREMENT 值：

START TRANSACTION;
 
INSERT INTO customers
  (customer_name, phone, email, city, registered_at, referrer_id)
VALUES
  ('程月', '13800001011', 'yue.cheng@example.com', '厦门', DEFAULT, 1);
 
SELECT LAST_INSERT_ID() AS new_customer_id;
 
ROLLBACK;

可能看到：

new_customer_id
11

这里用 ROLLBACK 撤销练习数据，让后面的表仍保持十位客户。自动增长序列不保证回滚后重用编号，所以你本机下一次插入也可能得到 12。这是正常现象：主键负责唯一标识，不负责连续好看。

始终写列清单

下面这种写法虽然短，却把语句和表的物理列顺序绑死：

INSERT INTO departments
VALUES (5, '错误示范');

一旦表增加列，它就可能报错，甚至把值放错位置。推荐写法明确列和值的对应关系：

INSERT INTO departments (department_id, department_name)
VALUES (5, '门店拓展部');

如果一次插入多行，整条语句应保持同样的列顺序和每行相同的值数量。Column count doesn't match value count 往往就是列清单与某组值没有对齐。

不要用 INSERT IGNORE 吞掉数据问题

MySQL 有 INSERT IGNORE，某些错误会被降为警告并跳过或调整数据。这在经过设计的批量导入流程里有用途，但不适合初学阶段“先让它跑”。如果唯一邮箱重复，最需要的是知道哪一行冲突，而不是悄悄少插一位客户。

执行写入后可以检查：

SHOW WARNINGS;

更好的默认习惯是保留严格模式、使用普通 INSERT，让不符合规则的数据尽早失败。

UPDATE：把影响范围当成语句的一部分

UPDATE 最危险的地方不是语法复杂，而是语法太简单：没有 WHERE 也完全合法。下面这条会把所有商品都改成下架：

UPDATE products
SET status = '下架';

数据库无法知道你是忘写条件，还是确实要全量修改。

一次安全更新的闭环

假设要把商品 2 从 89.00 元调到 92.00 元，我们按四步做：

完整语句如下：

SELECT product_id, product_name, unit_price
FROM products
WHERE product_id = 2;
 
START TRANSACTION;
 
UPDATE products
SET unit_price = 92.00
WHERE product_id = 2
  AND unit_price = 89.00;
 
SELECT ROW_COUNT() AS changed_rows;
 
SELECT product_id, product_name, unit_price
FROM products

结果会先显示原价，更新后受影响一行，再显示新价：

product_id  product_name    unit_price
2           原木托盘        89.00
 
changed_rows
1
 
product_id  product_name    unit_price
2           原木托盘        92.00

额外的 AND unit_price = 89.00 是乐观检查。如果别人已经调过价，这次更新会影响 0 行，提醒你重新读取，而不会把新价格盲目覆盖。最后 ROLLBACK 让课程基线回到 89.00。

安全更新模式是提醒，不是保险

命令行会话可以开启：

SET SESSION SQL_SAFE_UPDATES = 1;

它会阻止一部分没有使用键列条件的更新或删除。但“条件里出现主键”不代表业务一定正确，WHERE product_id >= 1 依然可能覆盖整表。真正的安全来自预览、精确条件、事务、行数核对和备份，而不是单一开关。

Rows matched 和 Changed 不一定相等

如果把 status 更新为它本来就有的值，MySQL 可能显示匹配一行但实际改变零行。Rows matched 回答条件选中了多少行，Changed 回答值真的发生了多少变化。排查“为什么更新没生效”时，要区分这两个数字。

DELETE：删的是行，不是“界面上的一项”

先理解物理删除的含义

DELETE FROM customers WHERE customer_id = 8 的意思是物理删除客户行，但客户 8 已经有订单 114，外键会阻止它。若业务只是“不再营销”或“账号注销”，真实系统往往需要状态字段、匿名化或归档方案，而不是直接抹去交易主体。本章统一字段里没有客户状态，所以不要借删除语句表达“停用”。

用事务练习删除未被引用的行

客户 7 陆野当前没有订单，可以在事务中演示一次受控删除：

START TRANSACTION;
 
SELECT customer_id, customer_name
FROM customers
WHERE customer_id = 7;
 
DELETE FROM customers
WHERE customer_id = 7;
 
SELECT ROW_COUNT() AS deleted_rows;
 
SELECT customer_id, customer_name
FROM customers
WHERE customer_id = 7;
 
ROLLBACK;

删除阶段会显示：

deleted_rows
1

第二次查询返回空集，ROLLBACK 后客户 7 恢复，种子数据完全不变。若省略 WHERE，整张表的所有行都会成为目标。课程里的任何 UPDATE 或 DELETE 都不鼓励“先执行再看看”。

外键会阻止破坏历史关系

客户 1 已有订单。尝试删除：

DELETE FROM customers
WHERE customer_id = 1;

会失败：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (... CONSTRAINT `fk_orders_customer` ...)

这条错误在说：你要删除父表 customers 的记录，但子表 orders 仍引用它。正确处理不是关闭外键检查，而是回到业务问题：历史订单是否必须保留？客户资料是否应该匿名化？只有明确答案后才设计操作。

读懂约束报错，比绕过报错更有用

报错通常已经包含定位线索。可以按固定顺序读：先看错误号和 SQLSTATE，再看对象名，最后找被违反的规则。不要一看到红字就修改数据类型或关闭模式。

重复主键或唯一值

INSERT INTO customers
  (customer_name, phone, email, city)
VALUES
  ('另一位顾言', '13800001999', 'guyan@example.com', '上海');

ERROR 1062 (23000): Duplicate entry 'guyan@example.com' for key 'customers.uq_customers_email'

1062 表示重复键，uq_customers_email 告诉你冲突的是邮箱唯一约束。接下来要判断这是重复提交、客户已存在，还是唯一规则设计不合理。不要通过给邮箱末尾随便加字符来“修好”数据。

子表引用了不存在的父记录

INSERT INTO orders
  (customer_id, order_date, status, total_amount)
VALUES
  (9999, '2026-05-01 10:00:00', '待支付', 20.00);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (... CONSTRAINT `fk_orders_customer` ...)

1452 的重点是“child row”。新订单是子记录，customer_id = 9999 在父表不存在。可能是插入顺序错了，也可能是调用方传错编号。应该查询父记录或修正输入，不应伪造一个客户 9999 只为通过约束。

CHECK 约束明确返回了 FALSE

UPDATE products
SET stock = -1
WHERE product_id = 1;

ERROR 3819 (HY000): Check constraint 'chk_products_stock' is violated.

约束名直接说明库存不能为负。如果这是一次出库操作，正确做法不是允许负库存，而是先确认可用库存，并在同一事务里减少 products.stock、增加一条负数的 inventory_movements。

再看库存流水的符号：

INSERT INTO inventory_movements
  (product_id, employee_id, movement_type, quantity, note)
VALUES
  (1, 7, '销售出库', 5, '符号写反的示例');

ERROR 3819 (HY000): Check constraint 'chk_inventory_movements_quantity' is violated.

因为“销售出库”必须是负数。改成 -5 才符合这套模型的含义。

NOT NULL 与默认值的边界

INSERT INTO departments
  (department_name)
VALUES
  (NULL);

ERROR 1048 (23000): Column 'department_name' cannot be null

这不是要求你给所有非空列都设置默认值。部门名称属于必须由调用方明确给出的事实，若随手默认成“未命名部门”，只会把输入错误推迟到报表阶段。

字符串超长

INSERT INTO departments (department_name)
VALUES ('这是一个故意写得非常非常长并且超过六十个字符限制的部门名称用于演示数据库如何拒绝超出边界的数据而不是把边界当作摆设');

在严格模式下会看到：

ERROR 1406 (22001): Data too long for column 'department_name' at row 1

先检查输入是否异常，再判断 40 个字符的业务边界是否真的太小。不能因为一条脏数据就盲目把列改成 LONGTEXT。

日期格式或日期本身无效

INSERT INTO employees
  (department_id, manager_id, employee_name, job_title, salary, hire_date)
VALUES
  (3, 3, '测试员工', '临时岗位', 7000.00, '2026-02-30');

ERROR 1292 (22007): Incorrect date value: '2026-02-30' for column 'hire_date' at row 1

字符串看起来像日期，不代表日历上真有这一天。应用可以先校验格式，但数据库仍应保留最后一道检查。

列数和值数不一致

INSERT INTO departments
  (department_id, department_name)
VALUES
  (9);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

从 at row 1 开始检查对应的 VALUES 分组。如果是多行插入，常见情况是其中一组漏了值。

一张排错速查表

错误号比整段英文消息更稳定，约束名比“第几个检查”更容易定位。给约束命名，是在为未来排错节省时间。

把建表脚本当成可以复现的产品

为什么不建议边点界面边建表

图形化工具适合查看结构，但如果所有结构都靠鼠标点击，团队很难知道谁改过什么，也无法在另一台机器上精确复现。SQL 脚本可以进入版本控制、接受评审、按顺序执行，也能和应用版本一起演进。

一份可复现的初始化内容至少分为：

• 数据库和账号，由管理员执行。
• schema，即九张表、约束和索引，由迁移账号执行。
• seed，即本章提供的固定课程数据，由迁移账号执行一次。
• 验证查询，用来确认表数、行数和关键不变量。

本章把它们放在一篇文章里是为了连贯学习。实际项目里通常拆成多个带版本号的迁移文件，已经执行过的迁移不会被随意改写，而是追加新的变更。

初始化不是无限次幂等

建库语句用了 IF NOT EXISTS，但表和种子数据故意没有到处加忽略逻辑。第二次执行种子数据会触发主键或唯一约束错误，这反而清楚地告诉你“已经初始化过”。如果用 INSERT IGNORE 把所有重复都吞掉，很可能得到一半旧数据、一半新数据的混合状态。

需要重建课程库时，应先确认这是可丢弃的学习数据库，再由管理员做完整备份或重新创建空库。不要在共享环境里复制一段含 DROP DATABASE 的脚本直接执行。

用最小验证集做交接

交给下一章前，可以依次执行：

SELECT DATABASE();
SHOW TABLES;
SHOW CREATE TABLE products;
SHOW CREATE TABLE order_items;
 
SELECT COUNT(*) AS customer_count FROM customers;
SELECT COUNT(*) AS order_count FROM orders;
SELECT COUNT(*) AS movement_count FROM inventory_movements;

期望值分别是 10、13 和 12。再确认三种刻意保留的缺失值：

SELECT customer_id, customer_name, phone, email
FROM customers
WHERE phone IS NULL OR email IS NULL;

customer_id  customer_name  phone        email
3            白露           NULL         bailu@example.com
5            乔木           13800001005  NULL
7            陆野           NULL         luye@example.com

这三行不是脏数据，它们让我们下一章能真正观察 NULL 对筛选条件的影响。

留给 PostgreSQL 读者的迁移清单

这套模型的关系、约束意图和种子内容可以迁移，但脚本需要按 PostgreSQL 语法改写：

不要只追求“语句不报错”。例如 PostgreSQL 没有无符号整数，如果简单去掉 UNSIGNED 却不补正数约束，模型允许的值就变了。迁移要逐项确认业务规则是否仍然成立。

练习：让约束替你说“不”

设计一条合法库存流水

商品 3 由员工 7 新入库 40 件，时间为 2026-05-01 09:00:00，备注为“五月补货”。写出插入语句。为了不改变后续课程基线，请放在事务里并回滚。

START TRANSACTION;
 
INSERT INTO inventory_movements
  (product_id, employee_id, movement_type, quantity, moved_at, note)
VALUES
  (3, 7, '采购入库', 40, '2026-05-01 09:00:00', '五月补货');
 
SELECT ROW_COUNT() AS inserted_rows;
 
ROLLBACK;

找出支付失败的原因

下面这条支付为什么会失败？

INSERT INTO payments
  (order_id, paid_at, amount, payment_method, status)
VALUES
  (110, NULL, 99.00, '微信', '成功');

写出安全调价

把商品 7 的价格从 79.00 元调整为 82.00 元。要求先预览，只在旧价格仍为 79.00 时更新，检查影响行数，最后回滚。

SELECT product_id, product_name, unit_price
FROM products
WHERE product_id = 7;
 
START TRANSACTION;
 
UPDATE products
SET unit_price = 82.00
WHERE product_id = 7
  AND unit_price = 79.00;
 
SELECT ROW_COUNT() AS changed_rows;
 
SELECT product_id, product_name, unit_price
FROM products

判断哪些数据能通过

选择合适的删除策略

从“存进去”走向“问出来”

现在，小满商店已经有了九张互相关联的表，也有一组不会只返回单一答案的数据。我们知道为什么金额用 DECIMAL，为什么可选联系方式允许 NULL，为什么订单明细要保留下单时价格；也知道权限、非空、唯一、检查和外键不是装饰，它们会在错误写入到达数据库时明确拒绝。

最后看一条只读语句：

SELECT product_id, product_name, unit_price, stock
FROM products
WHERE status = '在售'
  AND stock > 0
ORDER BY unit_price DESC;

它提出的问题很具体：哪些商品正在销售且仍有库存，并按价格从高到低排列？结果如下：

product_id  product_name        unit_price  stock
5           65W 氮化镓充电器     169.00      32
10          保温随行杯            139.00      39
8           暖光阅读灯            129.00      28
9           轻量折叠伞             99.00      64
2           原木托盘               89.00      45
3           四季手账本             49.00      86
1           白瓷马克杯             39.90     120
6           编织数据线             39.00     210
4           雾蓝中性笔套装         29.90     150
12          香樟衣柜挂片           25.00      90

下一章会从这条 SELECT 拆起：选择哪些列、从哪张表读取、怎样用 WHERE 过滤、怎样处理 NULL、为什么没有 ORDER BY 就不能假设结果顺序。到那时，我们不再对着空表讲语法，而是直接向小满商店提问。

若 changed_rows 为 0，先重新查询。可能是商品不存在，也可能是价格已被别人修改。不要直接删掉旧价格条件来强行覆盖。