代码之家 › 专栏 › 技术社区 › Pratik Thube

MySql过程无法正确筛选字符串输入的查询结果

stored-procedures mysql

Pratik Thube · 技术社区 · 7 年前

我在MySql数据库中获得了以下内容,它可以很好地用于前三个查询。但userId过滤器不起作用,它应该返回具有匹配userId的条目,但它正在返回该表中的所有记录。这在两个查询(详细信息查询和detailstwo表)中都会发生。

DELIMITER $$

DROP PROCEDURE IF EXISTS `sale`.`detailsProcedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `detailsProcedure`(IN userId VARCHAR(100))
BEGIN
  SELECT * FROM products WHERE ProductType=1 AND Price<=400;
  SELECT * FROM products WHERE ProductType=1 AND Price<=700;
  SELECT * FROM products WHERE ProductType=2;
  SELECT * FROM products WHERE ProductType=3;
  SELECT * FROM details WHERE UserId=userId;
  SELECT * FROM detailsTwo WHERE UserId=userId;
END $$

DELIMITER

;

注意:我正在我的节点中调用此过程。js restapi

提前感谢!

1 回复 | 直到 7 年前

Jacques Amar 7 年前

试试这个:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sale`.`detailsProcedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `detailsProcedure`(IN v_userId VARCHAR(100))
BEGIN
  SELECT * FROM products WHERE ProductType=1 AND Price<=400;
  SELECT * FROM products WHERE ProductType=1 AND Price<=700;
  SELECT * FROM products WHERE ProductType=2;
  SELECT * FROM products WHERE ProductType=3;
  SELECT * FROM details WHERE UserId=v_userId;
  SELECT * FROM detailsTwo WHERE UserId=v_userId;
END $$

DELIMITER ;