数据更新 #

-- 基本更新语法
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

-- 示例表
CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    stock INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO products (name, price, stock) VALUES
    ('Product A', 99.99, 100),
    ('Product B', 149.99, 50),
    ('Product C', 199.99, 30);

-- 更新单个字段
UPDATE products 
SET price = 89.99 
WHERE id = 1;

-- 查看结果
SELECT * FROM products WHERE id = 1;

-- 更新多个字段
UPDATE products 
SET 
    price = 79.99,
    stock = 150,
    status = 'promoted'
WHERE id = 1;

-- 使用表达式更新
UPDATE products 
SET 
    price = price * 0.9,  -- 打9折
    stock = stock + 50    -- 增加库存
WHERE id = 2;

SELECT * FROM products;

-- 使用各种条件更新

-- 等于条件
UPDATE products SET status = 'inactive' WHERE id = 3;

-- 范围条件
UPDATE products 
SET price = price * 0.8 
WHERE price > 100;

-- IN 条件
UPDATE products 
SET status = 'featured' 
WHERE id IN (1, 2);

-- LIKE 条件
UPDATE products 
SET status = 'sale' 
WHERE name LIKE 'Product%';

-- 多条件组合
UPDATE products 
SET price = price * 0.95 
WHERE stock > 50 AND status = 'active';

-- 危险操作: 没有 WHERE 子句
UPDATE products SET price = 0;
-- 这会更新所有行！

-- 安全做法: 始终使用 WHERE 子句
UPDATE products 
SET price = 0 
WHERE id = 999;  -- 明确条件

-- 使用 LIMIT 限制影响行数
UPDATE products 
SET status = 'inactive' 
WHERE stock = 0 
LIMIT 100;

-- 先查询确认要更新的数据
SELECT * FROM products 
WHERE stock = 0 AND status = 'active';

-- 确认无误后再更新
UPDATE products 
SET status = 'out_of_stock' 
WHERE stock = 0 AND status = 'active';

-- 使用事务确保安全
START TRANSACTION;

SELECT * FROM products WHERE id = 1 FOR UPDATE;

UPDATE products SET price = 59.99 WHERE id = 1;

-- 确认结果
SELECT * FROM products WHERE id = 1;

COMMIT;

-- 关键更新使用事务
START TRANSACTION;

-- 更新库存
UPDATE products 
SET stock = stock - 1 
WHERE id = 1 AND stock > 0;

-- 检查是否成功
SELECT ROW_COUNT() AS affected_rows;

-- 如果 affected_rows = 0，说明库存不足
-- 可以选择回滚
-- ROLLBACK;

-- 确认后提交
COMMIT;

-- 加减乘除
UPDATE products 
SET 
    price = price + 10,        -- 加价
    stock = stock - 5          -- 减库存
WHERE id = 1;

-- 乘除
UPDATE products 
SET price = price * 1.1        -- 涨价10%
WHERE category = 'electronics';

UPDATE products 
SET price = price / 2          -- 半价
WHERE status = 'clearance';

-- 取模
UPDATE products 
SET stock = stock - (stock % 10)  -- 库存取整
WHERE id = 1;

-- 字符串拼接
UPDATE products 
SET name = CONCAT(name, ' - New')
WHERE id = 1;

-- 大小写转换
UPDATE products 
SET name = UPPER(name)
WHERE id = 1;

UPDATE products 
SET name = LOWER(name)
WHERE id = 2;

-- 字符串替换
UPDATE products 
SET name = REPLACE(name, 'Old', 'New')
WHERE name LIKE '%Old%';

-- 截取字符串
UPDATE products 
SET name = LEFT(name, 50)
WHERE LENGTH(name) > 50;

-- 创建带日期的表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    amount DECIMAL(10, 2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expired_at TIMESTAMP
);

-- 更新日期
UPDATE orders 
SET expired_at = DATE_ADD(created_at, INTERVAL 30 DAY)
WHERE expired_at IS NULL;

-- 更新状态
UPDATE orders 
SET status = 'expired'
WHERE expired_at < NOW() AND status = 'pending';

-- 设置特定日期
UPDATE orders 
SET created_at = '2024-01-01 00:00:00'
WHERE id = 1;

-- 使用 CASE 条件更新
UPDATE products 
SET status = CASE
    WHEN stock = 0 THEN 'out_of_stock'
    WHEN stock < 10 THEN 'low_stock'
    WHEN stock < 50 THEN 'normal'
    ELSE 'sufficient'
END;

-- 多字段 CASE 更新
UPDATE products 
SET 
    status = CASE
        WHEN stock = 0 THEN 'out_of_stock'
        ELSE status
    END,
    price = CASE
        WHEN stock > 100 THEN price * 0.9
        ELSE price
    END;

-- 创建关联表
CREATE TABLE categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    discount DECIMAL(5, 2)
);

INSERT INTO categories (name, discount) VALUES
    ('Electronics', 10),
    ('Clothing', 20),
    ('Books', 15);

ALTER TABLE products ADD COLUMN category_id BIGINT;
UPDATE products SET category_id = 1 WHERE id IN (1, 2);
UPDATE products SET category_id = 2 WHERE id = 3;

-- 关联更新
UPDATE products p
JOIN categories c ON p.category_id = c.id
SET p.price = p.price * (1 - c.discount / 100)
WHERE c.discount > 0;

SELECT p.id, p.name, p.price, c.name AS category, c.discount
FROM products p
JOIN categories c ON p.category_id = c.id;

-- 使用子查询更新
UPDATE products 
SET price = (
    SELECT AVG(price) FROM products WHERE category_id = 1
)
WHERE id = 1;

-- 使用子查询条件
UPDATE products 
SET status = 'premium'
WHERE category_id IN (
    SELECT id FROM categories WHERE discount > 15
);

-- 使用 EXISTS
UPDATE products p
SET p.status = 'discounted'
WHERE EXISTS (
    SELECT 1 FROM categories c 
    WHERE c.id = p.category_id AND c.discount > 10
);

-- 批量更新满足条件的记录
UPDATE products 
SET status = 'inactive'
WHERE stock = 0;

-- 分批更新大量数据
-- 每次更新 1000 条
UPDATE products 
SET status = 'processed'
WHERE status = 'pending'
LIMIT 1000;

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

-- 批量更新不同值
UPDATE products 
SET price = CASE id
    WHEN 1 THEN 89.99
    WHEN 2 THEN 139.99
    WHEN 3 THEN 189.99
    ELSE price
END
WHERE id IN (1, 2, 3);

-- 批量更新多个字段
UPDATE products 
SET 
    price = CASE id
        WHEN 1 THEN 79.99
        WHEN 2 THEN 129.99
        ELSE price
    END,
    stock = CASE id
        WHEN 1 THEN 200
        WHEN 2 THEN 100
        ELSE stock
    END
WHERE id IN (1, 2);

-- 创建临时表存储更新数据
CREATE TEMPORARY TABLE temp_updates (
    product_id BIGINT,
    new_price DECIMAL(10, 2),
    new_stock INT
);

INSERT INTO temp_updates VALUES
    (1, 69.99, 300),
    (2, 119.99, 200),
    (3, 169.99, 150);

-- 关联临时表更新
UPDATE products p
JOIN temp_updates t ON p.id = t.product_id
SET 
    p.price = t.new_price,
    p.stock = t.new_stock;

-- 删除临时表
DROP TEMPORARY TABLE temp_updates;

SELECT * FROM products;

-- 按价格排序，更新前3个产品
UPDATE products 
SET status = 'top_seller'
ORDER BY price DESC
LIMIT 3;

-- 按库存排序，更新库存最少的
UPDATE products 
SET status = 'need_restock'
ORDER BY stock ASC
LIMIT 5;

SELECT * FROM products ORDER BY stock ASC;

-- 分页更新大量数据
-- 第1页
UPDATE products 
SET status = 'processed'
WHERE status = 'pending'
ORDER BY id
LIMIT 1000;

-- 第2页 (需要记录上一页最后ID)
UPDATE products 
SET status = 'processed'
WHERE status = 'pending' AND id > 1000
ORDER BY id
LIMIT 1000;

UPDATE 性能优化建议
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   1. 使用索引条件                                           │
│   ├── WHERE 条件使用索引列                                 │
│   └── 避免全表扫描                                         │
│                                                             │
│   2. 分批更新                                               │
│   ├── 大量更新分批进行                                     │
│   └── 使用 LIMIT 控制批次大小                              │
│                                                             │
│   3. 避免长事务                                             │
│   ├── 长时间持有锁影响并发                                 │
│   └── 及时提交事务                                         │
│                                                             │
│   4. 减少锁范围                                             │
│   ├── 精确的 WHERE 条件                                    │
│   └── 避免锁定不必要的数据                                 │
│                                                             │
│   5. 非高峰期执行                                           │
│   ├── 大批量更新在低峰期执行                               │
│   └── 减少对业务的影响                                     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 查看更新执行计划
EXPLAIN UPDATE products 
SET price = 99.99 
WHERE id = 1;

-- 分析执行计划
EXPLAIN ANALYZE UPDATE products 
SET price = 99.99 
WHERE id = 1;

-- 问题: 并发更新冲突
-- 会话1
START TRANSACTION;
UPDATE products SET price = 50 WHERE id = 1;
-- 不提交

-- 会话2 (会等待锁)
UPDATE products SET price = 60 WHERE id = 1;
-- 等待会话1释放锁

-- 解决方案: 使用乐观锁
UPDATE products 
SET price = 60, version = version + 1 
WHERE id = 1 AND version = 1;

-- 检查影响行数
SELECT ROW_COUNT();
-- 如果为0，说明版本已变化，需要重试

-- 问题: 更新超时
-- Error: Lock wait timeout exceeded

-- 解决方案1: 增加超时时间
SET SESSION innodb_lock_wait_timeout = 50;

-- 解决方案2: 使用 NOWAIT
UPDATE products 
SET price = 99.99 
WHERE id = 1 
FOR UPDATE NOWAIT;

-- 解决方案3: 检查并终止长时间运行的事务
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE TIME > 10 AND STATE LIKE '%lock%';

-- 问题: 频繁更新同一行导致热点
UPDATE counters SET value = value + 1 WHERE id = 1;

-- 解决方案: 分散更新
-- 使用多个计数器
UPDATE counters SET value = value + 1 
WHERE id = (RAND() * 10 + 1);

-- 汇总时求和
SELECT SUM(value) FROM counters;