在从中获取数据的循环中更新游标的查询表是否合法 [英] Is it legal to update cursor's querying table in the loop of fetching data from it

问题描述

例如，可以在下面做吗？

For example , is it ok to do below ?

DECLARE aId VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT  id FROM new_records WHERE is_loaded = false;
read_loop: LOOP

    FETCH cur1 INTO aId;
    ...
    update new_records set is_loaded = True where id = aId ; 
    ...
    CLOSE cur1;
END 

推荐答案

MySQL中的游标是敏感的（ 13.6.6光标）。

Cursors in MySQL are ASENSITIVE (13.6.6 Cursors).

不敏感游标是一种游标，它有效地导致要创建其结果表的单独
副本；游标会访问该副本，而不是原始结果
，因此此游标将看不到其他方法对原始
结果所做的任何更改。敏感的
游标是直接在其结果表上工作的游标：它不会复制
，因此对该结果表
可见对结果表所做的其他更改。敏感游标可能会也可能不会复制其
结果表；其结果表的其他更改是否将显示
是实现定义的。默认值为ASENSITIVE
光标。

An INSENSITIVE Cursor is a Cursor that effectively causes a separate copy of its result Table to be created; the Cursor accesses that copy, rather than the original result, so any changes made to the original result by other methods won't be visible to this Cursor. A SENSITIVE Cursor is a Cursor that works directly on its result Table: it makes no copy, so other changes made to the result Table will be visible to this Cursor. An ASENSITIVE Cursor may or may not make a copy of its result Table; whether other changes to its result Table will be visible is implementation-defined. The default is an ASENSITIVE Cursor.

-来自 DECLARE CURSOR语句-

SQL Fiddle演示

但是，根据您需要执行的操作，还有其他方法可以更新表。

However, depending on what you need to do, there are other ways to update the table.

这篇关于在从中获取数据的循环中更新游标的查询表是否合法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！