向mysql存储过程传递表名和限制不起作用 - passing table name and limit to mysql stored procedure not working

passing table name and limit to mysql stored procedure not working

本文关键字：不起作用 mysql 存储过程 | 更新日期: 2023-09-27

我创建了如下的过程。我正在传递表名和限制以及用户名，

DELIMITER $$
DROP PROCEDURE IF EXISTS `GetPosteingang`$$
CREATE PROCEDURE  `GetPosteingang`
(
IN stlimit INT,
IN tblname VARCHAR(100),
IN userId INT
)
BEGIN
DECLARE t1 VARCHAR(5000); 
SET @t1 =
"SELECT msg_id,msg_from_name as fromname,msg_titel as title,msg_date as date,msg_gelesen,msg_replied,msg_nodel,'msg' as type 
FROM "+@tblname+"
UNION
SELECT files_id as msg_id,from_username as fromname,files_oname as title,files_time as date,files_name as msg_gelesen,files_extension as msg_replied,files_filesize as msg_nodel,'file' as type 
FROM community_files
WHERE user_id = "+@userId+"
ORDER BY date DESC
LIMIT "+@stlimit+",30";
#SET @t1 =CONCAT('SELECT * FROM ',tab_name );
PREPARE STMT FROM @t1;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END $$
DELIMITER ;

但是当我调用这个时，

CALL GetPosteingang('1','community_msgin8','658468');

显示错误，

CALL GetPosteingang('1','community_msgin8','658468')    Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

请帮帮我。

+不是字符串连接操作符—使用MySQL的CONCAT()函数代替;
用户自定义(会话)变量(以@为前缀)与存储程序/声明变量(没有前缀)不同;
小心使用tblname调用您的过程，这会导致SQL注入;

UNION

列名由第一个查询决定;和
参数化你准备好的语句，如果可能的话。

因此:

CREATE PROCEDURE  `GetPosteingang`
(
  IN stlimit INT,
  IN tblname VARCHAR(100),
  IN userId INT
)
BEGIN
  SET @t1 := CONCAT("
    SELECT msg_id,
           msg_from_name AS fromname,
           msg_titel     AS title,
           msg_date      AS date,
           msg_gelesen,
           msg_replied,
           msg_nodel,
           'msg'         AS type
    FROM   `", REPLACE('`','``',tblname), "`
  UNION
    SELECT files_id,
           from_username,
           files_oname,
           files_time,
           files_name,
           files_extension,
           files_filesize,
           'file' as type
    FROM   community_files
    WHERE  user_id = ?
  ORDER BY date DESC
  LIMIT    ?, 30
  ", @t2 := userId, @t3 := stlimit;
  PREPARE stmt FROM @t1;
  EXECUTE stmt USING @t2, @t3;
  DEALLOCATE PREPARE stmt;
END$$

然而，尽管如此，拥有一个变量表名是一个强烈的指示符，表明你的模式是严重的反规范化的&考虑合并所有这样的表，并使用列来标识它们的差异。

上面的代码缺少CONCAT函数的结束括号。所以应该是这样:

CREATE PROCEDURE  `GetPosteingang`
(
  IN stlimit INT,
  IN tblname VARCHAR(100),
  IN userId INT
)
BEGIN
  SET @t1 := CONCAT("
    SELECT msg_id,
           msg_from_name AS fromname,
           msg_titel     AS title,
           msg_date      AS date,
           msg_gelesen,
           msg_replied,
           msg_nodel,
           'msg'         AS type
    FROM   `", REPLACE('`','``',tblname), "`
  UNION
    SELECT files_id,
           from_username,
           files_oname,
           files_time,
           files_name,
           files_extension,
           files_filesize,
           'file' as type
    FROM   community_files
    WHERE  user_id = ?
  ORDER BY date DESC
  LIMIT    ?, 30
  "), @t2 := userId, @t3 := stlimit;
  PREPARE stmt FROM @t1;
  EXECUTE stmt USING @t2, @t3;
  DEALLOCATE PREPARE stmt;
END$$