mysql - 如何在过程 MYSQL 中声明变量

Question

我正在尝试在我的 MySQL 中执行一个简单的过程:

DELIMITER $$

CREATE PROCEDURE `countRows`(IN v varchar(30))
    BEGIN
    DECLARE e INT DEFAULT 0;
    SET @t1 =CONCAT("SELECT COUNT(*) FROM ",V, " INTO",e);
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
    SELECT CONCAT("The ",V," table contains ",e," rows");
   END$$
DELIMITER ;

编辑:一切正常，但@t1 不会将值保存在变量中，因此默认情况下第二个查询始终显示 0。

我看不到任何语法错误，请提供一些使这项工作正常进行的建议？

Answer 1

对于@t2中的语句；不需要准备好的语句，因为它不包含任何要执行的 SQL 语句。下面提到的 3 行代码是不必要的。

PREPARE stmt3 FROM @t2;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;

你可以简单地显示它

SET @t2 =CONCAT("The ",V," table contains ",e," rows");
SELECT @t2;

(或)只是

SELECT CONCAT("The ",V," table contains ",e," rows");

这样，你的程序应该是这样的

DELIMITER $$
CREATE PROCEDURE `countRows`(IN v varchar(30))
BEGIN
DECLARE e INT DEFAULT 0;
SET @t1 =CONCAT("SELECT COUNT(*) FROM ",V, " INTO",e);
SET @t2 =CONCAT("The ",V," table contains ",e," rows");
PREPARE stmt3 FROM @t1;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
SELECT @t2;
END$$
DELIMITER ;

编辑:

使用用户定义的变量尝试这样一次(下面的过程主体)

DELIMITER $$
CREATE PROCEDURE `countRows`(IN v varchar(30))
    BEGIN
    SET @e := 0;
    SET @t1 =CONCAT("SELECT COUNT(*) INTO @e FROM ",V);
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
    SELECT CONCAT("The ",V," table contains ",@e," rows");
   END$$
DELIMITER ;