那些年我们忽视的 SQL 错误，及如何写出高效易维护代码

    -- 不建议的写法
    SELECT * FROM users WHERE status = 'active';

    -- 推荐写法
    SELECT id, username, email FROM users WHERE status = 'active';

为避免误用，团队应引入代码审查工具或 SQL Linter，自动检测并提示 SELECT \*。

    SELECT * FROM orders WHERE customer_id = 1234 ORDER BY order_date DESC LIMIT 10;

    -- 创建合适索引
    CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);

    WITH recent_orders AS (
      SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
    )
    SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;

    SELECT * FROM orders JOIN customers;
    -- 没有 ON 条件，所有订单和所有客户两两配对，结果集暴增

    SELECT o.id, c.name FROM orders o
    JOIN customers c ON o.customer_id = c.id;

    SELECT name FROM users WHERE id IN (
      SELECT user_id FROM purchases WHERE amount > 100
    );

    SELECT u.name FROM users u
    JOIN purchases p ON u.id = p.user_id
    WHERE p.amount > 100;

    WITH big_purchases AS (
      SELECT DISTINCT user_id FROM purchases WHERE amount > 100
    )
    SELECT u.name FROM users u
    JOIN big_purchases bp ON u.id = bp.user_id;

    DECLARE user_cursor CURSOR FOR SELECT id FROM users;
    OPEN user_cursor;
    FETCH NEXT FROM user_cursor INTO @user_id;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      -- 逐行操作，性能差，代码复杂
      FETCH NEXT FROM user_cursor INTO @user_id;
    END
    CLOSE user_cursor;
    DEALLOCATE user_cursor;

    WITH ranked_orders AS (
      SELECT user_id, order_id,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
      FROM orders
    )
    SELECT * FROM ranked_orders WHERE rn = 1;

    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    -- 若第二条失败，资金流失

    BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;

    EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;