MySQL - 游标

什么是游标？

游标是一个数据库对象，它允许你一次处理 SELECT 语句的结果集中的一行。它就像一个指针，按顺序遍历记录。游标通常在存储过程、函数或触发器内部使用。

在 MySQL 中，游标具有以下特点：

只读： 你不能通过标准 MySQL 游标更新或删除记录。
不可滚动： 你只能在结果集中向前移动，一次一行。你不能向后或跳转到特定行。
不敏感： 游标是“不敏感的”（asensitive），这意味着它们操作的是游标打开时的数据快照。其他连接对基本表所做的更改可能对该游标不可见。

何时使用（更重要的是，何时避免使用）游标

黄金法则： SQL 是为基于集合的操作设计的。在诉诸游标之前，你应始终尝试用单个 SQL 语句（UPDATE、INSERT ... SELECT 等）解决问题。游标是过程性的（逐行处理），通常比基于集合的操作慢得多。

何时避免使用游标：

数据转换/复制： 切勿使用游标将数据从一个表复制到另一个表。一个简单的 INSERT INTO ... SELECT ... 会快上几个数量级。
简单更新： 如果你可以使用单个 UPDATE ... WHERE ... 语句更新多行，请勿使用游标循环处理它们。

游标的合法（但罕见）用例：

复杂序列逻辑： 当你需要对每一行执行复杂的多步计算，并且该计算依赖于前一行的结果时（例如，复杂的累计总计、动态模拟）。
调用外部过程： 当你需要遍历行并为每一行调用另一个存储过程或外部进程时。

游标生命周期：声明、打开、取回、关闭

管理游标涉及四个主要步骤：

DECLARE（声明）： 定义游标并将其与 SELECT 语句关联。这必须在变量声明之后完成。
OPEN（打开）： 执行 SELECT 语句并为游标填充结果集以供迭代。
FETCH（取回）： 从结果集中检索下一行并推进游标的指针。这些值通常存储在局部变量中。
CLOSE（关闭）： 停用游标并释放其使用的内存。关闭游标对于避免资源泄漏至关重要。

一个修正后的游标示例：计算累计总计

让我们看一个可能考虑使用游标的场景：计算薪资的累计总计并存储它。（注意：现代 MySQL 版本也可以使用像 SUM() OVER (...) 这样的窗口函数来实现，这种方法效率更高。此示例仅用于演示游标机制。）

-- 设置示例表
CREATE TABLE Employees (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    SALARY DECIMAL(10, 2) NOT NULL
);
INSERT INTO Employees(NAME, SALARY) VALUES
('Alice', 50000), ('Bob', 60000), ('Charlie', 55000);

-- 创建一个表来存储结果
CREATE TABLE SalaryRunningTotals (
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2),
    running_total DECIMAL(12, 2)
);

-- 包含游标的存储过程
DELIMITER //

CREATE PROCEDURE CalculateRunningTotal()
BEGIN
    -- 1. 变量声明
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_salary DECIMAL(10, 2);
    DECLARE current_total DECIMAL(12, 2) DEFAULT 0.00;

    -- 2. 游标声明
    DECLARE cur CURSOR FOR
        SELECT NAME, SALARY FROM Employees ORDER BY ID;

    -- 3. 当未找到更多行时的处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 4. 打开游标
    OPEN cur;

    -- 5. 遍历行
    read_loop: LOOP
        -- 取回下一行
        FETCH cur INTO emp_name, emp_salary;

        -- 重要：在处理之前检查循环是否应该退出
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 处理行
        SET current_total = current_total + emp_salary;
        INSERT INTO SalaryRunningTotals(employee_name, salary, running_total)
        VALUES (emp_name, emp_salary, current_total);

    END LOOP read_loop;

    -- 6. 关闭游标
    CLOSE cur;
END //

DELIMITER ;

执行与输出

调用过程，然后检查结果表。

CALL CalculateRunningTotal();
SELECT * FROM SalaryRunningTotals;

员工姓名	薪资	累计总计
Alice	50000.00	50000.00
Bob	60000.00	110000.00
Charlie	55000.00	165000.00

反模式：为什么不应该使用游标复制数据

一个常见的错误是使用游标复制数据。原始教程展示了这一点。让我们看看为什么它是错误的，以及如何正确地进行操作。

-- 错误的方法（使用游标）
-- 这涉及到打开一个游标，遍历每一行，
-- 将其取回变量中，然后插入。它既慢又复杂。

-- 正确的方法（基于集合）
CREATE TABLE CUSTOMERS_BACKUP LIKE CUSTOMERS;
INSERT INTO CUSTOMERS_BACKUP SELECT * FROM CUSTOMERS;

基于集合的 INSERT ... SELECT 语句是一个单一的、高度优化的操作。它将以巨大的优势超越基于游标的方法，特别是在大型表上。

常见陷阱与调试

无限循环： 忘记声明 NOT FOUND 处理程序是导致无限循环的常见原因。
差一错误： LEAVE 语句必须紧跟在 FETCH 之后。如果你在检查 done 标志之前处理数据，你将会处理最后一行两次。
忘记 CLOSE： 务必关闭你的游标以释放服务器资源。打开的游标会持有锁并消耗内存。
性能盲点： 假设游标是必需的。始终问自己：“我可以用一个 SQL 语句完成这个操作吗？” 使用 EXPLAIN 来分析查询性能。