limit offset,n揭秘

时间：2011-05-18 来源：sunss

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

相关阅读更多 +

光与影33号远征队神秘废墟经怪要不要杀(光与影33号远征队) 游戏资讯 2025-05-26
熊视眈眈游戏多少钱游戏资讯 2025-05-26
光与影33号远征队神秘废墟隐藏任务怎么做(光与影33号远征队拦腰法) 游戏资讯 2025-05-26
炉石传说标准天梯平衡补丁首日卡组分享(炉石传说标准天赋是什么) 游戏资讯 2025-05-26
剑星胜利女神dlc什么时候出(剑星胜利女神) 游戏资讯 2025-05-26

排行榜更多 +

战机公司汉化版下载

飞行射击下载

飞行射击下载

飞行射击下载