在 MySQL 中循环结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1745165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Looping Over Result Sets in MySQL
提问by Dereleased
I am trying to write a stored procedure in MySQL which will perform a somewhat simple select query, and then loop over the results in order to decide whether to perform additional queries, data transformations, or discard the data altogether. Effectively, I want to implement this:
我正在尝试在 MySQL 中编写一个存储过程，它将执行一个稍微简单的选择查询，然后遍历结果以确定是否执行额外的查询、数据转换或完全丢弃数据。实际上，我想实现这一点：
$result = mysql_query("SELECT something FROM somewhere WHERE some stuff");
while ($row = mysql_fetch_assoc($result)) {
// check values of certain fields, decide to perform more queries, or not
// tack it all into the returning result set
}
Only, I want it only in MySQL, so it can be called as a procedure. I know that for triggers, there is the FOR EACH ROW ...syntax, but I can't find mention of anything like this for use outside of the CREATE TRIGGER ...syntax. I have read through some of the looping mechanisms in MySQL, but so far all I can imagine is that I would be implementing something like this:
只是，我只想要它在 MySQL 中，所以它可以作为一个过程调用。我知道对于触发器，有FOR EACH ROW ...语法，但我找不到在CREATE TRIGGER ...语法之外使用类似的任何内容。我已经阅读了 MySQL 中的一些循环机制，但到目前为止我能想象的是我会实现这样的东西：
SET @S = 1;
LOOP
SELECT * FROM somewhere WHERE some_conditions LIMIT @S, 1
-- IF NO RESULTS THEN
LEAVE
-- DO SOMETHING
SET @S = @S + 1;
END LOOP
Although even this is somewhat hazy in my mind.
虽然这在我的脑海中也有些朦胧。
For reference, though I don't think it's necessarily relevant, the initial query will be joining four tables together to form a model of hierarchal permissions, and then based on how high up the chain a specific permission is, it will retrieve additional information about the children to which that permission should be inherited.
作为参考，虽然我认为它不一定相关，但最初的查询将把四个表连接在一起形成一个分层权限模型，然后根据特定权限在链上的高度，它会检索关于应继承该权限的子代。
回答by mjv
Something like this should do the trick (However, read after the snippet for more info)
像这样的事情应该可以解决问题（但是，请阅读代码段以获取更多信息）
CREATE PROCEDURE GetFilteredData()
BEGIN
DECLARE bDone INT;
DECLARE var1 CHAR(16); -- or approriate type
DECLARE Var2 INT;
DECLARE Var3 VARCHAR(50);
DECLARE curs CURSOR FOR SELECT something FROM somewhere WHERE some stuff;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
DROP TEMPORARY TABLE IF EXISTS tblResults;
CREATE TEMPORARY TABLE IF NOT EXISTS tblResults (
--Fld1 type,
--Fld2 type,
--...
);
OPEN curs;
SET bDone = 0;
REPEAT
FETCH curs INTO var1,, b;
IF whatever_filtering_desired
-- here for whatever_transformation_may_be_desired
INSERT INTO tblResults VALUES (var1, var2, var3 ...);
END IF;
UNTIL bDone END REPEAT;
CLOSE curs;
SELECT * FROM tblResults;
END
A few things to consider...
需要考虑的几件事...
Concerning the snippet above:
关于上面的片段：
- may want to pass part of the query to the Stored Procedure, maybe particularly the search criteria, to make it more generic.
- If this method is to be called by multiple sessions etc. may want to pass a Session ID of sort to create a unique temporary table name (actually unnecessary concern since different sessions do not share the same temporary file namespace; see comment by Gruber, below)
- A few parts such as the variable declarations, the SELECT query etc. need to be properly specified
- 可能希望将部分查询传递给存储过程，尤其是搜索条件，以使其更通用。
- 如果此方法要由多个会话等调用，则可能希望传递排序的会话 ID 以创建唯一的临时表名称（实际上是不必要的关注，因为不同的会话不共享相同的临时文件命名空间；请参阅下面的 Gruber 评论)
- 需要正确指定一些部分，例如变量声明、SELECT 查询等
More generally: trying to avoid needing a cursor.
更一般地说：试图避免需要一个 cursor。
I purposely named the cursor variable curs[e], because cursors are a mixed blessing. They can help us implement complicated business rules that may be difficult to express in the declarative form of SQL, but it then brings us to use the procedural (imperative) form of SQL, which is a general feature of SQL which is neither very friendly/expressive, programming-wise, and often less efficient performance-wise.
我特意将游标变量命名为curs[e]，因为游标是喜忧参半。它们可以帮助我们实现复杂的业务规则，这些规则可能很难用 SQL 的声明形式表达，但它又让我们使用 SQL 的过程（命令式）形式，这是 SQL 的一个普遍特征，既不太友好/富有表现力，编程方面，但在性能方面通常效率较低。
Maybe you can look into expressing the transformation and filtering desired in the context of a "plain" (declarative) SQL query.
也许您可以研究在“普通”（声明性）SQL 查询的上下文中表达所需的转换和过滤。
回答by AlishahNovin
Use cursors.
使用游标。
A cursor can be thought of like a buffered reader, when reading through a document. If you think of each row as a line in a document, then you would read the next line, perform your operations, and then advance the cursor.
在阅读文档时，游标可以被认为是一个缓冲阅读器。如果您将每一行视为文档中的一行，那么您将阅读下一行，执行您的操作，然后移动光标。
