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

查看：29 发布时间：2021/12/30 14:54:04 sql postgresql sqlalchemy ansi-sql

本文介绍了SELECT * 语句中的列顺序 - 保证?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用 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*.

到目前为止，我的假设是返回的列按照用于创建 db 表的 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

是的，我知道将手工制作的 SQL 语句与 ORM 结合使用会违背 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 .. USING 或 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 中进一步解释了表的序数位置在 T 中的含义:

For completeness, the meaning of an ordinal position within T for tables is explained further down in 11.4 <column definition>:

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 语句和子句依赖于 <表表达式> 中 ordinal position 的正式规范.一些例子:

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屋！

查看全文

登录关闭

扫码关注1秒登录

发送“验证码”获取 | 15天全站免登陆

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

问题描述

推荐答案

相关文章

其他开发最新文章

热门教程

热门工具

登录关闭

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

问题描述

推荐答案

相关文章

其他开发最新文章

热门教程

热门工具

登录 关闭

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

登录关闭