在MySQL中循环结果集 [英] Looping Over Result Sets in MySQL

问题描述

我试图在MySQL中编写一个存储过程，它将执行一些简单的选择查询，然后循环结果，以决定是否执行额外的查询，数据转换或完全丢弃数据。实际上，我想实现这个：

  $ result = mysql_query（SELECT something FROM wherewhere where WHERE 
 while（$ row = mysql_fetch_assoc（$ result））{
 //检查某些字段的值，决定执行更多的查询，还是不执行
 //将它全部粘贴到返回的结果集中
} 
  

只有在MySQL中才需要它，程序。我知道触发器，有 FOR EACH ROW ... 语法，但我不能找到提及任何类似的使用 CREATE TRIGGER ... 语法。我已经阅读了MySQL中的一些循环机制，但到目前为止我可以想象是，我将实现这样的：

  SET @S = 1; 
 LOOP 
 SELECT * FROM where where WHERE some_conditions LIMIT @S，1 
  - 如果没有结果，
 LEAVE 
  -  DO SOMETHING 
 SET @S = @S + 1; 
 END LOOP 
  

虽然这在我心里有些模糊。

为了参考，虽然我不认为它是必然相关的，初始查询将连接四个表一起形成层次权限的模型，然后基于如何高级链接一个特定的权限是，它将检索有关该权限应该继承的子级的附加信息。

解决方案

这样的东西应该做的窍门（但是，更多信息后阅读代码片段）

  CREATE PROCEDURE GetFilteredData（）
 BEGIN 
 DECLARE bDone INT; 
 
 DECLARE var1 CHAR（16）; - 或approriate类型
 DECLARE Var2 INT; 
 DECLARE Var3 VARCHAR（50）; 
 
 DECLARE curs CURSOR FOR SELECT东西从某处WHERE一些东西; 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1; 
 
 DROP TEMPORARY TABLE IF EXISTS tblResults; 
如果不存在，则CREATE TEMPORARY TABLE tblResults（
 --Fld1 type，
 --Fld2 type，
 --... 
）; 
 
 OPEN curs; 
 
 SET bDone = 0; 
 REPEAT 
 FETCH curs INTO var1 ,, b; 
 
 if whatever_filtering_desired 
  - 这里为whatever_transformation_may_be_desired 
 INSERT INTO tblResults VALUES（var1，var2，var3 ...）; 
 END IF; 
 UNTIL bDone END REPEAT; 
 
关闭curs; 
 SELECT * FROM tblResults; 
 END 
  

需要考虑的几件事情 / p>

对于上述代码段：

• 可能需要将部分查询


• 如果此方法要由多个会话调用等，则可能需要传递一个排序的会话ID来创建一个唯一的临时表名（实际上不必要的关注，因为不同的会话不共享同一个临时文件命名空间;参见Gruber的注释，下面）


• 几个部分，如变量声明，SELECT查询等需要正确指定
。

更一般地说：试图避免需要光标 p>

我有意地命名游标变量curs [e]，因为游标是一个混合的祝福。它们可以帮助我们实现可能难以用SQL的声明性形式表达的复杂业务规则，但是它使我们使用SQL的过程性（命令式）形式，这是SQL的一个通用特性，它既不是非常友好/

也许你可以在plain（声明式）的上下文中表达所需的转换和过滤， ）SQL查询。

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:

$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:

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.

解决方案

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

More generally: trying to avoid needing a 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.

Maybe you can look into expressing the transformation and filtering desired in the context of a "plain" (declarative) SQL query.

这篇关于在MySQL中循环结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！