mysql 分页存储过程调用报错

以前没有写过存储过程,第一次写遇到了这个问题,没弄明白。希望哪位大神能给出的建议!!!

  • sql 语句如下
CREATE DEFINER=`root`@`%`
PROCEDURE `pager`
(
    IN `table_name` varchar(50),
    IN `columes` varchar(200),
    IN `page_size` int,
    IN `page_now` int,
    IN `where_str` varchar(200),
    IN `order_str` varchar(200),
    OUT `out_rows` int
 )
    DETERMINISTIC
    COMMENT '分页存储过程'
BEGIN
    DECLARE m_begin_row INT DEFAULT 0;
    DECLARE m_limit_string CHAR(64);

    SET m_begin_row = (page_now - 1) * page_size;
    SET m_limit_string = CONCAT('LIMIT', m_begin_row, ',', page_size);

    SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', table_name, ' ', where_str);
    SET @MAIN_STRING = CONCAT('SELECT ', columes, ' FROM ', table_name, ' ', where_str, ' ', order_str,m_limit_string);

    PREPARE count_stmt FROM @COUNT_STRING;
    EXECUTE count_stmt;
    DEALLOCATE PREPARE count_stmt;
    SET out_rows = @ROWS_TOTAL;

    PREPARE main_stmt FROM @MAIN_STRING;
    EXECUTE main_stmt;
    DEALLOCATE PREPARE main_stmt;
END
  • 调用传参如下:
call pager("bco_cms.cms_coupon_user","*",1,3,"","",@result);
  • 报错如下:
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 '1' at line 1
阅读 2.2k
1 个回答

LIMIT后少个空格

SET m_limit_string = CONCAT('LIMIT ', m_begin_row, ',', page_size);
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题