mysql - 如何使用存储过程获取MYSQL动态查询？

Question

我正在尝试使用存储过程运行动态查询，例如，我在其中发送 where 条件情况。

我的示例存储过程如下所示:

CREATE PROCEDURE `Storedproc`(IN getwhereconditon varchar(1000))
    BEGIN
    set @param=getwhereconditon ;
    SET @S=concat('Select * from table where (1)',@param);
    PREPARE stmt1 FROM @s;
    EXECUTE stmt1;
    END

这里我的存储过程的名称是 Storedproc，我在其中传递 where 条件详细信息，当我调用我的存储过程时，我收到此错误

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0tablecol=1' at line

Answer 1

尝试

DELIMITER //
CREATE PROCEDURE myproc(IN _where VARCHAR(512))
BEGIN
  SET @sql = 'SELECT * FROM table1';
  IF COALESCE(_where, '') <> '' THEN
    SET @sql = CONCAT(@sql, ' WHERE ', _where);
  END IF;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

或

DELIMITER //
CREATE PROCEDURE myproc(IN _where VARCHAR(512))
BEGIN
  SET @sql = CONCAT('SELECT * FROM table1', COALESCE(CONCAT(' WHERE ', NULLIF(_where, '')), ''));
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

使用示例:

CALL myproc(NULL);
CALL myproc('name=''John''');
CALL myproc('age > 25');
CALL myproc('age < 28 OR age > 30');

这里是SQLFiddle 演示