带有不返回数据的参数的MySQL预准备语句
带有不返回数据的参数的MySQL预准备语句
提问于 2018-06-16 03:54:05
我一直在尝试使参数化的预准备语句工作。第一个函数只显示带有列名的标题行，不显示任何daya。第二个函数提供所有请求的数据。不同之处在于，第一个使用参数，而第二个只使用连接字符串。第一个函数中的select语句用于调试目的，任何变量中都没有空字符串。
我想让参数化版本正常工作，如果有任何帮助我将不胜感激。
我已经检查了这些堆栈溢出的答案，Multiple Parameters，mysql Prepare Statement，Internals of prepared statement，unable to create prepared statements (可能是我的问题的答案)。
-- -----------------------------------------------------
-- procedure getAllBookDataWhere2
-- -----------------------------------------------------
USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`getAllBookDataWhere2`;
DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `getAllBookDataWhere2`
(
IN whereStr VARCHAR(256)
)
BEGIN
DECLARE finalQuery VARCHAR(4096);
DECLARE selectedFields, leftJoinTables, joinOnFields VARCHAR(1024);
DECLARE whereClause, orderByClause VARCHAR(256);
SET @selectedFields = allBooksSelectFields();
SET @jointTables4Query = allBooksDataTables();
-- orderBy may become a parameter in the future.
SET @orderByClause = ' a.LastName, a.FirstName, s.SeriesName, v.VolumeNumber, t.TitleStr';
SET @whereclause = whereStr;
-- @selectedFields and @jointTables4Query are concatenated because they don't change,
-- @whereClause and @orderByClause can change and therefore are parameters.
SELECT @orderByClause;
SELECT @whereClause;
SET @finalQuery = CONCAT('SELECT ', @selectedFields);
SET @finalQuery = CONCAT(@finalQuery, ' FROM bookinfo AS BKI ');
SET @finalQuery = CONCAT(@finalQuery, @jointTables4Query);
SET @finalQuery = CONCAT(@finalQuery, ' WHERE ? ORDER BY ? ;');
SELECT @finalQuery;
PREPARE stmt FROM @finalQuery;
EXECUTE stmt USING @whereClause, @orderByClause;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------
-- procedure getAllBookDataWhere
-- -----------------------------------------------------
USE `booklibinventory`;
DROP procedure IF EXISTS `booklibinventory`.`getAllBookDataWhere`;
DELIMITER $$
USE `booklibinventory`$$
CREATE PROCEDURE `getAllBookDataWhere`
(
IN whereStr VARCHAR(256)
)
BEGIN
DECLARE finalQuery VARCHAR(4096);
DECLARE selectedFields, leftJoinTables, joinOnFields VARCHAR(1024);
DECLARE whereClause, orderByClause VARCHAR(256);
SET @selectedFields = allBooksSelectFields();
SET @jointTables4Query = allBooksDataTables();
-- orderBy may become a parameter in the future.
SET @orderByClause = ' ORDER BY a.LastName, a.FirstName, s.SeriesName, v.VolumeNumber, t.TitleStr;';
SET @whereclause = CONCAT(' WHERE ', whereStr);
-- @selectedFields and @jointTables4Query are concatenated because they don't change,
-- @whereClause and @orderByClause can change and therefore are parameters.
SET @finalQuery = CONCAT('SELECT ', @selectedFields);
SET @finalQuery = CONCAT(@finalQuery, ' FROM bookinfo AS BKI ');
SET @finalQuery = CONCAT(@finalQuery, @jointTables4Query);
SET @finalQuery = CONCAT(@finalQuery, @whereClause);
SET @finalQuery = CONCAT(@finalQuery, @orderByClause);
PREPARE stmt FROM @finalQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50881911
复制相关文章
相似问题