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.

顺便说一下，使用 LIMIT 而不先使用 ORDER BY 可能并不总是给您想要的结果.实际上，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屋！