SQLite LIMIT/OFFSET查询 [英] Sqlite LIMIT / OFFSET query

问题描述

我对Sqlite有一个简单的问题.这有什么区别?

I have simple question with Sqlite. What is the difference between this:

Select * from Animals LIMIT 100 OFFSET 50

和

Select * from Animals LIMIT 100,50

推荐答案

这两种语法形式有些混乱，因为它们会颠倒数字:

The two syntax forms are a little confusing because they reverse the numbers:

LIMIT <skip>, <count>

等效于:

LIMIT <count> OFFSET <skip>

它与MySQL和PostgreSQL的语法兼容. MySQL支持两种语法形式，并且其 docs 声称第二种形式OFFSET的语法旨在提供与PostgreSQL的兼容性. PostgreSQL docs 显示它仅支持第二种语法，而 SQLite的文档表明它同时支持这两种格式，建议使用第二种语法以避免混淆.

It's compatible with the syntax from MySQL and PostgreSQL. MySQL supports both syntax forms, and its docs claim that the second syntax with OFFSET was meant to provide compatibility with PostgreSQL. PostgreSQL docs show it only supports the second syntax, and SQLite's docs show that it supports both, recommending the second syntax to avoid confusion.

顺便说一句，在没有先使用ORDER BY的情况下使用LIMIT可能并不总是能为您带来预期的结果.实际上，SQLite将以某种顺序返回行，这可能取决于它们在文件中的物理存储方式.但这并不一定意味着它符合您想要的顺序.获得可预测订单的唯一方法是显式使用ORDER BY.

By the way, using LIMIT without first using ORDER BY may not always give you the results you intend. In practice, SQLite will return the rows in some order, probably determined by how they're physically stored in the file. But this doesn't necessarily mean it's in the order you want. The only way to get a predictable order is to use ORDER BY explicitly.

这篇关于SQLite LIMIT/OFFSET查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！