SQL 为什么选择 0, ... 而不是 SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13219472/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why SELECT 0, ... instead of SELECT
提问by Christian Kienle
Lets say I have a SQLite database that contains a table:
假设我有一个包含表的 SQLite 数据库：
sqlite> create table person (id integer, firstname varchar, lastname varchar);
Now I want to get every entry which is in the table.
现在我想获取表格中的每个条目。
sqlite> select t0.id, t0.firstname, t0.lastname from person t0;
This works fine and this it what I would use. However I have worked with a framework from Apple (Core Data) that generates SQL. This framework generates a slightly different SQL query:
这工作正常，这就是我会使用的。但是，我使用了 Apple（Core Data）的框架来生成 SQL。这个框架生成一个稍微不同的 SQL 查询：
sqlite> select 0, t0.id, t0.firstname, t0.lastname from person t0;
Every SQL query generated by this framework begins with "select 0,". Why is that?
此框架生成的每个 SQL 查询都以“select 0,”开头。这是为什么？
I tried to use the explain command to see whats going on but this was inconclusive - at least to me.
我尝试使用解释命令来查看发生了什么，但这是不确定的 - 至少对我而言。
sqlite> explain select t0.id, t0.firstname, t0.lastname from person t0;
addr opcode p1 p2 p3 p4 p5 comment
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 Trace 0 0 0 00 NULL
1 Goto 0 11 0 00 NULL
2 OpenRead 0 2 0 3 00 NULL
3 Rewind 0 9 0 00 NULL
4 Column 0 0 1 00 NULL
5 Column 0 1 2 00 NULL
6 Column 0 2 3 00 NULL
7 ResultRow 1 3 0 00 NULL
8 Next 0 4 0 01 NULL
9 Close 0 0 0 00 NULL
10 Halt 0 0 0 00 NULL
11 Transactio 0 0 0 00 NULL
12 VerifyCook 0 1 0 00 NULL
13 TableLock 0 2 0 person 00 NULL
14 Goto 0 2 0 00 NULL
And the table for the second query looks like this:
第二个查询的表如下所示：
sqlite> explain select 0, t0.id, t0.firstname, t0.lastname from person t0;
addr opcode p1 p2 p3 p4 p5 comment
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 Trace 0 0 0 00 NULL
1 Goto 0 12 0 00 NULL
2 OpenRead 0 2 0 3 00 NULL
3 Rewind 0 10 0 00 NULL
4 Integer 0 1 0 00 NULL
5 Column 0 0 2 00 NULL
6 Column 0 1 3 00 NULL
7 Column 0 2 4 00 NULL
8 ResultRow 1 4 0 00 NULL
9 Next 0 4 0 01 NULL
10 Close 0 0 0 00 NULL
11 Halt 0 0 0 00 NULL
12 Transactio 0 0 0 00 NULL
13 VerifyCook 0 1 0 00 NULL
14 TableLock 0 2 0 person 00 NULL
15 Goto 0 2 0 00 NULL
回答by Christian Kienle
Some frameworks do this in order to tell, without any doubt, whether a row from that table was returned.
一些框架这样做是为了毫无疑问地判断是否返回了该表中的一行。
Consider
考虑
A B
+---+ +---+------+
| a | | a | b |
+---+ +---+------+
| 0 | | 0 | 1 |
+---+ +---+------+
| 1 | | 1 | NULL |
+---+ +---+------+
| 2 |
+---+
SELECT A.a, B.b
FROM A
LEFT JOIN B
ON B.a = A.a
Results
+---+------+
| a | b |
+---+------+
| 0 | 1 |
+---+------+
| 1 | NULL |
+---+------+
| 2 | NULL |
+---+------+
In this result set, it is not possible to see that a = 1exists in table B, but a = 2does not. To get that information, you need to select a non-nullable expression from table b, and the simplest way to do that is to select a simple constant value.
在这个结果集中，不可能看到a = 1表 B中存在但a = 2不存在。要获取该信息，您需要从表 b 中选择一个不可为空的表达式，而最简单的方法是选择一个简单的常量值。
SELECT A.a, B.x, B.b
FROM A
LEFT JOIN (SELECT 0 AS x, B.a, B.b FROM B) AS B
ON B.a = A.a
Results
+---+------+------+
| a | x | b |
+---+------+------+
| 0 | 0 | 1 |
+---+------+------+
| 1 | 0 | NULL |
+---+------+------+
| 2 | NULL | NULL |
+---+------+------+
There are a lot of situations where these constant values are not strictly required, for example when you have no joins, or when you could choose a non-nullable column from b instead, but they don't cause any harm either, so they can just be included unconditionally.
在很多情况下，这些常量值并不是严格要求的，例如当您没有连接时，或者当您可以从 b 中选择不可为空的列时，但它们也不会造成任何伤害，因此它们可以只是无条件地被包括在内。
回答by Glenn
When I have code to dynamically generate a WHEREclause, I usually start the clause with a:
当我有动态生成WHERE子句的代码时，我通常以以下内容开头子句：
WHERE 1 = 1
Then the loop to add additional conditions always adds each condition in the same format:
然后添加附加条件的循环总是以相同的格式添加每个条件：
AND x = y
without having to put conditional logic in place to check if this is the first condition or not: "if this is the first condition then start with the WHEREkeyword, else add the ANDkeyword.
无需放置条件逻辑来检查这是否是第一个条件：“如果这是第一个条件，则从WHERE关键字开始，否则添加AND关键字。
So I can imagine a framework doing this for similar reasons. If you start the statement with a SELECT 0then the code to add subsequent columns can be in a loop without any conditional statements. Just add , colxeach time without any conditional checking along the lines of "if this is the first column, don't put a comma before the column name, otherwise do".
所以我可以想象一个框架出于类似的原因这样做。如果以 a 开头语句，SELECT 0则添加后续列的代码可以在没有任何条件语句的循环中。只需, colx按照“如果这是第一列，不要在列名前放逗号，否则做”这样的行添加每次而不进行任何条件检查。
Example pseudo code:
示例伪代码：
String query = "SELECT 0";
for (Column col in columnList)
query += ", col";
回答by CL.
Only Apple knows … but I see two possibilities:
只有苹果知道……但我看到了两种可能性：
Inserting a dummy column ensures that the actualoutput columns are numbered beginning with 1, not 0. If some existing interface already assumed one-based numbering, doing it this way in the SQL backend might have been the easiest solution.
If you make a query for multiple objects using multiple subqueries, a value like this could be used to determine from which subquery a record originates:
SELECT 0, t0.firstname, ... FROM PERSON t0 WHERE t0.id = 123 UNION ALL SELECT 1, t0.firstname, ... FROM PERSON t0 WHERE t0.id = 456(I don't know if Core Data actually does this.)
插入一个虚拟列可确保实际输出列从 1 开始编号，而不是从 0 开始。如果某些现有接口已经采用基于 1 的编号，那么在 SQL 后端这样做可能是最简单的解决方案。
如果您使用多个子查询查询多个对象，则可以使用这样的值来确定记录源自哪个子查询：
SELECT 0, t0.firstname, ... FROM PERSON t0 WHERE t0.id = 123 UNION ALL SELECT 1, t0.firstname, ... FROM PERSON t0 WHERE t0.id = 456（我不知道 Core Data 是否真的这样做了。）
Your EXPLAINoutput shows that the only difference is (at address 4) that the second program sets the extra column to zero, so there is only a minimal performance difference.
您的EXPLAIN输出显示唯一的区别是（在地址 4 处）第二个程序将额外的列设置为零，因此性能差异很小。
