MySQL 将表名传递给游标选择

Question

我希望程序在 select 语句中采用参数 answertable 和 partid，但是当我调用它时，它不会用值替换参数 answertable

调用 call updateTotalScores('quiz_participation', 'quiz_answer', 1)

返回错误:1146 - 表“quizdb.answertable”不存在

传递 id 有效，但传递表名无效那么我如何将表名传递给 select in

DECLARE cur1 CURSOR FOR SELECT SUM(`score`), SUM(`maxscore`) FROM answertable WHERE `idParticipation`=partid;

整个过程:

DELIMITER $$
CREATE PROCEDURE updateTotalScores(IN participationtable CHAR(64), IN answertable CHAR(64), IN partid INT)
BEGIN
 DECLARE done INTEGER DEFAULT 0;
 DECLARE sscore INTEGER DEFAULT 0;
 DECLARE smaxscore INTEGER DEFAULT 0;
 DECLARE cur1 CURSOR FOR SELECT SUM(`score`), SUM(`maxscore`) FROM answertable WHERE `idParticipation`=partid;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 OPEN cur1;
 REPEAT
  FETCH cur1 INTO sscore, smaxscore;
  UNTIL done = 1
 END REPEAT;
 CLOSE cur1;

 UPDATE participationtable SET `score`=sscore, `maxscore`=smaxscore WHERE `idParticipation`=partid;
END $$
DELIMITER ;

为了完整性

表名不能传递给 MySql 游标，至少现在不能

http://forge.mysql.com/worklog/task.php?id=3433

下面的答案(更正了一点)

DELIMITER $$

CREATE PROCEDURE updateTotalScores(IN participation_table VARCHAR(45), IN answer_table VARCHAR(45), IN part_id INT)
BEGIN
    SET @stmt_text=CONCAT("SELECT @score := SUM(`score`), @maxscore := SUM(`maxscore`) FROM ",
                         answer_table, " WHERE `idParticipation`=",  part_id);
    PREPARE stmt FROM @stmt_text;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @stmt_text=CONCAT("UPDATE ", participation_table, 
                        " SET `score`=?, `maxscore`=? WHERE `idParticipation`=", part_id);
    PREPARE stmt FROM @stmt_text;
    EXECUTE stmt USING @score, @maxscore;
    DEALLOCATE PREPARE stmt;
END $$

Answer 1

我相信你不能以这种方式做到这一点。

为了实现这一点，您应该使用动态 SQL。

请注意，您也不能使用动态 SQL 打开游标。但在你的情况下，似乎不需要游标。

如果我正确理解您的代码，您可以只使用用户变量，并可能使用 2 个动态准备的语句实现您想要做的事情。

  SET @stmt_text=CONCAT("SELECT @score = SUM(`score`), @maxscore=SUM(`maxscore`) FROM ",                
                         answertable, "WHERE `idParticipation`= ",  partid);
  PREPARE stmt FROM @stmt_text;
  EXECUTE stmt USING @a;

然后使用以下语句更新值

  SET @stmt_text=CONCAT("UPDATE", participationtable, " SET `score`=@score,  
                      `maxscore`=@maxscore WHERE `idParticipation`=", partid);

  PREPARE stmt FROM @stmt_text;
  EXECUTE stmt USING @a;

  DEALLOCATE PREPARE stmt;

注意:请检查语法。我无法对其进行测试以准确验证它，但我希望你明白这一点。