mysql 查询 - 一起使用 in & order by 和 limit

Question

在 mysql 中运行查询时，得到了一些奇怪的结果:

SQL 1:

 SELECT *
 FROM location
 WHERE name LIKE CONCAT('%','SZ','%')
   AND `STATUS` = 1
   AND location_level IN (4,3)
 ORDER BY location_level;

SQL 2:

 SELECT * 
 FROM location
 WHERE name LIKE CONCAT('%','SZ','%')
   AND `STATUS` = 1
   AND location_level IN (4,3)
 ORDER BY location_level
 LIMIT 2;

这两个 sql 之间的唯一区别是 Sql 2 有一个 limit 部分。

执行结果:

• Sql 1 has 1 row,
• Sql 2 has 0 rows

问题:

我希望 Sql 2 也应该得到结果，但是没有，知道吗？

---

@更新:

windows上的mysql好像比较老，我停止不了，好像死机了，所以卸载了，然后下载mysql5.6，安装，现在2个sql都一样了。

对于无意义的问题，我们深表歉意。有了mysql5.6就可以了。

Answer 1

我在 sqlfiddle.com 上试过了并得到预期的结果，即 1。我也浏览了 mySQL 文档并发现了以下内容

If you combine LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result.****(which in your case it should have return 1 if in somehow the result didn't gets changed in middle) If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set