为什么MySQL使用参数不返回数据准备语句?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (59)

有以下代码块:

-- -----------------------------------------------------
-- 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 ;
提问于
用户回答回答于

WHERE 'somestring'和ORDER BY 'somestring'(注意引号),执行语句不会将结果集添加到过程返回的结果中;通常,执行结果最终需要是insert select到一个临时表中,该过程可以在退出之前直接从该表中选择。

扫码关注云+社区

领取腾讯云代金券