Home > Database > Mysql Tutorial > limit offset,n揭秘_MySQL

limit offset,n揭秘_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2016-06-01 13:51:54
Original
1288 people have browsed it

今天利用limit offset,n从一个表中分批导出数据的时候,总比原表少那么几条记录,创建一个小表做测试才发现其中的奥秘:

mysql> select * from sss1;<br>+----+------+------+<br>| id | name | c_id |<br>+----+------+------+<br>|  1 | aaa  |    1 | <br>|  2 | bbb  |    2 | <br>|  3 | ccc  |    3 | <br>|  4 | ddd  |    1 | <br>|  5 | eee  |    4 | <br>|  6 | ffff |    6 | <br>|  7 | NULL |    0 | <br>|  8 | NULL |    0 | <br>|  9 | NULL |    0 | <br>| 10 | ssss |    0 | <br>| 11 | ooo  |    0 | <br>| 12 | 234  |    0 | <br>+----+------+------+<br>12 rows in set (0.00 sec)<br><br>mysql> select * from sss1 limit 0,3;<br>+----+------+------+<br>| id | name | c_id |<br>+----+------+------+<br>|  1 | aaa  |    1 | <br>|  2 | bbb  |    2 | <br>|  3 | ccc  |    3 | <br>+----+------+------+<br>3 rows in set (0.00 sec)<br><br>mysql> select * from sss1 limit 4,6; <br>+----+------+------+<br>| id | name | c_id |<br>+----+------+------+<br>|  5 | eee  |    4 | <br>|  6 | ffff |    6 | <br>|  7 | NULL |    0 | <br>|  8 | NULL |    0 | <br>|  9 | NULL |    0 | <br>| 10 | ssss |    0 | <br>+----+------+------+<br>6 rows in set (0.00 sec)
Copy after login

看明白了吗?

如果有offset的话,就会从offset+1条开始显示,越过第offset条,所以分批后我才会少几条记录,刚好是我的offset点,解决办法:offset 和上一条的n保持一致就行了:

mysql> select * from sss1;<br>+----+------+------+<br>| id | name | c_id |<br>+----+------+------+<br>|  1 | aaa  |    1 | <br>|  2 | bbb  |    2 | <br>|  3 | ccc  |    3 | <br>|  4 | ddd  |    1 | <br>|  5 | eee  |    4 | <br>|  6 | ffff |    6 | <br>|  7 | NULL |    0 | <br>|  8 | NULL |    0 | <br>|  9 | NULL |    0 | <br>| 10 | ssss |    0 | <br>| 11 | ooo  |    0 | <br>| 12 | 234  |    0 | <br>+----+------+------+<br>12 rows in set (0.00 sec)<br><br>mysql> select * from sss1 limit 0,3;<br>+----+------+------+<br>| id | name | c_id |<br>+----+------+------+<br>|  1 | aaa  |    1 | <br>|  2 | bbb  |    2 | <br>|  3 | ccc  |    3 | <br>+----+------+------+<br>3 rows in set (0.00 sec)<br><br>mysql> select * from sss1 limit 3,6; <br>+----+------+------+<br>| id | name | c_id |<br>+----+------+------+<br>|  4 | ddd  |    1 | <br>|  5 | eee  |    4 | <br>|  6 | ffff |    6 | <br>|  7 | NULL |    0 | <br>|  8 | NULL |    0 | <br>|  9 | NULL |    0 | <br>+----+------+------+<br>6 rows in set (0.00 sec)
Copy after login
Related labels:
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Issues
Update one column of data in the table
From 1970-01-01 08:00:00
0
0
0
Optimize table indexes in MySQL
From 1970-01-01 08:00:00
0
0
0
Why can't the list be rendered?
From 1970-01-01 08:00:00
0
0
0
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template