为什么使用“*”构建视图不正确?

我不认为软件中有太多“只是坏的”,但是有很多东西被错误地使用了。

你给出的例子就是为什么*不能给出你所期望的,我认为还有其他的。例如,如果基础表发生更改,可能会添加或删除列,则使用*的视图将继续有效,但可能会中断使用它的任何应用程序。如果您的视图已经显式地命名了列,那么在进行模式更改时,有人更有可能发现问题。

另一方面,你可能 希望 你的观点 接受对基础表的所有更改,在这种情况下,*将 做你想要的。

更新: 我不知道OP是否考虑了一个特定的数据库供应商,但现在很明显,我最后一句话对所有类型都不适用。我感谢user12861和jonny leeds指出这一点,很抱歉我花了6年时间来编辑我的答案。

尽管这里的许多注释都非常好,并且引用了在查询中使用通配符的一个常见问题,例如,如果基础表发生更改,则会导致错误或产生不同的结果,但还没有涉及的另一个问题是优化。提取表中每一列的查询往往不如只提取实际需要的列的查询效率高。当然,有时你需要每一列,这是一个主要的PIA必须引用所有列,尤其是在一个大表中,但是如果你只需要一个子集,为什么要用比你需要的更多的列来阻塞你的查询呢?

另一个原因是” * “不仅在视图中,而且在查询中,风险在于列可以更改基础表中的名称或位置。使用通配符意味着您的视图可以轻松地适应这些更改,而无需进行更改。但是,如果应用程序在结果集中按位置引用列,或者使用返回列名称键控的结果集的动态语言,则可能会遇到难以调试的问题。

我总是避免使用通配符。这样,如果一个列更改了名称,我会立即在视图或查询中得到一个错误,并且知道在哪里修复它。如果列更改了基础表中的位置,那么在视图或查询中指定列的顺序就可以弥补这一点。

这些其他答案都有好的地方,但在SQL Server上,至少它们也有一些错误的地方。试试这个:

create table temp (i int, j int)
go
create view vtemp as select * from temp
go
insert temp select 1, 1
go
alter table temp add k int
go
insert temp select 1, 1, 1
go
select * from vtemp

添加“new”列时,SQL Server不会了解该列。取决于你想要什么,这可能是一件好事,也可能是一件坏事,但不管怎样,依赖它都可能是不好的。所以,避免这样做似乎是个好主意。

对我来说,这种奇怪的行为是避免在视图中选择*的最令人信服的原因。

这些评论告诉我,MySQL有类似的行为,而Oracle没有(它将了解表的更改)。对我来说,这种不一致性更是不在视图中使用select*的原因。

对任何产品使用“*”都是不好的。它非常适合一次性查询,但是在生产代码中,您应该尽可能地明确。

尤其是对于视图,如果基础表中添加或删除了列,那么在重新编译之前,视图要么是错误的,要么是中断的。

使用 SELECT * 如果不在视图外部使用列,则视图内部不会产生很大的性能开销-优化器将优化它们; SELECT * FROM TheView 可能会浪费带宽,就像您在网络连接上拉更多的列一样。

事实上,我发现在我的数据仓库中,链接几乎所有列的视图根本没有引入任何性能问题,即使通过这些列中相对较少的列从视图外部请求。优化器处理得很好,能够很好地将外部筛选条件下推到视图中。

然而,由于上述所有原因,我很少使用 选择* .

我有一些业务流程,其中许多CTE是建立在彼此之上的,有效地从派生列的派生列构建派生列(希望有一天,随着业务的合理化和简化这些计算,这些派生列将被重构),在这种情况下,我需要每次都删除所有列,并且我使用 选择* -但是 选择* 不用于基层,仅用于第一个CTE和最后一个CTE之间。

实际上,SQL Server上的情况比@user12861给出的答案还要糟糕:如果使用 SELECT * 对于多个表,向查询早期引用的表中添加列实际上会导致视图以旧列的形式返回新列的值。见下例:

-- create two tables
CREATE TABLE temp1 (ColumnA INT, ColumnB DATE, ColumnC DECIMAL(2,1))
CREATE TABLE temp2 (ColumnX INT, ColumnY DATE, ColumnZ DECIMAL(2,1))
GO


-- populate with dummy data
INSERT INTO temp1 (ColumnA, ColumnB, ColumnC) VALUES (1, '1/1/1900', 0.5)
INSERT INTO temp2 (ColumnX, ColumnY, ColumnZ) VALUES (1, '1/1/1900', 0.5)
GO


-- create a view with a pair of SELECT * statements
CREATE VIEW vwtemp AS 
SELECT *
FROM temp1 INNER JOIN temp2 ON 1=1
GO


-- SELECT showing the columns properly assigned
SELECT * FROM vwTemp 
GO


-- add a few columns to the first table referenced in the SELECT 
ALTER TABLE temp1 ADD ColumnD varchar(1)
ALTER TABLE temp1 ADD ColumnE varchar(1)
ALTER TABLE temp1 ADD ColumnF varchar(1)
GO


-- populate those columns with dummy data
UPDATE temp1 SET ColumnD = 'D', ColumnE = 'E', ColumnF = 'F'
GO


-- notice that the original columns have the wrong data in them now, causing any datatype-specific queries (e.g., arithmetic, dateadd, etc.) to fail
SELECT *
FROM vwtemp
GO

-- clean up
DROP VIEW vwTemp
DROP TABLE temp2
DROP TABLE temp1

这是因为你并不总是需要每一个变量,也要确保你在考虑你具体需要什么。

例如,在您的站点上构建用户列表时,没有必要将所有哈希密码从数据库中删除,因此select*将不会产生任何效果。

有一次,我使用

Select * From dbname..tablename

然后有一天,一列被添加到目标表中。视图开始返回完全不正确的结果,直到重新部署。

完全错误:没有行。

它在SQL Server 2000上。

我推测这是因为视图捕获了syscolumns值,即使我使用了*。

SQL查询基本上是由程序员设计的功能单元,用于某些上下文。为了长期的稳定性和可支持性(可能是由你以外的其他人),功能单元中的所有内容都应该是有目的的,并且应该合理地证明(或记录)为什么它存在——尤其是数据的每个元素。

如果两年后我想改变你的问题,我希望在我确信我能解决它之前,能彻底地摸索一下。这意味着我需要理解为什么所有的列都被调用。(如果您试图在多个上下文中重用查询,则更明显是这样。这在一般情况下是有问题的,原因是相似的。)如果我在输出中看到与某些目的不相关的列,我肯定我不知道它做了什么,为什么,以及改变它的后果。

使用*通常是个坏主意。一些代码认证引擎将此标记为警告,并建议您只显式地引用必要的列。使用*会导致性能下降,因为您可能只需要一些列,而不是全部列。但是,另一方面,有些情况下使用*是理想的。想象一下,无论使用您提供的示例,对于这个视图(aview),您总是需要这些表中的所有列。将来,添加列时,不需要更改视图。这可能是好的也可能是坏的,这取决于你正在处理的情况。

我认为这取决于你使用的语言。当语言或数据库驱动程序返回结果的dict(python、perl等)或关联数组(php)时,我更喜欢使用select*。如果您是按名称而不是作为数组中的索引引用列,这会使代码更容易理解。

似乎没有其他人提到过它,但在SQL Server中,您还可以使用 schemabinding 属性。

这样可以防止对任何基表的修改(包括删除基表)影响视图定义。

在某些情况下,这可能对您有用。我知道我还没有完全回答你的问题,但我想我还是要强调一下。

如果使用select*进行连接,则自动意味着您返回的数据多于所需的数据,因为连接字段中的数据是重复的。这浪费了数据库和网络资源。

如果您天真地使用调用其他视图的视图,那么使用select*会使它们的性能更差(这是一种不利于性能的技术,调用不需要的多个列会使性能更差)。