What is the usage of limit in mysql

In mysql, the limit keyword is mainly used to specify which record the query results start to be displayed, and how many records are displayed in total; it has three usage syntaxes: "LIMIT initial position, number of records", "LIMIT record" Number" and "LIMIT number of records 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 one time will reduce the speed of data return and put a lot of pressure on the database server. At this time, you can use the LIMIT keyword to limit the number of returned query results.

LIMIT is a special keyword in MySQL, which is used to specify which record the query results start to be displayed and how many records are displayed in total.

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

Specify the initial position

The LIMIT keyword can specify which record the query results should start displaying from and how many records should be displayed.

LIMIT The basic syntax format for specifying the initial position is as follows:

LIMIT 初始位置，记录数

Among them, "initial position" indicates which record to start displaying; "number of records" indicates the number of displayed records. The first record is at position 0 and the second record is at position 1. The subsequent records are deduced in sequence.

Note: The two parameters after LIMIT must be positive integers.

Example 1

In the tb_students_info table, use the LIMIT clause to return 5 records starting from the 4th record. The SQL statement and running results are as follows.

mysql> SELECT * FROM tb_students_info LIMIT 3,5;
+----+-------+---------+------+------+--------+------------+
| id | name  | dept_id | age  | sex  | height | login_date |
+----+-------+---------+------+------+--------+------------+
|  4 | Jane  |       1 |   22 | F    |    162 | 2016-12-20 |
|  5 | Jim   |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily  |       6 |   22 | F    |    165 | 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 results, this statement returns the next 5 records starting from the 4th record. The first number "3" after the LIMIT keyword means starting from line 4 (the recorded position starts from 0, and the position of line 4 is 3), and the second number 5 means the number of rows returned.

Do not specify the initial position

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

The basic syntax format of LIMIT without specifying the initial position is as follows:

LIMIT 记录数

Among them, "number of records" indicates the number of displayed records. If the value of "Number of records" is less than the total number of query results, the specified number of records will be displayed starting from the first record. If the value of "Number of records" is greater than the total number of query results, all the queried records will be displayed directly.

Example 2

Display the first 4 rows of the query results of the tb_students_info table. The SQL statements and running results are as follows.

mysql> SELECT * FROM tb_students_info LIMIT 4;
+----+-------+---------+------+------+--------+------------+
| id | name  | dept_id | age  | sex  | height | login_date |
+----+-------+---------+------+------+--------+------------+
|  1 | Dany  |       1 |   25 | F    |    160 | 2015-09-10 |
|  2 | Green |       3 |   23 | F    |    158 | 2016-10-22 |
|  3 | Henry |       2 |   23 | M    |    185 | 2015-05-31 |
|  4 | Jane  |       1 |   22 | F    |    162 | 2016-12-20 |
+----+-------+---------+------+------+--------+------------+
4 rows in set (0.00 sec)

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

Example 3

Display the first 15 rows of the query results of the tb_students_info table. The SQL statements and running 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    |    160 | 2015-09-10 |
|  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 |
|  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 |
|  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 |
|  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 |
|  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 |
|  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 |
| 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.26 sec)

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

LIMIT with one parameter specifies starting from the first row of the query result. The only parameter indicates the number of rows returned, that is, "LIMIT n" and "LIMIT 0, n" return the same result. LIMIT with two arguments returns a specified number of rows of data starting at any position.

LIMIT is used in combination with OFFSET

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

LIMIT 记录数 OFFSET 初始位置

Parameters and LIMIT The parameters in the syntax have the same meaning. "Initial position" specifies which record to start displaying; "Number of records" indicates the number of records to display.

Example 4

In the tb_students_info table, use LIMIT OFFSET to return 5 records starting from the 4th record. The SQL statement and running 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    |    162 | 2016-12-20 |
|  5 | Jim   |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily  |       6 |   22 | F    |    165 | 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 results, this statement returns the next 5 records starting from the 4th record. That is, "LIMIT 5 OFFSET 3" means to obtain the next 5 records starting from the 4th record, which is the same as the result returned by "LIMIT 3, 5".

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the usage of limit in mysql. For more information, please follow other related articles on the PHP Chinese website!