我正在使用ORM(sqlalchemy)从PG数据库中获取数据.我想避免在我手工制作的sql语句*中指定所有表列名.
到目前为止,我的假设是返回的列是用于创建db表的DDL语句的顺序.到目前为止,这是有效的 – 但我想知道这只是运气,还是在(ANSI)sql规范中专门解决.
即ANSI sql(因此可能是数据库)是否保证SELECT *语句中返回的列的顺序？
>是的,我知道在ORM中使用手工制作的sql语句会破坏ORM的目的,但需要……
解决方法
让我们考虑sql标准,第7.9节<查询规范>如下所示：
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中的序数位置来投射列.注意,当你的< table expression>时,事情变得有点棘手.由几个涉及JOIN … USING或NATURAL JOIN子句的表组成.但是,从简单的表中进行选择时,假设订单符合预期,您可能会很好.
为了完整起见,表格中T的序数位置的含义将在11.4< column definition>中进一步解释：
General Rules
5) [...] The ordinal position included
in the column descriptor is equal to the degree of T. [...]
然后在11.11< add column definition> (对于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语句和子句依赖于< table expressions>中的序数位置的正式规范.一些例子：
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 *,请继续！
