limit offset,n揭秘_MySQL-mysql教程-PHP中文网

limit offset,n揭秘_MySQL

php中文网

发布： 2016-06-01 13:51:54

原创

1415人浏览过

今天利用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)

登录后复制

看明白了吗？

如果有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)

登录后复制