MySQL / 系统课时

编写业务增删改查

编写业务增删改查

这一课把用户、商品和订单三类数据串成一套可执行的 CRUD 操作。你会建立最小业务表,完成新增、查询、修改和删除,并通过事务与外键约束避免把订单改成不一致的数据。

学习目标

  • 能为用户、商品和订单编写常见的新增、查询、修改与删除 SQL
  • 能用事务保证订单写入与库存扣减要么一起成功、要么一起回滚
  • 能识别删除顺序、主键条件和受影响行数等关键边界

准备业务数据表

示例使用 users、products、orders 三张表。订单通过外键引用用户和商品,并记录购买数量与下单时的商品单价。这样即使商品以后调价,历史订单金额仍能按订单中的 unit_price 计算。

sql
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'CREATED',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_orders_product FOREIGN KEY (product_id) REFERENCES products(id)
);

预期结果

text
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected

不同 MySQL 版本显示的执行时间可能不同,但三张表都应成功创建。

完成新增与查询

先写入一名用户和一个商品,再生成订单。实际业务中不应依赖固定自增 ID,因此这里使用 LAST_INSERT_ID() 保存刚刚生成的主键。

sql
INSERT INTO users (username) VALUES ('alice');
SET @user_id = LAST_INSERT_ID();

INSERT INTO products (name, price, stock)
VALUES ('机械键盘', 399.00, 20);
SET @product_id = LAST_INSERT_ID();

INSERT INTO orders (user_id, product_id, quantity, unit_price)
VALUES (@user_id, @product_id, 2, 399.00);

SELECT o.id,
       u.username,
       p.name AS product_name,
       o.quantity,
       o.unit_price,
       o.quantity * o.unit_price AS order_amount,
       o.status
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
JOIN products AS p ON p.id = o.product_id
WHERE o.id = LAST_INSERT_ID();

预期结果

text
+----+----------+--------------+----------+------------+--------------+---------+
| id | username | product_name | quantity | unit_price | order_amount | status  |
+----+----------+--------------+----------+------------+--------------+---------+
| <ID> | alice    | 机械键盘       |        2 |     399.00 |       798.00 | CREATED |
+----+----------+--------------+----------+------------+--------------+---------+

<ID> 是数据库生成的订单主键,实际值会随当前数据变化。业务查询通常应明确列名,不建议长期使用 SELECT *,这样可以减少无关字段传输,也能避免表结构变化影响调用方。

用事务完成订单修改

订单提交后,库存扣减和订单状态变化属于同一个业务动作。若只成功执行其中一条 SQL,就会出现订单已确认但库存没减少,或者库存减少却没有有效订单的问题。

下面的事务先锁定商品行并检查库存,再扣减库存、更新订单状态。示例假设当前订单购买数量为 2,商品库存至少为 2。

sql
START TRANSACTION;

SELECT stock
FROM products
WHERE id = @product_id
FOR UPDATE;

UPDATE products
SET stock = stock - 2
WHERE id = @product_id
  AND stock >= 2;

UPDATE orders
SET status = 'PAID'
WHERE user_id = @user_id
  AND product_id = @product_id
  AND status = 'CREATED';

COMMIT;

SELECT stock
FROM products
WHERE id = @product_id;

SELECT status
FROM orders
WHERE user_id = @user_id
  AND product_id = @product_id;

预期结果

text
+-------+
| stock |
+-------+
|    18 |
+-------+

+--------+
| status |
+--------+
| PAID   |
+--------+

生产代码还应检查每条 UPDATE 的受影响行数。如果库存更新影响 0 行,说明库存不足或商品不存在,此时应执行 ROLLBACK,而不是继续把订单改成已支付。

安全地修改和删除数据

修改用户状态时,应使用主键或其他明确条件限制目标行。删除数据时则要考虑外键依赖:当前结构下,存在订单的用户或商品不能直接删除。

sql
UPDATE users
SET status = 'DISABLED'
WHERE id = @user_id;

DELETE FROM orders
WHERE user_id = @user_id
  AND product_id = @product_id;

DELETE FROM users
WHERE id = @user_id;

DELETE FROM products
WHERE id = @product_id;

预期结果

text
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected

真实系统通常不会随意物理删除订单,而会通过状态字段进行取消、关闭或归档。无论采用物理删除还是逻辑删除,核心原则都一样:写操作必须带明确条件,删除前先处理依赖关系,并核对受影响行数是否符合预期。

CRUD 编写时的关键边界

新增时要依赖数据库约束阻止重复用户名、无效外键等错误;查询时只返回业务需要的字段;修改和删除时优先使用主键或唯一键定位;涉及多张表的一致性操作则放入同一事务。把这几条原则落实到 SQL 中,基础 CRUD 才能从“能执行”提升到“可以安全用于业务”。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

编写业务增删改查

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通