limit中 使用事项 不能使用表达式 避免踩坑

limit中不能使用表达式

mysql> select * from t_order where limit 1,4+1;
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 'limit 1,4+1' at line 1
mysql> select * from t_order where limit 1+0;
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 'limit 1+0' at line 1
mysql>

结论：limit后面只能够跟明确的数字。

limit后面的2个数字不能为负数

mysql> select * from t_order where limit -1;
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 'limit -1' at line 1
mysql> select * from t_order where limit 0,-1;
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 'limit 0,-1' at line 1
mysql> select * from t_order where limit -1,-1;
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 'limit -1,-1' at line 1

总结

• order by ... [asc|desc]用于对查询结果排序，asc：升序，desc：降序，asc|desc可以省略，默认为asc
• limit用来限制查询结果返回的行数，有2个参数（offset，count），offset：表示跳过多少行，count：表示跳过offset行之后取count行
• limit中offset可以省略，默认值为0
• limit中offset 和 count都必须大于等于0
• limit中offset和count的值不能用表达式
• 分页排序时，排序不要有二义性，二义性情况下可能会导致分页结果乱序，可以在后面追加一个主键排序