mysql+存储过程+问题_存储过程 – MySQL存储过程导致问题？

编辑：

我把我的mysql等待超时缩小到这一行：

IF @resultsFound > 0 THEN

INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId);

END IF;

知道为什么会导致问题吗？我无法解决这个问题！

嗨伙计们,我写了一个存储过程来搜索某些类别的产品,由于我遇到的某些限制,我无法做我想要的(限制,但仍然返回找到的总行数,排序等.)

这意味着将1,2,3 in的类别ID拆分为临时表,然后根据排序选项和限制构建全文搜索查询,执行查询字符串,然后选择结果总数.

现在,我知道我不是MySQL大师,离它很远,我已经有了它的工作,但我一直在等待产品搜索等等.所以我认为这可能会导致某种问题？

有没有人有任何想法如何我可以整理它,甚至以一种我可能不知道的更好的方式做到这一点？

谢谢..

DELIMITER $$

DROP PROCEDURE IF EXISTS `product_search` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `product_search`(keywords text, categories text, topLevelCategoryId int, sortOrder int, startOffset int, itemsToReturn int)

BEGIN

declare foundPos tinyint unsigned;

declare tmpTxt text;

declare delimLen tinyint unsigned;

declare element text;

declare resultingNum int unsigned;

drop temporary table if exists categoryIds;

create temporary table categoryIds

(

`CategoryId` int

) engine = memory;

set tmpTxt = categories;

set foundPos = instr(tmpTxt, ',');

while foundPos <> 0 do

set element = substring(tmpTxt, 1, foundPos-1);

set tmpTxt = substring(tmpTxt, foundPos+1);

set resultingNum = cast(trim(element) as unsigned);

insert into categoryIds (`CategoryId`) values (resultingNum);

set foundPos = instr(tmpTxt,',');

end while;

if tmpTxt <> '' then

insert into categoryIds (`CategoryId`) values (tmpTxt);

end if;

CASE

WHEN sortOrder = 0 THEN

SET @sortString = "ProductResult_Relevance DESC";

WHEN sortOrder = 1 THEN

SET @sortString = "ProductResult_Price ASC";

WHEN sortOrder = 2 THEN

SET @sortString = "ProductResult_Price DESC";

WHEN sortOrder = 3 THEN

SET @sortString = "ProductResult_StockStatus ASC";

END CASE;

SET @theSelect = CONCAT(CONCAT("

SELECT SQL_CALC_FOUND_ROWS

supplier.SupplierId as Supplier_SupplierId,

supplier.Name as Supplier_Name,

supplier.ImageName as Supplier_ImageName,

product_result.ProductId as ProductResult_ProductId,

product_result.SupplierId as ProductResult_SupplierId,

product_result.Name as ProductResult_Name,

product_result.Description as ProductResult_Description,

product_result.ThumbnailUrl as ProductResult_ThumbnailUrl,

product_result.Price as ProductResult_Price,

product_result.DeliveryPrice as ProductResult_DeliveryPrice,

product_result.StockStatus as ProductResult_StockStatus,

product_result.TrackUrl as ProductResult_TrackUrl,

product_result.LastUpdated as ProductResult_LastUpdated,

MATCH(product_result.Name) AGAINST(?) AS ProductResult_Relevance

FROM

product_latest_state product_result

JOIN

supplier ON product_result.SupplierId = supplier.SupplierId

JOIN

category_product ON product_result.ProductId = category_product.ProductId

WHERE

MATCH(product_result.Name) AGAINST (?)

AND

category_product.CategoryId IN (select CategoryId from categoryIds)

ORDER BY

", @sortString), "

LIMIT ?, ?;

");

set @keywords = keywords;

set @startOffset = startOffset;

set @itemsToReturn = itemsToReturn;

PREPARE TheSelect FROM @theSelect;

EXECUTE TheSelect USING @keywords, @keywords, @startOffset, @itemsToReturn;

SET @resultsFound = FOUND_ROWS();

SELECT @resultsFound as 'TotalResults';

IF @resultsFound > 0 THEN

INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId);

END IF;

END $$

DELIMITER ;

非常感谢任何帮助！