跳到主要内容

UPDATE 更新

UPDATE 语句用于修改表中的现有数据。本章将详细介绍 UPDATE 的各种用法。

基本 UPDATE​

更新单个列​

UPDATE users SET city = '上海' WHERE id = 1;

更新多个列​

UPDATE users 
SET city = '上海', age = 26 
WHERE id = 1;

更新所有行​

-- 危险！更新所有行
UPDATE users SET status = 'active';

-- 建议先查询确认
SELECT * FROM users;  -- 确认要更新的数据
警告

不带 WHERE 子句的 UPDATE 会更新所有行！在生产环境中要特别小心。

WHERE 条件​

使用条件更新​

-- 更新特定条件的记录
UPDATE products 
SET price = price * 1.1 
WHERE category = '电子产品';

-- 更新多个条件
UPDATE orders 
SET status = 'cancelled' 
WHERE status = 'pending' 
  AND created_at < '2024-01-01';

-- 使用子查询
UPDATE users 
SET status = 'vip' 
WHERE id IN (SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 10000);

使用 LIMIT 限制​

-- MySQL: 只更新前 10 条
UPDATE users 
SET status = 'processed' 
WHERE status = 'pending' 
LIMIT 10;

-- PostgreSQL: 使用子查询
UPDATE users 
SET status = 'processed' 
WHERE id IN (
    SELECT id FROM users WHERE status = 'pending' LIMIT 10
);

表达式更新​

算术运算​

-- 价格上涨 10%
UPDATE products SET price = price * 1.1;

-- 库存减少
UPDATE products 
SET stock = stock - 1 
WHERE id = 1;

-- 使用函数
UPDATE users 
SET name = UPPER(name);

CASE 表达式​

UPDATE products 
SET price = CASE 
    WHEN category = '电子产品' THEN price * 1.1
    WHEN category = '服装' THEN price * 1.05
    ELSE price * 1.02
END;

字符串操作​

-- 拼接字符串
UPDATE users 
SET email = CONCAT(name, '@example.com');

-- 替换字符串
UPDATE products 
SET name = REPLACE(name, '旧', '新');

-- 截取字符串
UPDATE users 
SET phone = CONCAT('****', RIGHT(phone, 4));

多表更新​

MySQL: 多表 UPDATE​

UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.discount = 0.1
WHERE u.vip_level = 'gold';

PostgreSQL: FROM 子句​

UPDATE orders o
SET discount = 0.1
FROM users u
WHERE o.user_id = u.id AND u.vip_level = 'gold';

SQL Server: FROM 子句​

UPDATE o
SET discount = 0.1
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.vip_level = 'gold';

返回更新结果​

PostgreSQL: RETURNING​

-- 返回更新后的数据
UPDATE users 
SET age = age + 1 
WHERE id = 1
RETURNING *;

-- 返回特定列
UPDATE products 
SET stock = stock - 1 
WHERE id = 1
RETURNING id, name, stock;

MySQL: 获取影响行数​

UPDATE users SET status = 'active' WHERE age > 18;
SELECT ROW_COUNT();  -- 返回影响的行数

安全更新​

使用事务​

START TRANSACTION;

-- 先查询确认
SELECT * FROM users WHERE status = 'inactive';

-- 执行更新
UPDATE users SET status = 'active' WHERE status = 'inactive';

-- 确认无误后提交
COMMIT;

-- 如果有问题，可以回滚
-- ROLLBACK;

MySQL 安全模式​

-- 启用安全更新模式（必须使用键列）
SET SQL_SAFE_UPDATES = 1;

-- 这样会报错（没有使用键列）
UPDATE users SET city = '上海';

-- 这样可以（使用了键列）
UPDATE users SET city = '上海' WHERE id = 1;

先查询再更新​

-- 好的做法：先查询确认
SELECT COUNT(*) FROM users WHERE status = 'inactive';
-- 确认数量合理后再更新
UPDATE users SET status = 'active' WHERE status = 'inactive';

性能优化​

使用索引​

-- 确保条件列有索引
CREATE INDEX idx_status ON users(status);

UPDATE users SET city = '上海' WHERE status = 'active';

分批更新​

-- 大批量更新分批进行
-- MySQL
UPDATE users SET status = 'processed' WHERE status = 'pending' LIMIT 1000;
-- 重复执行直到影响行数为 0

-- PostgreSQL
UPDATE users 
SET status = 'processed' 
WHERE id IN (
    SELECT id FROM users WHERE status = 'pending' LIMIT 1000
);

避免全表扫描​

-- 差：没有索引，全表扫描
UPDATE users SET city = '上海' WHERE YEAR(created_at) = 2024;

-- 好：使用索引
UPDATE users 
SET city = '上海' 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

常见错误​

忘记 WHERE 子句​

-- 错误：更新所有行！
UPDATE users SET status = 'active';

-- 正确：指定条件
UPDATE users SET status = 'active' WHERE id = 1;

更新不存在的记录​

-- 不会报错，但影响行数为 0
UPDATE users SET city = '上海' WHERE id = 99999;

-- 检查影响行数
SELECT ROW_COUNT();  -- MySQL

违反约束​

-- 违反唯一约束
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- Error: Duplicate entry '[email protected]' for key 'email'

-- 违反外键约束
UPDATE orders SET user_id = 999 WHERE id = 1;
-- Error: Cannot add or update a child row: a foreign key constraint fails

小结​

本章我们学习了：

  1. 基本 UPDATE 语法
  2. WHERE 条件更新
  3. 表达式更新
  4. 多表更新
  5. 返回更新结果
  6. 安全更新实践
  7. 性能优化
  8. 常见错误

练习​

  1. 将 id 为 1 的用户城市更新为"深圳"
  2. 将所有电子产品的价格降低 5%
  3. 将 2024 年之前的订单状态更新为"已过期"
  4. 使用 CASE 表达式根据不同条件更新用户等级
  5. 使用事务安全地执行批量更新