为什么使用“*"来构建视图不好? [英] Why is using '*' to build a view bad?

问题描述

为什么使用*"来构建视图不好?

Why is using '*' to build a view bad ?

假设您有一个复杂的联接并且所有字段都可能在某处使用.

Suppose that you have a complex join and all fields may be used somewhere.

然后您只需选择所需的字段.

Then you just have to chose fields needed.

SELECT field1, field2 FROM aview WHERE ...

视图aview"可以是 SELECT table1.*, table2.* ... FROM table1 INNER JOIN table2 ...

The view "aview" could be SELECT table1.*, table2.* ... FROM table1 INNER JOIN table2 ...

如果 table1 和 table2 中有 2 个字段具有相同的名称，我们就会遇到问题.

We have a problem if 2 fields have the same name in table1 and table2.

这仅仅是在视图中使用*"不好的原因吗?

Is this only the reason why using '*' in a view is bad?

使用*"，您可以在不同的上下文中使用视图，因为信息就在那里.

With '*', you may use the view in a different context because the information is there.

我错过了什么?

问候

推荐答案

我不认为软件中有很多很糟糕"的东西，但是有很多东西被以不好的方式滥用了 :-)

I don't think there's much in software that is "just bad", but there's plenty of stuff that is misused in bad ways :-)

你给出的例子是 * 可能没有给你期望的原因，我认为还有其他原因.例如，如果基础表发生变化，可能会添加或删除列，使用 * 的视图将继续有效，但可能会破坏使用它的任何应用程序.如果您的视图显式命名了列，那么在更改架构时更有可能有人发现问题.

The example you give is a reason why * might not give you what you expect, and I think there are others. For example, if the underlying tables change, maybe columns are added or removed, a view that uses * will continue to be valid, but might break any applications that use it. If your view had named the columns explicitly then there was more chance that someone would spot the problem when making the schema change.

另一方面，你可能实际上希望你的观点愉快地接受对基础表的所有更改，在这种情况下 * 将成为你想要的.

On the other hand, you might actually want your view to blithely accept all changes to the underlying tables, in which case a * would be just what you want.

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

Update: I don't know if the OP had a specific database vendor in mind, but it is now clear that my last remark does not hold true for all types. I am indebted to user12861 and Jonny Leeds for pointing this out, and sorry it's taken over 6 years for me to edit my answer.

这篇关于为什么使用“*"来构建视图不好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！