Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use limit in mysql". In daily operation, I believe many people have doubts about how to use limit in mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to use limit in mysql". Next, please follow the editor to study!

In mysql, the limit keyword is mainly used to specify the record from which the query results are displayed and how many records are displayed; it uses the syntax "LIMIT initial position, number of records", "number of LIMIT records" and "LIMIT record number OFFSET initial position".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

When there are tens of thousands of data in the data table, querying all the data in the table at once will reduce the speed of data return and cause great pressure on the database server. At this point, you can use the LIMIT keyword to limit the number of entries returned by the query results.

LIMIT is a special keyword in MySQL that specifies which record the query results are displayed from and how many records are displayed.

The LIMIT keyword can be used in three ways, namely, specifying the initial position, not specifying the initial position, and using it in combination with OFFSET.

Specify initial location

The LIMIT keyword specifies the record from which the query results are displayed and how many records are displayed.

The basic syntax format for LIMIT to specify the initial location is as follows:

LIMIT initial position, number of records

Where the "initial position" indicates the record from which the display starts, and the "number of records" indicates the number of records displayed. The position of the first record is 0 and the position of the second record is 1. The following records are in turn and so on.

Note: both parameters after LIMIT must be positive integers.

Example 1

In the tb_students_info table, use the LIMIT clause to return a record with 5 rows starting with record 4. The SQL statement and run results are as follows.

Mysql > SELECT * FROM tb_students_info LIMIT 3 +-+ | id | name | dept_id | age | sex | height | login_date | +- -+-+ | 4 | Jane | 1 | 22 | F | 2016 | 12-20 | 5 | Jim | 1 | 24 | M | 2016-01-15 | 6 | John | 2 | 21 | M | 2015-11-11 | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 | +-+ 5 rows in set (0.00 sec)

As you can see from the result, this statement returns five records starting with the fourth record. The first number "3" after the LIMIT keyword indicates line 4 (the position of the record starts at 0, and the position of line 4 is 3), and the second number 5 indicates the number of rows returned.

Do not specify the initial position

When the LIMIT keyword does not specify the initial position, the record is displayed from the first record. The number of records displayed is specified by the LIMIT keyword.

The basic syntax format in which LIMIT does not specify the initial location is as follows:

Number of LIMIT records

Where "number of records" indicates the number of records displayed. If the value of the number of records is less than the total number of query results, the specified number of records are displayed starting with the first record. If the value of the number of records is greater than the total number of query results, all records from the query are directly displayed.

Example 2

The first four rows of the tb_students_info query results are displayed. The SQL statement and run results are as follows.

Mysql > SELECT * FROM tb_students_info LIMIT 4 +-+ | id | name | dept_id | age | sex | height | login_date | +- -+-+ 4 rows in set (0.00 sec)

Only four records are displayed in the result, indicating that "LIMIT 4" limits the number of entries displayed to 4.

Example 3

The first 15 rows of the tb_students_info query results are displayed. The SQL statement and run results are as follows.

Mysql > SELECT * FROM tb_students_info LIMIT 15 +-+ | id | name | dept_id | age | sex | height | login_date | + -+-+ | 1 | Dany | 1 | 25 | F | 2015 | 09-10 | 2 | Green | 3 | 23 | F | 2016-10-22 | 3 | Henry | 2 | 23 | M | 2015-05-31 | 4 | Jane | 1 | 22 | F | 2016-12-20 | 5 | | Jim | 1 | 24 | M | 2016 | 01-15 | | 6 | John | 2 | 21 | M | 2015 | 11-11 | | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 2015-10-01 | 9 | Thomas | 3 | 22 | M | 2016-06-07 | | | 10 | Tom | 4 | 23 | M | 165,165 | 2016-08-05 | +-+ 10 rows in set (0.26sec) |

Only 10 records are displayed in the result. Although the LIMIT keyword specifies that 15 records are displayed, there are only 10 records in the query results. Therefore, the database system displays all these 10 records.

LIMIT with one parameter specifies that it starts at the first row of the query result, and the only parameter indicates the number of rows returned, that is, "LIMIT n" returns the same result as "LIMIT 0line n". LIMIT with two parameters returns data that starts at a specified number of rows from anywhere.

Combined use of LIMIT and OFFSET

LIMIT can be used in combination with OFFSET. The syntax format is as follows:

Number of LIMIT records OFFSET initial position

The parameter has the same meaning as the parameter in the LIMIT syntax, "initial position" specifies which record to display, and "number of records" indicates the number of records displayed.

Example 4

In the tb_students_info table, use LIMIT OFFSET to return a record with 5 rows starting with record 4. The SQL statement and run results are as follows.

Mysql > SELECT * FROM tb_students_info LIMIT 5 OFFSET 3 +-+ | id | name | dept_id | age | sex | height | login_date | +- -+-+ | 4 | Jane | 1 | 22 | F | 2016 | 12-20 | 5 | Jim | 1 | 24 | M | 2016-01-15 | 6 | John | 2 | 21 | M | 2015-11-11 | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 | +-+ 5 rows in set (0.00 sec)

As you can see from the result, this statement returns five records starting with the fourth record. That is, "LIMIT 5 OFFSET 3" means to get the next five records starting from Article 4, which is the same as the result returned by "LIMIT 3 OFFSET 5".

At this point, the study on "how to use limit in mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.