SELECT *语句中的列顺序-保证吗？ [英] column order in SELECT * statement - guaranteed?

问题描述

我正在使用ORM（sqlalchemy）从PG数据库中获取数据。我想避免在手工制作的SQL语句中指定所有表列名称*。

I am using an ORM (sqlalchemy) to fetch data from a PG database. I want to avoid specifying all the table column names in my hand crafted SQL statements*.

到目前为止，我的假设是返回的列按DDL语句的顺序排列用于创建数据库表。到目前为止，这是可行的-但我想知道这是否只是运气，还是在（ANSI）SQL规范中专门解决了这个问题。

My assumption so far is that the returned columns are in the order of the DDL statements used to create the db tables. So far this is working - but I want to know if this is merely luck, or if it is specifically addressed in the (ANSI) SQL specification.

即ANSI SQL（因此可能是数据库）是否保证 SELECT * 语句中返回的列顺序？

i.e. does ANSI SQL (and thus presumably the database) guarantee the order of columns returned in a SELECT * statement?

我将PostgreSQL 8.4用作后端数据库

I am using PostgreSQL 8.4 as my backend db

• 是的，我知道使用带有ORM的手工SQL语句会破坏ORM，但是需要必须...

推荐答案

让我们考虑SQL标准， 7.9<查询规范> 如此处指定：

Let's consider the SQL standard, section 7.9 <query specification> as specified here:

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

<query specification> ::=
          SELECT [ <set quantifier> ] <select list> <table expression>
[...]
<select list> ::=
            <asterisk>
          | <select sublist> [ { <comma> <select sublist> }... ]

[...]
Syntax Rules
1) Let T be the result of the <table expression>.
3) Case:
       a) [...]
       b) Otherwise, the <select list> "*" is equivalent to a <value
          expression> sequence in which each <value expression> is a
          <column reference> that references a column of T and each
          column of T is referenced exactly once. The columns are ref-
          erenced in the ascending sequence of their ordinal position
          within T.

因此，换句话说，是的，SQL标准指定要根据列在 T 中的顺序位置来投影列。请注意，当您的<表表达式> 由几个涉及 JOIN ..使用或 NATURAL JOIN 子句。但是，从简单表中进行选择时，假设顺序是预期的就可以了。

So, in other words, yes, the SQL standard specifies that columns are to be projected according to their ordinal position within T. Note, that things get a bit tricky, when your <table expression> consists of several tables involving JOIN .. USING or NATURAL JOIN clauses. However, when selecting from a simple table, you're probably fine assuming that the order is as expected.

为完整性起见，的含义 11.4<列定义> ：

General Rules
     5) [...] The ordinal position included
        in the column descriptor is equal to the degree of T. [...]

，然后在 11.11中<添加列定义> （用于 ALTER TABLE 语句）

General Rules
     4) [...] In particular, the degree of T
        is increased by 1 and the ordinal position of that column is
        equal to the new degree of T as specified in the General Rules
        of Subclause 11.4, "<column definition>".

还有很多其他SQL语句和子句，它们取决于<$ c $的形式规范c> <表表达式> 中的普通位置。一些示例：

There are quite a few other SQL statements and clauses that depend on the formal specification of ordinal positions within <table expressions>. Some examples:

13.8 <insert statement> 
     (when omitting the `<insert column list>`)
20.2 <direct select statement: multiple rows>
     (when `<sort specification>` contains an `<unsigned integer>`)

尤其是Postgres，它完全符合标准，因此，如果您真的要 SELECT * ，请继续！

Postgres, in particular, is quite standards-compliant, so if you really want to SELECT *, go ahead!

这篇关于SELECT *语句中的列顺序-保证吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！