SQLite LIMIT Clause: OFFSET or OFFSET and ROW COUNT?

Which LIMIT clause is syntactically correct in SQLite: OFFSET or OFFSET and ROW COUNT?

SQLite provides two syntaxes to limit and offset query results:

<code>LIMIT <跳过>, <数量></code>

and

<code>LIMIT <数量> OFFSET <跳过></code>

The difference between these two grammatical forms can be confusing because their numbers are in reverse order.

The first grammatical form <code>LIMIT <跳过>, <数量></code> is equivalent to:

<code>LIMIT <数量> OFFSET <跳过></code>

It is syntax compatible with MySQL and PostgreSQL. MySQL supports both syntax forms, while PostgreSQL only supports the second syntax. SQLite supports both syntax forms, but it is recommended to use the second syntax to avoid confusion.

For example, the following query will skip the first 50 rows and return the next 100 rows:

<code>SELECT * FROM Animals LIMIT 100 OFFSET 50</code>

It is important to note that using ORDER BY without using LIMIT first does not always give the expected results. SQLite will return rows in some order, which may be determined by where they are physically stored in the file. However, this order is not guaranteed to be meaningful or consistent. The only way to get a predictable order is to use ORDER BY explicitly.

The above is the detailed content of SQLite LIMIT Clause: OFFSET or OFFSET and ROW COUNT?. For more information, please follow other related articles on the PHP Chinese website!