存储过程中的 MySQL 准备语句未按预期运行

Question

我有这个 MySQL 存储过程

DELIMITER $$

CREATE DEFINER=`dbuser`@`%` PROCEDURE `getTranslatedAnswer`(IN questionDesc VARCHAR(2500), col VARCHAR(10))
BEGIN
    SET @paramVal = questionDesc;
    SET @str = CONCAT('SELECT C.',col,' AS `answer`, D.',col,' AS `message` 
        FROM `option_group` A 
            INNER JOIN `questions_answers` B ON A.`option_id` = B.`option_id` 
            INNER JOIN `answers` C ON B.`answers_id` = C.`answers_id` 
            INNER JOIN  `chat_message` D ON A.`option_group_id` = D.`option_group_id` 
        WHERE UPPER(`A`.',col,') = ? LIMIT 1');
    PREPARE stmt FROM @str;
    EXECUTE stmt USING @paramVal;
    DEALLOCATE PREPARE stmt;
END

上面的代码调用时使用

call getTranslatedAnswer('我可以在机场买到预付SIM卡吗？','zh_chs');

将返回 0 行。与我调用下面的裸 SQL 时相比

SELECT C.zh_chs AS `answer`, D.zh_chs AS `message` 
        FROM `option_group` A 
            INNER JOIN `questions_answers` B ON A.`option_id` = B.`option_id` 
            INNER JOIN `answers` C ON B.`answers_id` = C.`answers_id` 
            INNER JOIN  `chat_message` D ON A.`option_group_id` = D.`option_group_id` 
        WHERE UPPER(`A`.`zh_chs`) = '我可以在机场买到预付SIM卡吗？' LIMIT 1;

它返回 1 行。我在这里错过了什么吗？我该如何调试这个？请指教，谢谢!

Answer 1

我可能记错了，但我似乎记得 execute 实际上并没有像您期望的那样返回结果。如果你尝试这样的事情会发生什么？

DELIMITER $$

CREATE DEFINER=`dbuser`@`%` PROCEDURE `getTranslatedAnswer`(IN questionDesc VARCHAR(2500), col VARCHAR(10))
BEGIN
    SET @paramVal = questionDesc;
    DROP TEMPORARY TABLE IF EXISTS `t_getTranslatedAnswer`;
    SET @str = CONCAT('
        CREATE TEMPORARY TABLE `t_getTranslatedAnswer`
        SELECT C.',col,' AS `answer`, D.',col,' AS `message` 
        FROM `option_group` A 
            INNER JOIN `questions_answers` B ON A.`option_id` = B.`option_id` 
            INNER JOIN `answers` C ON B.`answers_id` = C.`answers_id` 
            INNER JOIN  `chat_message` D ON A.`option_group_id` = D.`option_group_id` 
        WHERE UPPER(`A`.',col,') = ? LIMIT 1');
    PREPARE stmt FROM @str;
    EXECUTE stmt USING @paramVal;
    DEALLOCATE PREPARE stmt;

    SELECT * FROM `t_getTranslatedAnswer`;
    DROP TEMPORARY TABLE `t_getTranslatedAnswer`;
END