使用“SELECT * .. LiMIT start, count”扫描 MySQL 中的表是否正确？没有 ORDER BY 子句？
假设表 X 有 100 个元组。
以下扫描 X 的方法会在 MySQL 中生成表 X 中的所有元组吗？
for start in [0, 10, 20, ..., 90]:
print results of "select * from X LIMIT start, 10;"
我问，因为我一直在使用 PostgreSQL，它清楚地表明这种方法 不需要工作，但似乎没有这样的信息 MySQL。如果不会，是否有一种方法可以在不知道有关表的任何其他信息（例如主键字段是什么）的情况下以固定顺序返回结果？
我需要扫描应用程序中表中的每个元组，并且我希望有一种方法可以在不使用应用程序中太多内存的情况下执行此操作（因此只需执行“select * from X”即可）。
Suppose Table X has a 100 tuples.
Will the following approach to scanning X generate all the tuples in TABLE X, in MySQL?
for start in [0, 10, 20, ..., 90]:
print results of "select * from X LIMIT start, 10;"
I ask, because I've been using PostgreSQL, which clearly says that this approach need not work, but there seems to be no such info for MySQL. If it won't, is there a way to return results in a fixed ordering without knowing any other info about the table (like what the primary key fields are)?
I need to scan each tuple in a table in an application, and I want a way to do it without using too much memory in the application (so simply doing a "select * from X" is out).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不，这不是一个安全的假设。如果没有
ORDER BY子句，则无法保证您的查询每次都会返回唯一的结果。如果该表已正确建立索引，则添加ORDER BY（对于索引）应该不会太昂贵。编辑：非
ORDER BY编辑的结果有时会按照聚集索引的顺序排列，但我不会为此投入任何金钱！No, that isn't a safe assumption. Without an
ORDER BYclause, there is no guaranteeing that your query will return unique results each time. If this table is properly indexed, adding anORDER BY(for the index) shouldn't be too expensive.Edit: Non-
ORDER BYed results will sometimes be in the order of the clustered index, but I wouldn't put any money on that!如果您使用 Innodb 或 MyISAM 表类型，更好的方法是使用 HANDLER 接口。只有 MySQL 支持此功能，但它可以满足您的要求：
http://dev .mysql.com/doc/refman/5.0/en/handler.html
另外，MySQL API 支持两种从服务器检索数据的模式：
大多数不同语言的 MySQL API 都以某种形式支持这一点。它通常是创建连接时提供的参数，和/或可用于现有连接以将其切换到该模式的单独调用。
因此，为了回答你的问题 - 我会执行以下操作：
If you are using Innodb or MyISAM table types, a better approach is to use the HANDLER interface. Only MySQL supports this, but it does what you want:
http://dev.mysql.com/doc/refman/5.0/en/handler.html
Also, the MySQL API supports two modes of retrieving data from the server:
Most of the MySQL APIs for various languages support this in oneform or another. It is usually an argument that can be supplied as when creating the connection, and / or a separate call that can be used against an existing connection to switch it to that mode.
So, in answer to your question - I would do the following: