【韩顺平jsp教程笔记一】MySql下关于分页时取出前几条记录方法（不同于SqlServer）

已知数据库表名为users，表字段如下图：

教程中使用的数据库是sqlserver,取出前几条记录的sql语句是：

select top N * from users where userID not in (selecttop N userID from users);

以取出前3条记录为例，sql语句是：

select top 3 * from users where userID not in (select top 0 userID from users);

也即排除前0条的记录再取出前3条。

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

在使用MySQL数据库做同样的操作时会报错：错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3 * from users where userID not in (select top 0 userID from users)' at line 1

原因是相同的功能，SQL Server和MySQL使用的SQL语句不同。

MySQL下的sql语句为：

SELECT * FROM users LIMIT M,N;或者

SELECT * FROM users WHERE userID NOT IN (SELECT t.userID FROM (SELECT * FROM users LIMIT M ) AS t) LIMIT N;

以取出前3条记录为例，mysql语句是：

SELECT * FROM users LIMIT 0,3;或者

SELECT * FROM users WHERE userID NOT IN (SELECT t.userID FROM (SELECT * FROM users LIMIT 0 ) AS t) LIMIT 3; 结果如图所示：