如何将构建MySQL synatx的冷融合循环转换为MySQL存储过程？

Question

我正在尝试将产品搜索从Cold聚变转移到MySQL存储过程中。

搜索有大约20个标准，是它所在站点的一个普通功能，所以想将其转换为存储的proc，因为需要做大量的预编译。

我想我找出了大部分的搜索标准。我在为最后一个问题而挣扎。

第一，冷融部分：

<cfquery datasource="dtb" name="get_pricelists">
  SELECT sid, pricelist
  FROM buyerList AS b
  LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
  WHERE b.bid = >parameter<
</cfquery>

这出售所有卖家使用的价格表和相应的价格表为当前用户。如下所示(在MySQL中，我正在创建一个临时表)：

=====================
sellerID    pricelist
12345         NULL
23467         foo
99999         bar

下一部分是我挣扎的地方：

<cfset misterLister = "LEFT JOIN preislisten p ON ">
<cfoutput query="get_pricelists" >
<cfif pricelist IS ''>
     <cfset misterLister = misterLister & '(p.sid = a.sid AND p.pricelist = "BASE" AND p.ean = a.ean AND p.iln = "#sellerID#") OR '>
  <cfelse>
     <cfset misterLister = misterLister & '(p.sid = a.sid AND p.pricelist = "#pricelist#" AND p.ean = a.ean AND p.iln = "#sellerID#") OR '>
  </cfif>
</cfoutput>
<cfset misterLister = misterLister & "(1=0)">

因此，使用上面的示例，这将遍历找到的3位卖家和价格师，以便在Cold聚变中创建这个MySQL语法：

LEFT JOIN pricelists p ON
   (p.sid = a.sid AND p.pricelist = "BASE" AND p.ean = a.ean AND p.iln = 12345 ) OR 
   (p.sid = a.sid AND p.pricelist = "foo" AND p.ean = a.ean AND p.iln = 23467 ) OR 
   (p.sid = a.sid AND p.pricelist = "bar" AND p.ean = a.ean AND p.iln = 99999) OR 
   (1=0)

然后将其传递到实际查询中。

问题

我可以做第一部分，并将其存储在临时表中。但是，是否也可以在MySQL中创建第2部分，即循环遍历temp表的结果并从中构造上述语句？

我还是MySQL的新手，所以我不知道从哪里开始。我正在查看准备好的语句和游标，但这是唯一的选项吗？

编辑：

好的。我试着想出我的第一份准备好的声明。看起来是这样的：

SET @sql_text := '
DECLARE strCount   INT DEFAULT 1;

SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs
    FROM buyerList AS b
    LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
    WHERE b.bid = ?

SET @string = "LEFT JOIN preislisten AS p";

lj:
  LOOP

    SET @string = CONCAT( @string, "ON (p.iln = a.iln AND p.preisliste = sid AND p.ean = a.ean AND p.iln = pricelist ) OR");

    SET strCount = strCount+1;
    IF strCount = recs
    THEN LEAVE lj;
    END IF;

END LOOP lj;

SET @string = CONCAT( @string,"(1=0)")
';
SET @param_iln = param_iln;
PREPARE stmt FROM @sql_text;
EXECUTE stmt using @param_iln;
DEALLOCATE PREPARE stmt;

因此，我在prep语句字符串中执行inital查询，然后希望能够遍历找到的价格表(它将有3个以上的卖家和价格表，所以我需要遍历一个循环，对吗？)我把一切都放在一起。但是，如果这样做有效，我将如何将此字符串添加到实际的搜索查询中，该查询如下所示：

SELECT articles AS art 
   << insert left join here >>
    FROM bigtable AS bt
    WHERE
      a lot of other criteria

我迷路了..。

Answer 1

替换SELECT sid, pricelist

用SELECT sid, ifnull(pricelist,'BASE')

( server阅读器，mysql的ifnull()就像您的isnull()函数)

这使得价格表在空时作为“基数”出现。

然后可以跳过<cfif pricelist IS ''> + <cfelse>

如果您使用的是cf9+，也可以使用速记级联<cfset misterLister &= "value">。

通过构建动态sql并在存储过程中执行，您将获得更好的性能。减少数据库往返旅行。如果coldfusion只需要与mysql交谈一次，速度就会更快。

您不需要一个临时表来循环。只需构建要在查询中直接执行的sql字符串。然后执行sql字符串。

不要担心游标-当您想要加载一些记录、循环它们并在每一行上执行一个操作时，它们都是为您准备的。

准备发言：http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html

存储过程：http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

基本上，您需要一个存储过程来提供参数。它在内部构建一个字符串，该字符串是准备和执行的。返回结果数据。