MySQLSyntaxErrorException: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

同学的一个项目。。之前的数据库是MSSQL的，满目的top在子查询语句中，于是照猫画虎，把top和pageSize放到后面改成limit，发现报错。

这个错误的意思是，该版本的MySQL不支持LIMIT在IN/ALL/ANY/SOME的子句中。

其实 = = limit函数已经很好用了。。比如原来的MSSQL用的拼接语句是

sqlStr = "select top " + pageSize + " * from IndentInfo";
if (page == 1)
{
sqlStr = sqlStr + " order by Id desc";
}else {
sqlStr = sqlStr + " where Id not in ( select top " + (recordCount-pageSize * page ) + " Id from IndentInfo order by Id
) and Id in " +
"(select top " + (recordCount - pageSize * (page-1)) + " Id from IndentInfo order by Id) " + " order by Id desc";
}

改为MySQL用的拼接语句则可以是：

sqlStr = "select * from IndentInfo";
if (page == 1)
{
sqlStr = sqlStr + " order by Id desc limit " + pageSize;
}else {
sqlStr = sqlStr + " order by Id desc limit " + (recordCount - pageSize * (page-1)) + ", " + pageSize;
}

limit中的参数如果只有1个，则表示页面大小；如果有2个，则第一个参数为起始记录，第二个参数为页面大小。