使用LIMIT与SQLite分页 [英] Pagination with SQLite using LIMIT
问题描述
我正在编写自己的SQLIteBrowser，但我有一个最后的问题，显然是在网络上经常讨论此问题，但似乎没有一个好的通用解决方案.
因此，当前我存储用户输入的SQL.每当需要获取行时，我都会通过在SQL末尾添加"Limit n，m`"来执行SQL.
对于我最常使用的普通SQL，这似乎已经足够好了.但是，如果我想在查询中使用限制我自己，显然会出现错误，因为生成的sql看起来可能像这样:
select * from table limit 30 limit 1,100
这显然是错误的.有更好的方法吗?
我的想法是，我可以扫描SQL并检查是否已经使用了limit子句，然后将其忽略.当然，它不像simlpe那样简单，因为如果我有这样的sql:
select * from a where a.b = ( select x from z limit 1)
在这种情况下，显然仍然应该应用我的限制，因此我可以从头开始扫描字符串，看看是否存在限制.我的问题是，这有多可行.由于我不知道谁是SQL解析器，我不确定LIMIT是否必须位于SQL的末尾，或者末尾是否也可以有其他命令.
我用order by和group by对其进行了测试，如果limit不在末尾，则会出现SQL错误，因此我的假设似乎是正确的.
我现在发现了一个更好的解决方案，它非常简单，不需要我解析SQL.
用户可以输入任意sql.结果被加载到表中.由于我们不想一次加载整个结果，因为这可能返回数百万条记录，因此仅检索N条记录.当用户滚动到表格底部时，接下来的N个项目将被提取并加载到表格中.
解决方案是，将SQL包装到具有我的页面大小限制的外部sql中.
select * from (arbitrary UserSQL) limit PageSize, CurrentOffset
我用我经常使用的SQL对其进行了测试，这看起来效果很好，并且对于我的目的来说也足够快了.
但是，我不知道SQLite是否具有一种机制可以更快地获取新行，或者是否每次都必须重新运行sql.在这种情况下，对于响应时间较长的复杂查询而言，这可能不是一个好的解决方案.
I'm writing my own SQLIteBrowser and I have one final problem, which apparently is quite often discussed on the web, but it doesn't seem to have a good general solution.
So currently I store the SQL which the user entered. Whenever I need to fetch rows, I execute the SQL by adding "Limit n, m` at the end of the SQL.
For normal SQLs, which I mostly use, this seems good enough. However if I want to use limit myself in the query, this will obviously give an error, because the resulting sql can look like this:
select * from table limit 30 limit 1,100
which is obviously wrong. Is there some better way to do this?
My idea was, that I could scan the SQL and check if there is a limit clause already used and then ignore it. Of course it's not as simlpe as that, because if I have an sql like this:
select * from a where a.b = ( select x from z limit 1)
it obviously should still apply my limit in such a case, so I could scan the string from the end and look if there is a limit somehwere. My question now is, how feasable this is. As I don't know who the SQL parser works, I'm not sure if LIMIT has to be at the end of SQL or if there can be other commands at the end as well.
I tested it with order byand group by and I get SQL errors if limit is not at the end, so my assumption seems to be true.
I found now a much better solution which is quite simple and doesn't require me to parse the SQL.
The user can enter an arbitrary sql. The result is loaded into a table. Since we don't want to load the whole result at once, as this can return millions of records, only N records are retriueved. When the user scroll to the bottom of the table the next N items are fetched and loaded into the table.
The solution is, to wrapt the SQL into an outer sql with my page size limits.
select * from (arbitrary UserSQL) limit PageSize, CurrentOffset
I tested it with SQLs I regularly use, and this seem to work quite nicely and is also fast enough for my purpose.
However, I don't know wether SQLite has a mechanism to fetch the new rows faster, or if the sql has to be rerun every time. In that case it might not be a good solution fo rrealy complex queries with a long response time.
这篇关于使用LIMIT与SQLite分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
