为什么这个MySQL过程语法不正确?适用于测试服务器

Question

我已经编写了几个过程，并在我的虚拟主机的服务器上成功实现了它们。但是，通过此过程，我收到#1064错误。我在MySQL Workbench中编写了它，它没有一点抱怨。在我的本地计算机上工作正常。

USE mydb
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `temp`(
  IN p_page int(11),
  IN p_approved varchar(3))
  BEGIN
    DECLARE v_postsPerPage tinyint;
    DECLARE v_totalPostCount int(11);
    DECLARE v_listFrom int(11);
    SELECT posts_per_page INTO v_postsPerPage FROM diskus_settings;
    SELECT COUNT(*) INTO v_totalPostCount FROM diskus_post;
    SET v_listFrom = p_page * v_postsPerPage - v_postsPerPage;
    SELECT p.post_id, p.user_id, u.username, get_time_diff(p.date) as date, p.ip, p.text, p.parent_post_id, p.approved AS posts
    FROM diskus_post p, diskus_user u
    WHERE p.user_id = u.user_id
      AND p.approved = p_approved
    ORDER BY p.date DESC LIMIT v_listFrom, v_postsPerPage;
END $$

Answer 1

12.2.9. 选择语法

在存储程序中，可以使用以下命令指定 LIMIT 参数 MySQL 的整数值例程参数或局部变量 5.5.6.

SP 不接受 LIMIT 子句中的变量

Answer 2

这应该有效 -

USE mydb
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `temp`(IN p_page int(11), IN p_approved varchar(3))
  BEGIN
    DECLARE v_postsPerPage tinyint;
    DECLARE v_totalPostCount int(11);
    DECLARE v_listFrom int(11);
    SELECT posts_per_page INTO v_postsPerPage FROM diskus_settings;
    SELECT COUNT(*) INTO v_totalPostCount FROM diskus_post;
    SET v_listFrom = p_page * v_postsPerPage - v_postsPerPage;
    SET @sql = CONCAT('SELECT p.post_id, p.user_id, u.username, get_time_diff(p.date) as date, p.ip, p.text, p.parent_post_id, p.approved AS posts
        FROM diskus_post p, diskus_user u
        WHERE p.user_id = u.user_id
        AND p.approved = p_approved
        ORDER BY p.date DESC
        LIMIT ', v_listFrom, ', ', v_postsPerPage);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$