使用"SELECT * .. LiMIT start，count"在MySQL中扫描表是否正确?没有ORDER BY子句? [英] Is it correct to scan a table in MySQL using "SELECT * .. LiMIT start, count" without an ORDER BY clause?
问题描述
假设表X有100个元组.
Suppose Table X has a 100 tuples.
以下扫描X的方法是否会在MySQL中在表X中生成所有元组?
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;"
我问，因为我一直在使用PostgreSQL，它清楚地表明这种方法
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)?
我需要扫描应用程序表中的每个元组，并且我想找到一种方法来在不使用应用程序过多内存的情况下进行扫描(因此只需执行"select * from X"就可以了).
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).
推荐答案
如果使用的是Innodb或MyISAM表类型，则更好的方法是使用HANDLER接口.只有MySQL支持此功能，但是它可以满足您的要求:
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
此外，MySQL API支持两种从服务器检索数据的模式:
Also, the MySQL API supports two modes of retrieving data from the server:
- 存储结果:在这种模式下，执行查询后，API会在返回用户代码之前检索整个结果集.这样会耗尽很多客户端内存的缓冲结果，但会最大程度地减少服务器上资源的使用.
- 使用结果:在这种模式下，API逐行提取结果并将控制权更频繁地返回给用户代码.这样可以最大程度地减少客户端上的内存使用量，但可以将服务器上的锁保持更长的时间.
大多数用于各种语言的MySQL API都以一种或另一种形式支持该功能.通常，它是创建连接时可以提供的参数，和/或可以用于现有连接以将其切换到该模式的单独调用.
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:
set the connection to "use result" mode;
select * from X
这篇关于使用"SELECT * .. LiMIT start，count"在MySQL中扫描表是否正确?没有ORDER BY子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
