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个，则第一个参数为起始记录，第二个参数为页面大小。
评论 (0)
