mysql存储过程翻页

千家信息网最后更新 2025年01月20日mysql存储过程翻页mysql5.6.20版本

开发提了一个需要分页的存储过程需求,刚来时理解是,只要带入一个变量,根据变量计算下值,就直接分页实现了...可结果,创建存储过程的时候报错了...查了很多资料,后来才得知,limit后面不能带变量.于是就想到直接吧带入参数进行计算.在吧带入参数进行分页...
把大概过程贴一下...自己也记录一下:
mysql>delimiter //
mysql> create procedure t2 ( d1 int)
-> begin
-> set @a=(d1-1)*10;
-> select * from t1 limt @a,1;
-> end//
ERROR 1064 (42000): 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 '@a,1;
end' at line 4

mysql>create procedure t2 ( d1 int)
->begin
->set d1=(d1-1)*10;
->select * from t1 limit d1,1;
->end//
Query OK, 0 rows affected (0.00 sec)


mysql> select * from t1;//
+------+-------+------+
| id | cart | name |
+------+-------+------+
| 1 | 001 | jak |
| 2 | NULL | mak |
| 3 | dd | kk |
| 4 | 2d | dkk |
| 5 | 2ddw2 | 9jd2 |
| 6 | 2ddw2 | 9jd2 |
| 7 | 2ddw2 | 9jd2 |
| 8 | 2ddw2 | 9jd2 |
| 9 | 2ddw2 | 9jd2 |
| 10 | 2ddw2 | 9jd2 |
+------+-------+------+
10 rows in set (0.00 sec)


mysql> call t2(3);//
+------+-------+------+
| id | cart | name |
+------+-------+------+
| 5 | 2ddw2 | 9jd2 |
| 6 | 2ddw2 | 9jd2 |
+------+-------+------+
2 rows in set (0.00 sec)


mysql> select * from t1 limit 3,2;//
+------+-------+------+
| id | cart | name |
+------+-------+------+
| 4 | 2d | dkk |
| 5 | 2ddw2 | 9jd2 |
+------+-------+------+
2 rows in set (0.00 sec)