<tfoot id='Unipr'></tfoot>

      <bdo id='Unipr'></bdo><ul id='Unipr'></ul>
      1. <legend id='Unipr'><style id='Unipr'><dir id='Unipr'><q id='Unipr'></q></dir></style></legend>

        <i id='Unipr'><tr id='Unipr'><dt id='Unipr'><q id='Unipr'><span id='Unipr'><b id='Unipr'><form id='Unipr'><ins id='Unipr'></ins><ul id='Unipr'></ul><sub id='Unipr'></sub></form><legend id='Unipr'></legend><bdo id='Unipr'><pre id='Unipr'><center id='Unipr'></center></pre></bdo></b><th id='Unipr'></th></span></q></dt></tr></i><div id='Unipr'><tfoot id='Unipr'></tfoot><dl id='Unipr'><fieldset id='Unipr'></fieldset></dl></div>

        <small id='Unipr'></small><noframes id='Unipr'>

      2. 在 MySQL 中循环结果集

        时间:2023-10-10
        <tfoot id='AMbhJ'></tfoot>
          <bdo id='AMbhJ'></bdo><ul id='AMbhJ'></ul>

            <small id='AMbhJ'></small><noframes id='AMbhJ'>

                <i id='AMbhJ'><tr id='AMbhJ'><dt id='AMbhJ'><q id='AMbhJ'><span id='AMbhJ'><b id='AMbhJ'><form id='AMbhJ'><ins id='AMbhJ'></ins><ul id='AMbhJ'></ul><sub id='AMbhJ'></sub></form><legend id='AMbhJ'></legend><bdo id='AMbhJ'><pre id='AMbhJ'><center id='AMbhJ'></center></pre></bdo></b><th id='AMbhJ'></th></span></q></dt></tr></i><div id='AMbhJ'><tfoot id='AMbhJ'></tfoot><dl id='AMbhJ'><fieldset id='AMbhJ'></fieldset></dl></div>

                  <tbody id='AMbhJ'></tbody>

                • <legend id='AMbhJ'><style id='AMbhJ'><dir id='AMbhJ'><q id='AMbhJ'></q></dir></style></legend>
                  本文介绍了在 MySQL 中循环结果集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我正在尝试在 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.

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

                  上一篇:在 EF4 中使用 NOLOCK 提示? 下一篇:在 SQL Server 2008 中执行 while 循环

                  相关文章

                • <legend id='86ntL'><style id='86ntL'><dir id='86ntL'><q id='86ntL'></q></dir></style></legend>

                  1. <tfoot id='86ntL'></tfoot>

                    <small id='86ntL'></small><noframes id='86ntL'>

                  2. <i id='86ntL'><tr id='86ntL'><dt id='86ntL'><q id='86ntL'><span id='86ntL'><b id='86ntL'><form id='86ntL'><ins id='86ntL'></ins><ul id='86ntL'></ul><sub id='86ntL'></sub></form><legend id='86ntL'></legend><bdo id='86ntL'><pre id='86ntL'><center id='86ntL'></center></pre></bdo></b><th id='86ntL'></th></span></q></dt></tr></i><div id='86ntL'><tfoot id='86ntL'></tfoot><dl id='86ntL'><fieldset id='86ntL'></fieldset></dl></div>
                    • <bdo id='86ntL'></bdo><ul id='86ntL'></ul>