mysql的limit offset,n的奥秘
使用mysql中的limit offset,n从一个表中分批导出数据时，总比原表少那么几条记录，创建一个小表做测试才发现其中的奥秘，分享一下，供大家学习参考。
mysql> select * from sss1;
+----+------+------+
| id | name | c_id |
+----+------+------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
| 4 | ddd | 1 |
| 5 | eee | 4 |
| 6 | ffff | 6 |
| 7 | NULL | 0 |
| 8 | NULL | 0 |
| 9 | NULL | 0 |
| 10 | ssss | 0 |
| 11 | ooo | 0 |
| 12 | 234 | 0 |
+----+------+------+
12 rows in set (0.00 sec)
mysql> select * from sss1 limit 0,3;
+----+------+------+
| id | name | c_id |
+----+------+------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
+----+------+------+
3 rows in set (0.00 sec)
mysql> select * from sss1 limit 4,6;
+----+------+------+
| id | name | c_id |
+----+------+------+
| 5 | eee | 4 |
| 6 | ffff | 6 |
| 7 | NULL | 0 |
| 8 | NULL | 0 |
| 9 | NULL | 0 |
| 10 | ssss | 0 |
+----+------+------+
6 rows in set (0.00 sec)
看明白了没有？
如果有offset的话，就会从offset+1条开始显示，越过第offset条，所以分批后我才会少几条记录，刚好是我的offset点，解决办法：offset 和上一条的n保持一致就行了：
mysql> select * from sss1;
+----+------+------+
| id | name | c_id |
+----+------+------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
| 4 | ddd | 1 |
| 5 | eee | 4 |
| 6 | ffff | 6 |
| 7 | NULL | 0 |
| 8 | NULL | 0 |
| 9 | NULL | 0 |
| 10 | ssss | 0 |
| 11 | ooo | 0 |
| 12 | 234 | 0 |
+----+------+------+
12 rows in set (0.00 sec)
mysql> select * from sss1 limit 0,3;
+----+------+------+
| id | name | c_id |
+----+------+------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
+----+------+------+
3 rows in set (0.00 sec)
mysql> select * from sss1 limit 3,6;
+----+------+------+
| id | name | c_id |
+----+------+------+
| 4 | ddd | 1 |
| 5 | eee | 4 |
| 6 | ffff | 6 |
| 7 | NULL | 0 |
| 8 | NULL | 0 |
| 9 | NULL | 0 |
+----+------+------+
6 rows in set (0.00 sec)
您可能感兴趣的文章：
学习MySQL数据分页查询(limit用法)
mysql limit,offset 区别实例分析
mysql的limit offset,n的奥秘
学习mysql中的limit分页优化
mysql中limit、order by和group by的用法实例解析
mysql limit用法及优化详解
实例详解mysql limit大数据量分页优化的方法
MySQL limit查询优化的实例详解
mysql分页limit速度太慢
Mysql中limit的用法实例详解