第9章 数据增删改操作实战与商业应用

9.1 插入数据：从单条记录到批量导入

在商业系统中，数据的插入是最基础也是最频繁的操作。无论是用户注册、订单生成，还是商品上架，背后都是INSERT语句在默默工作。看似简单的插入，如果处理不当，可能导致数据不一致、性能瓶颈甚至系统崩溃。

9.1.1 为表的所有字段插入数据

当我们需要向表中添加一条完整记录时，可以使用最标准的INSERT语法：

INSERT INTO 表名 VALUES (值1, 值2, ...);

这里要求值的顺序必须与表定义字段的顺序完全一致，数量也必须匹配。例如在电商用户表中：

INSERT INTO users VALUES (NULL, '张三', '13800138000', '2024-03-15 10:30:00', 1);

假设users表结构为：user_id INT AUTO_INCREMENT, username VARCHAR(50), phone VARCHAR(11), register_time DATETIME, level TINYINT。这里用NULL让自增字段自动生成。

但在商业项目中，这种写法并不推荐，因为表结构可能变化，字段顺序调整会导致插入失败。更稳妥的方式是指定字段名，如下一节所述。

9.1.2 为表的指定字段插入数据

明确指定字段名，可以提高代码的可读性和健壮性：

INSERT INTO users (username, phone, register_time, level) 
VALUES ('李四', '13912345678', NOW(), 1);

这里没有指定user_id，因为它是自增的，MySQL会自动生成。指定字段的插入也允许只插入部分字段，但那些未指定的字段必须允许NULL或有默认值，否则会报错。

商业场景：用户注册时，前端只传递用户名和手机号，注册时间和会员等级由后端自动填充：

INSERT INTO users (username, phone, register_time, level) 
VALUES (?, ?, NOW(), 1);

使用占位符防止SQL注入。

9.1.3 同时插入多条记录

批量插入可以显著提升性能，减少与数据库的交互次数。语法是在VALUES后面跟多个括号：

INSERT INTO users (username, phone, register_time, level) VALUES 
('王五', '13512345678', NOW(), 1),
('赵六', '13612345678', NOW(), 2),
('钱七', '13712345678', NOW(), 1);

在商业项目中，比如后台批量导入会员数据，或者初始化商品规格，都可以用这种写法。相比逐条插入，速度能提升数倍甚至数十倍。

9.1.4 将查询结果插入到表中

这是非常实用的功能，常用于数据备份、报表生成、数据迁移。语法是INSERT INTO … SELECT …。

例如，我们需要将本月订单明细汇总到一张月结表中：

INSERT INTO monthly_order_summary (user_id, total_amount, order_month)
SELECT user_id, SUM(total_amount), DATE_FORMAT(create_time, '%Y-%m')
FROM orders
WHERE create_time BETWEEN '2024-03-01' AND '2024-03-31'
GROUP BY user_id, DATE_FORMAT(create_time, '%Y-%m');

另一个常见场景：创建一张表的副本（包括数据）：

CREATE TABLE users_backup LIKE users;
INSERT INTO users_backup SELECT * FROM users;

注意：SELECT的目标字段类型必须与INSERT表的目标字段兼容。

9.1.5 插入冲突处理：ON DUPLICATE KEY UPDATE

在商业系统中，经常遇到“有则更新，无则插入”的需求。MySQL提供了ON DUPLICATE KEY UPDATE语法，当插入导致唯一键或主键冲突时，执行更新操作。

例如，在用户积分变更表中，我们希望每天每个用户的积分记录只有一条，如果当天已有记录则累加积分：

INSERT INTO user_points (user_id, points_date, points) 
VALUES (1001, CURDATE(), 50)
ON DUPLICATE KEY UPDATE points = points + 50;

前提是表有唯一索引 (user_id, points_date)。

这个特性在计数器、实时统计等场景非常有用。

9.1.6 插入时的高并发注意事项

在高并发写入场景，比如秒杀订单、抢红包，直接INSERT可能导致锁竞争和性能下降。需要考虑：

• 使用批量插入合并多个请求。
• 考虑使用消息队列异步落库。
• 合理设计索引，避免插入时更新过多索引。
• 使用InnoDB的AUTO_INCREMENT锁机制，可以通过innodb_autoinc_lock_mode参数优化。

9.2 更新数据：精确修改与批量调整

更新数据是业务逻辑中不可或缺的一环，比如修改订单状态、调整商品价格、更新用户信息。UPDATE语句看似简单，但稍有不慎就可能造成大面积数据错误。

9.2.1 基本的UPDATE语法

UPDATE 表名 SET 字段1=值1, 字段2=值2 WHERE 条件;

例如，修改用户手机号：

UPDATE users SET phone = '15912345678' WHERE user_id = 1001;

务必加上WHERE条件，否则会更新整张表！这是生产环境的大忌。

9.2.2 多表更新

有时需要根据另一张表的数据来更新当前表。MySQL支持多表UPDATE语法。

例如，根据订单表统计每个用户的消费总额，更新到用户表的total_spent字段：

UPDATE users u
JOIN (SELECT user_id, SUM(total_amount) AS spent FROM orders GROUP BY user_id) o ON u.user_id = o.user_id
SET u.total_spent = o.spent;

也可以直接用关联更新：

UPDATE users u, orders o 
SET u.total_spent = u.total_spent + o.total_amount
WHERE u.user_id = o.user_id AND o.pay_status = 'paid';

但要注意，多表更新可能锁定多行，需谨慎。

9.2.3 使用ORDER BY和LIMIT限制更新行数

在某些场景，比如只更新最早的一条记录，可以配合ORDER BY和LIMIT。

例如，将库存中最早入库的一批商品（先进先出）标记为已出库：

UPDATE inventory 
SET status = 'sold' 
WHERE product_id = 1001 AND status = 'in_stock'
ORDER BY create_time ASC
LIMIT 10;

9.2.4 更新时的数据一致性

在并发环境下，更新操作可能造成数据不一致。例如，扣减库存时，如果多个线程同时读取同一库存并执行UPDATE，可能导致超卖。解决方案：

• 使用乐观锁（版本号）：

UPDATE products SET stock = stock - 1, version = version + 1 
WHERE product_id = 1001 AND stock > 0 AND version = old_version;

• 使用悲观锁（SELECT … FOR UPDATE）：

START TRANSACTION;
SELECT stock FROM products WHERE product_id = 1001 FOR UPDATE;
-- 检查库存，执行更新
UPDATE products SET stock = stock - 1 WHERE product_id = 1001;
COMMIT;

• 利用MySQL的原子操作：直接 UPDATE … SET stock = stock - 1 WHERE stock > 0。

9.2.5 大表更新策略

对于千万级大表，直接UPDATE可能导致长事务、锁范围大、主从延迟。应采用分批更新策略，每次更新一小部分，并加上LIMIT。

例如，清理过期数据：

UPDATE user_sessions SET status = 'expired' 
WHERE last_active < DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 1000;

循环执行直到影响行数为0。

9.3 删除数据：谨慎操作与软删除实践

删除操作是风险最高的数据操作，误删后难以恢复。商业系统中通常不直接物理删除，而是采用软删除（标记删除）。

9.3.1 基本DELETE语法

DELETE FROM 表名 WHERE 条件;

例如，删除测试用户：

DELETE FROM users WHERE username = 'testuser';

与UPDATE一样，务必加上WHERE，否则清空表。

9.3.2 多表删除

有时需要同时删除多张表的关联记录。例如，删除用户及其所有订单：

DELETE u, o FROM users u LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id = 1001;

或者使用子查询。

9.3.3 使用ORDER BY和LIMIT限制删除行数

和UPDATE类似，可以限制删除数量，避免长事务。

DELETE FROM logs WHERE log_time < '2024-01-01' ORDER BY log_time ASC LIMIT 1000;

9.3.4 软删除实现

软删除是在表中增加一个字段（如is_deleted或status），用UPDATE代替DELETE。

例如用户表添加is_deleted TINYINT DEFAULT 0。

软删除：

UPDATE users SET is_deleted = 1 WHERE user_id = 1001;

查询时自动过滤：

SELECT * FROM users WHERE is_deleted = 0;

软删除的优势是可以恢复数据，且不影响外键约束。缺点是需要额外维护状态，且唯一约束可能失效（例如唯一用户名，软删除后不能再用同一个用户名注册，需要处理）。

9.3.5 删除大表的注意事项

直接DROP或DELETE大表会产生大量IO，影响业务。推荐使用：

• 分批删除（如前所述）。
• 使用TRUNCATE快速清空表（但无法回滚，且重置自增值）。
• 删除表前先解除外键引用，或使用RENAME TABLE备份。

9.3.6 误删恢复策略

生产中务必开启binlog，并定期备份。误删后可以通过binlog进行时间点恢复。例如：

mysqlbinlog --start-datetime="2024-03-15 10:00:00" --stop-datetime="2024-03-15 11:00:00" binlog.000001 | mysql -u root -p

更详细的恢复步骤需要结合备份策略。

9.4 综合案例：电商订单系统的增删改操作

我们模拟一个电商订单从生成到完成的完整数据操作流程，涵盖插入、更新、删除，并考虑事务和并发。

9.4.1 表结构回顾

• users(user_id, username, points)
• products(product_id, product_name, stock, price)
• orders(order_id, user_id, total_amount, status, create_time)
• order_items(item_id, order_id, product_id, quantity, price)

9.4.2 用户下单（插入+更新）

用户下单涉及插入订单表、插入订单明细表、扣减库存，必须在一个事务中完成。

START TRANSACTION;

-- 插入订单主表
INSERT INTO orders (user_id, total_amount, status, create_time) 
VALUES (1001, 5999, 'pending', NOW());
SET @order_id = LAST_INSERT_ID();

-- 插入订单明细
INSERT INTO order_items (order_id, product_id, quantity, price) 
VALUES (@order_id, 2001, 1, 5999);

-- 扣减库存
UPDATE products SET stock = stock - 1 
WHERE product_id = 2001 AND stock >= 1;

-- 检查库存是否扣减成功（ROW_COUNT() > 0），如果失败则回滚
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    SELECT '库存不足';
ELSE
    COMMIT;
    SELECT '下单成功';
END IF;

在应用程序中，可以根据更新影响行数决定提交或回滚。

9.4.3 用户支付（更新订单状态）

支付成功后，更新订单状态，同时可能给用户增加积分：

UPDATE orders SET status = 'paid', pay_time = NOW() 
WHERE order_id = 10086 AND status = 'pending';

-- 如果影响行数为1，则给用户加积分
UPDATE users SET points = points + 599 WHERE user_id = (SELECT user_id FROM orders WHERE order_id = 10086);

这里可以用事务保证一致性。

9.4.4 取消订单（更新状态+恢复库存）

如果用户取消订单，需要将订单状态改为cancelled，并恢复库存：

START TRANSACTION;

-- 更新订单状态
UPDATE orders SET status = 'cancelled' 
WHERE order_id = 10086 AND status = 'pending';

-- 恢复库存
UPDATE products p
JOIN order_items oi ON p.product_id = oi.product_id
SET p.stock = p.stock + oi.quantity
WHERE oi.order_id = 10086;

COMMIT;

9.4.5 删除过期测试订单（物理删除）

对于测试数据，可以物理删除：

DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE status = 'test');
DELETE FROM orders WHERE status = 'test';

或者用JOIN删除。

9.4.6 批量插入订单数据（数据迁移）

假设要从旧系统导入历史订单：

INSERT INTO orders (order_id, user_id, total_amount, status, create_time)
SELECT order_id, user_id, amount, status, create_time FROM legacy_orders;

注意自增主键冲突问题，可能需要调整。

9.4.7 使用ON DUPLICATE KEY UPDATE更新每日统计

每日统计用户下单次数：

INSERT INTO user_daily_stats (user_id, stat_date, order_count)
VALUES (1001, CURDATE(), 1)
ON DUPLICATE KEY UPDATE order_count = order_count + 1;

9.5 专家解惑：常见问题与解决方案

9.5.1 插入数据时出现主键冲突怎么办？

如果主键是自增，一般不会冲突。如果手动指定了值，或者从其他表插入，可能遇到重复。解决方法：

• 使用INSERT IGNORE忽略冲突（不报错，也不插入）。
• 使用REPLACE INTO（先删除后插入，注意可能删除其他数据）。
• 使用ON DUPLICATE KEY UPDATE。

9.5.2 更新数据时，如何避免长时间锁表？

UPDATE没有WHERE条件会锁全表。即使有WHERE，如果条件没有索引，也会锁很多行。确保WHERE条件使用索引，并且尽量缩小范围。对于大表更新，分批进行。

9.5.3 删除数据后，自增主键会重用吗？

InnoDB不会重用已删除的自增值，除非手动重置。但MySQL重启后，自增计数器可能变化。通常不需要担心重用问题。

9.5.4 如何高效地批量插入百万级数据？

可以使用LOAD DATA INFILE，比INSERT快得多。例如从CSV文件导入：

LOAD DATA INFILE '/tmp/data.csv' INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(username, phone, register_time, level);

或者使用mysqlimport工具。

9.5.5 如何保证数据插入的完整性？

使用外键约束（但可能影响性能），或者在应用层保证。事务是必须的，尤其是在多表操作时。

9.5.6 误删数据后如何恢复？

如果没有备份，可以尝试从binlog恢复。所以binlog必须开启，且设置格式为ROW。定期备份也是必须的。

9.6 经典习题与实战思考

1. 设计一个学生选课系统，有学生表、课程表、选课表。写出向选课表插入一条选课记录的SQL，要求检查课程是否还有名额，并在事务中完成。
2. 使用INSERT … SELECT将成绩大于90分的学生复制到优秀学生表。
3. 解释UPDATE语句如果没有WHERE条件会有什么后果。
4. 什么是软删除？如何实现？优缺点是什么？
5. 如何批量删除一个月前的日志数据（表数据量上亿）？请写出具体方案。
6. 在并发环境下，如何安全地扣减商品库存？
7. 使用ON DUPLICATE KEY UPDATE实现一个简单的计数器（记录每个页面的访问次数）。
8. 误删了users表中的所有数据，如何利用binlog恢复？
9. 简述TRUNCATE、DELETE、DROP的区别。
10. 编写一个存储过程，循环删除过期数据，每次删除1000条，直到删除完毕。

本章详细介绍了数据增删改的各种操作技巧和商业应用场景，从基础语法到高并发下的最佳实践。掌握这些内容，你就能安全高效地操作数据库中的数据，为业务系统的稳定运行打下坚实基础。记住，每一次UPDATE和DELETE都是一次责任，务必谨慎。