在 MySQL 中循环结果集

2021-12-08 00:00:00 loops mysql stored-procedures cursor

我正在尝试在 MySQL 中编写一个存储过程，该过程将执行一个稍微简单的选择查询，然后遍历结果以确定是否执行其他查询、数据转换或完全丢弃数据.实际上，我想实现这一点:

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
}

只是，我只想要它在MySQL中，所以它可以作为一个过程调用.我知道对于触发器，有 FOR EACH ROW ... 语法，但我找不到在 CREATE TRIGGER ... 之外使用的类似内容的提及代码> 语法.我已经阅读了 MySQL 中的一些循环机制，但到目前为止我能想象的是我会实现这样的东西:

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

需要考虑的一些事项...

关于上面的片段:

  • 可能希望将部分查询传递给存储过程，尤其是搜索条件，以使其更通用.
  • 如果这个方法要被多个会话调用等，可能需要传递一个排序的会话 ID 来创建一个唯一的临时表名(实际上没有必要担心，因为不同的会话不共享相同的临时文件命名空间；请参阅评论格鲁伯，下面)
  • 需要正确指定一些部分，例如变量声明、SELECT 查询等

更一般地说:尽量避免需要光标.

我特意将游标变量命名为curs[e]，因为游标是喜忧参半.它们可以帮助我们实现复杂的业务规则，这些规则可能很难用 SQL 的声明形式表达，但它又让我们使用 SQL 的过程(命令式)形式，这是 SQL 的一个普遍特性，既不太友好/富有表现力、编程方面，但在性能方面通常效率较低.

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.

也许您可以研究在普通"(声明性)SQL 查询的上下文中表达所需的转换和过滤.

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

相关文章