mysql - 我正在努力准备好声明，其中

Question

...表名是参数。我在这里找到了解决方案:unable to store return value from execute statement in mysql但这对我不起作用。这是我的测试:

SET @v_sourceTableSize = 0;
SET @parameter_sourceTable = category;
SET @v_query = CONCAT("SELECT COUNT(*) INTO ",@v_sourceTableSize," FROM ",@parameter_sourceTable);

category 是我数据库中的一个表。当我运行这个时，什么也没有发生。没有错误，什么都没有。有办法让它发挥作用吗？从早上起我就一直在寻找解决方案!

编辑
我将“类别”放在引号之间，现在它可以正常工作，但是我原来的程序却不能。请看一下:

DROP PROCEDURE IF EXISTS insert_categories;
DELIMITER $$
CREATE PROCEDURE insert_categories(
    IN parameter_sourceTableIdColumnName INTEGER,
    IN parameter_sourceTable VARCHAR(25),
    IN parameter_sourceColumn VARCHAR(25),
    IN parameter_destinationParentCategoryID INTEGER)

    MODIFIES SQL DATA

    BEGIN
        DECLARE v_parameterSourceTable VARCHAR(25) DEFAULT "";
        DECLARE v_sourceTableSize INTEGER;
        DECLARE v_counter INTEGER DEFAULT 0;
        DECLARE v_query VARCHAR(100) DEFAULT "";
        DECLARE v_recordName VARCHAR(25) DEFAULT "";
        SET v_parameterSourceTable = parameter_sourceTable;
        SET v_query = CONCAT("SELECT COUNT(*) INTO ",v_sourceTableSize," FROM ",v_parameterSourceTable);
        PREPARE stmt FROM v_query;
        EXECUTE stmt;
        SELECT v_sourceTableSize;
        #SET v_sourceTableSize := (SELECT COUNT(*) FROM parameter_sourceTable);
        SET v_sourceTableSize = v_sourceTableSize + 1;
        WHILE v_counter < v_sourceTableSize DO
            SET v_counter = v_counter + 1;
            SET v_recordName = (SELECT parameter_sourceColumn 
                                FROM v_parameterSourceTable 
                                WHERE parameter_sourceTableIdColumnName = v_counter);
            CALL insertCategory(v_recordName, parameter_destinationParentCategoryID);
        END WHILE;
    END $$    
DELIMITER ;

我收到此错误:

1064 - 您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，了解在 'v_query; 附近使用的正确语法。 执行命令； 选择 v_sourceTableSize; 在第 17 行设置 v_sourceTa'

Answer 1

A prepared statement can access user-defined variables, but not local variables or procedure's parameters.

    SET @v_query = CONCAT("SELECT COUNT(*) INTO ",v_sourceTableSize," FROM ",v_parameterSourceTable);
    PREPARE stmt FROM @v_query;
    EXECUTE stmt;

Docs