Usage of limit in Mysql: When we use query statements, we often need to return the first few items or certain rows of data in the middle. What should we do at this time? Don’t worry, mysql has already provided us with such a function.
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
The LIMIT clause can be used to force the SELECT statement to return a specified number of records. LIMIT accepts one or two numeric arguments. The parameter must be an integer constant. If two parameters are given, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. The offset of the initial record row is 0 (instead of 1): For compatibility with PostgreSQL, MySQL also supports the syntax: LIMIT # OFFSET #.
mysql> SELECT * FROM table LIMIT 5,10; //Retrieve record rows 6-15
//In order to retrieve all record rows from a certain offset to the end of the record set, you can specify the second parameter is -1:
mysql> SELECT * FROM table LIMIT 95,-1; //Retrieve record row 96-last.
//If only one parameter is given, it means returning the maximum number of record rows:
mysql> ; SELECT * FROM table LIMIT 5; //Retrieve the first 5 record rows
//In other words, LIMIT n is equivalent to LIMIT 0,n.
Note the difference between limit 10 and limit 9, 1:
For example:
1.
Select * From cyclopedia Where ID>=( Select Max(ID) From ( Select ID From cyclopedia Order By ID limit 90001 ) As tmp ) limit 100;
2.
Select * From cyclopedia Where ID>=( Select Max(ID) From ( Select ID From cyclopedia Order By ID limit 90000,1 ) As tmp ) limit 100;