SQL 中的 LIMIT 语句有多通用?
我正在推广 Django DB 复制应用程序,它使用以下语句:
SELECT %s FROM %s LIMIT 1
获取 1 行并使用 Python DBAPI 来描述字段,它与 ORACLE 和 MySQL 配合良好,但是 LIMIT 语句的跨平台程度如何?
I'm in the process of generalizing a Django DB replication app and it uses the statement:
SELECT %s FROM %s LIMIT 1
to fetch 1 row and use the Python DBAPI to describe the fields, it works fine with ORACLE and MySQL but, how cross platform is the LIMIT statement?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
LIMIT
在各种开源数据库中非常流行,但不幸的是,事实是OFFSET
分页一直是所有数据库中标准化程度最低的 SQL 功能,最早在 SQL:2008 中标准化。在此之前,jOOQ 用户
LIMIT
子句 上的手册页显示了如何在每种 SQL 方言中形成各种等效语句:现在,这些都非常简单,对吧?当您必须模拟它们时,令人讨厌的部分来了:
在此处了解
ROW_NUMBER()
与DENSE_RANK()
的基本原理选择你的毒药; -)
LIMIT
has become quite popular with a variety of Open Source databases, but unfortunately, the fact is thatOFFSET
pagination has been about the least standardised SQL feature of them all, having been standardised as late as in SQL:2008.Until then, the jOOQ user manual page on the
LIMIT
clause shows how the various equivalent statements can be formed in each SQL dialect:Now, these were all pretty straight-forward, right? Here comes the nasty part, when you have to emulate them:
Read about the
ROW_NUMBER()
vs.DENSE_RANK()
rationale hereChoose your poison ;-)
LIMIT
远非通用 - 在主要的 RDBMS 中,它几乎仅限于 MySQL 和 PostgreSQL。 这里详细分析了许多其他实现中如何实现这一点,包括MSSQL、Oracle 和 DB2,以及 ANSI SQL。LIMIT
is very far from universal - out of major RDBMS, it's pretty much restricted to MySQL and PostgreSQL. Here is a detailed analysis of how this is done in many other implementations, including MSSQL, Oracle and DB2, as well as in ANSI SQL.http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows< /a> 列出 select 命令的所有主要变体。
我相信最好的方法是在 SELECT 语句之前使用 SET ROWCOUNT 命令。
所以,对你来说:
http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows lists all of the major variants of the select command.
I believe the best way to do this is to use the SET ROWCOUNT command before your SELECT statement.
So, for you:
它根本不具有普遍性。事实上,我很惊讶它在 Oracle 中为你工作;它以前不存在。通常 Oracle 用户会选择
ROWNUM
。每个数据库都有自己的语法,用于按行号限制结果。还有两种方法是 ANSI 标准 SQL:
FETCH FIRST
。源自 DB/2,仅在 SQL:2008 中制定标准,因此很少有 DBMS 支持。无法使用偏移量。窗口函数
SELECT ..., ROW_NUMBER() OVER (ORDER BY some_ordering) AS rn WHERE rn BETWEEN n AND m ... ORDER BY some_ordering
。这是来自 SQL:2003 的,在较新的 DBMS 中有一些(不完整,有时很慢)支持。它可以在行号上使用偏移量或任何其他比较函数,但具有丑陋得令人震惊的缺点。这里有一个很好的概述,如果您愿意的话,您将不得不处理这些乏味的事情跨 DBMS 分页支持。
It's not at all universal. Actually I am surprised it is working for you in Oracle; it didn't used to be present. Normally Oracle users go for
ROWNUM
.Every database has its own syntax for limiting results by row number. There are also two methods that are ANSI standard SQL:
FETCH FIRST
. Derived from DB/2 and only made standard in SQL:2008, so very little DBMS support. Can't use an offset.The windowing function
SELECT ..., ROW_NUMBER() OVER (ORDER BY some_ordering) AS rn WHERE rn BETWEEN n AND m ... ORDER BY some_ordering
. This is from SQL:2003 and has some (patchy, sometimes slow) support in newer DBMSs. It can use an offset or any other comparison function on the row number, but has the drawback of being appallingly ugly.Here's a good overview of the tediousness you will have to deal with if you want cross-DBMS pagination support.
自 1992 年标准起,LIMIT 不再是 ANSI SQL 标准的一部分;我手头没有任何后来的标准的副本。即使在最好的情况下,供应商对标准的遵守情况也相当模糊。就其价值而言,“LIMIT”被列为保留字(这意味着即使它不是实现中的关键字,它也不能合法地用作标识符)。
LIMIT is not part of the ANSI SQL standard as of the 1992 standard; I don't have a copy of any later standard to hand. Vendors' compliance with the standard is pretty vague at the best of times. For what it's worth, "LIMIT" is listed as a reserved word (meaning it can't legally be used as an identifier even in cases where it's not a keyword in the implementation).
它不适用于 MSSQL(它使用 SELECT TOP 10 * FROM Blah 代替)。这削减了数据库市场的很大一部分。我不确定其他人的情况。
此外,您的 DB API 有可能(尽管可能性很小)为您翻译它。
It doesn't work on MSSQL (which uses
SELECT TOP 10 * FROM Blah
instead). That cuts out a significant portion of the DB market. I'm not sure about others.Also, it's possible, though very unlikely, that your DB API will translate it for you.
由于在一个答案中提到LIMIT和OFFSET或多或少仅限于MySQL和PostgreSQL,所以我想指出SAP HANA也支持LIMIT和OFFSET子句。但 SAP HANA 数据库中不允许使用不带 LIMIT 的 OFFSET。
Since it was mentioned in one of the answers that LIMIT and OFFSET is more or less limited to MySQL and PostgreSQL, I thought of pointing out that SAP HANA also supports LIMIT and OFFSET clause. But OFFSET without LIMIT is not permitted in the SAP HANA database.