CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(IN `pageSize` int,IN `currentPage` int,IN `search` varchar)
BEGIN
declare @sql text;
declare @col int;
SELECT @col = ` AutoCode` FROM `zt_classfily` ORDER BY `AutoCode` DESC LIMIT (`pageSize` *(`currentPage` -1)+1) );
SELECT * FROM `zt_classfily` WHERE `AutoCode`<=@col ORDER BY `AutoCode` DESC LIMIT `pageSize`;
END;;
这个没用。去掉
declare @sql text;
declare col int;
SELECT ` AutoCode` into col FROM `zt_classfily` ORDER BY `AutoCode` DESC LIMIT (`pageSize` *(`currentPage` -1)+1) );
SELECT * FROM `zt_classfily` WHERE `AutoCode`<=@col ORDER BY `AutoCode` DESC LIMIT `pageSize`;
END;
如果你的AutoCode是连续的,且有索引的,那建议用以下方式:CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(IN `pageSize` int,IN `currentPage` int,IN `search` varchar(100))
BEGIN
declare v_MaxNum int;
declare v_CurrentFirstNum int;
declare v_CurrentLastNum int;
select ifnull(max(AutoCode),0) into v_MaxNum from zt_classfily;
set v_CurrentFirstNum = v_MaxNum - pageSize*(currentPage-1);
set v_CurrentLastNum = v_CurrentFirstNum - pageSize + 1;
SELECT * FROM `zt_classfily` WHERE `AutoCode`<=v_CurrentFirstNum and `AutoCode`>=v_CurrentLastNum ORDER BY `AutoCode` DESC;
END;
+----------+
| autocode |
+----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----------+
8 rows in set (0.01 sec)mysql> call NewProc(3,2,'');
+----------+
| autocode |
+----------+
| 5 |
| 4 |
| 3 |
+----------+
3 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>
另外有种全局变量以@开头,在动态语句中赋值用此。详细的情况多看一下mysql的帮助手册中关于变量的地方,就都会清晰了。
navicat、phpmyadmin等等