SQL 查询 - Select * from view 或 Select col1, col2, ... colN from view
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/128412/
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
SQL query - Select * from view or Select col1, col2, ... colN from view
提问by Napco
回答by devlord
NEVER, EVER USE "SELECT *"!!!!
永远，永远不要使用“选择 *”！！！！
This is the cardinal rule of query design!
这是查询设计的基本规则！
There are multiple reasons for this. One of which is, that if your table only has three fields on it and you use all three fields in the code that calls the query, there's a great possibility that you will be adding more fields to that table as the application grows, and if your select * query was only meant to return those 3 fields for the calling code, then you're pulling much more data from the database than you need.
这有多种原因。其中之一是，如果您的表上只有三个字段并且您在调用查询的代码中使用了所有三个字段，那么随着应用程序的增长，您很可能会向该表添加更多字段，如果您的 select * 查询只是为了返回调用代码的那 3 个字段，然后您从数据库中提取的数据比您需要的要多得多。
Another reason is performance. In query design, don't think about reusability as much as this mantra:
另一个原因是性能。在查询设计中，不要像这个口头禅那样考虑可重用性：
TAKE ALL YOU CAN EAT, BUT EAT ALL YOU TAKE.
吃多少就吃多少，但吃多少就吃多少。
回答by Gthompson83
It is best practice to select each column by name. In the future your DB schema might change to add columns that you would then not need for a particular query. I would recommend selecting each column by name.
最佳做法是按名称选择每一列。将来，您的数据库架构可能会更改以添加特定查询不需要的列。我建议按名称选择每一列。
回答by HLGEM
Select * is a poor programming practice. It is as likely to cause things to break as it is to save things from breaking. If you are only querying one table or view, then the efficiency gain may not be there (although it is possible if you are not intending to actually use every field). If you have an inner join, then you have at least two fields returning the same data (the join fields) and thus you are wasting network resources to send redundant data back to the application. You won't notice this at first, but as the result sets get larger and larger, you will soon have a network pipeline that is full and doesn't need to be. I can think of no instance where select * gains you anything. If a new column is added and you don't need to go to the code to do something with it, then the column shouldn't be returned by your query by definition. If someone drops and recreates the table with the columns in a different order, then all your queries will have information displaying wrong or will be giving bad results, such as putting the price into the part number field in a new record.
Select * 是一种糟糕的编程习惯。导致事物破裂的可能性与防止事物破裂的可能性一样。如果您只查询一个表或视图，那么效率增益可能不存在（尽管如果您不打算实际使用每个字段，这是可能的）。如果你有一个内部联接，那么你至少有两个字段返回相同的数据（联接字段），因此你浪费了网络资源来将冗余数据发送回应用程序。一开始您不会注意到这一点，但是随着结果集变得越来越大，您很快就会拥有一个已满且不需要的网络管道。我想不出 select * 能让你得到任何东西的例子。如果添加了一个新列并且您不需要转到代码对其进行处理，则该列应该' t 根据定义由您的查询返回。如果有人删除并以不同的顺序重新创建包含列的表，那么您的所有查询都会显示错误的信息或给出错误的结果，例如将价格放入新记录的零件编号字段中。
Plus it is quick to drag the column names over from the object browser, so that is just pure laziness not efficiency in coding.
另外，从对象浏览器中拖出列名是非常快速的，所以这只是纯粹的懒惰，而不是编码效率。
回答by CJM
Just to clarify a point that several people have already made, the reason Select * is inefficient is because there has to be an initial call to the DB to find out exactly what fields are available, and then a second call where the query is made using explicit columns.
只是为了澄清一些人已经提出的观点， Select * 效率低下的原因是因为必须对数据库进行初始调用以准确找出哪些字段可用，然后第二次调用使用显式列。
Feel free to use Select * when you are debugging, running casual queries or are in the early stages of developing a query, but as soon as you know your required columns, state them explicitly.
在调试、运行临时查询或处于开发查询的早期阶段时，请随意使用 Select *，但是一旦您知道所需的列，请明确说明它们。
回答by Simon
It depends. Inheritance of views can be a handy thing and easy to maintain (SQL Anywhere):
这取决于。视图的继承可以是一件方便的事情并且易于维护 (SQL Anywhere)：
create view v_fruit as select F.id, S.strain from F key join S;
create view v_apples as select v_fruit.*, C.colour from v_fruit key join C;
回答by Alireza Masali
select
column1
,column2
,column3
.
.
.
from Your-View
this one is more optimizer than Using the
这个比使用更优化
select *
from Your View
回答by Amy B
For performance - look at the query plan (should be no difference).
对于性能 - 查看查询计划（应该没有区别）。
For maintainability. - always supply a fieldlist (that goes for INSERT INTO too).
为了可维护性。- 始终提供一个字段列表（也适用于 INSERT INTO）。
回答by Adam Bellaire
If you're really selecting all columns, it shouldn't make any noticeable difference whether you ask for * or if you are explicit. The SQL server will parse the request the same way in pretty much the same amount of time.
如果您真的选择了所有列，那么无论您是要求 * 还是明确的，都不应该有任何明显的区别。SQL 服务器将在几乎相同的时间内以相同的方式解析请求。
回答by Mike McAllister
Always do select col1, col2 etc from view. There's no efficieny difference between the two methods that I know of, but using "select *" can be dangerous. If you modify your view definition adding new columns, you can break a program using "select *", whereas selecting a predefined set of columns (even all of them, named), will still work.
始终从视图中选择 col1、col2 等。我所知道的两种方法在效率上没有区别，但是使用“select *”可能很危险。如果您修改视图定义添加新列，您可以使用“select *”中断程序，而选择一组预定义的列（甚至所有列，已命名）仍然有效。
回答by Ken Ray
I guess it all depends on what the query optimizer does.
我想这完全取决于查询优化器的作用。
If I want to get every record in the row, I will generally use the "SELECT *..." option, since I then don't have to worry should I change the underlying table structure. As well, for someone maintaining the code, seeing "SELECT *" tells them that this query is intended to return every column, whereas listing the columns individually does not convey the same intention.
如果我想获取行中的每条记录，我通常会使用“SELECT *...”选项，因为这样我就不必担心是否更改基础表结构。同样，对于维护代码的人来说，看到“SELECT *”告诉他们这个查询旨在返回每一列，而单独列出列并没有传达相同的意图。
