在 MySQL 中使用视图的优势
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2054980/
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
Advantage of using Views in MySQL
提问by jrharshath
I've learned that views can be used to create custom "table views" (so to say) that aggregate related data from multiple tables.
我了解到视图可用于创建自定义“表视图”（可以这么说），从多个表聚合相关数据。
My question is: what are the advantages of views? Specifically, let's say I have two tables:
我的问题是：视图的优点是什么？具体来说，假设我有两个表：
event | eid, typeid, name
eventtype | typeid, max_team_members
Now I create a view:
现在我创建一个视图：
eventdetails | event.eid, event.name, eventtype.max_team_members
| where event.typeid=eventtype.typeid
Now if I want to maximum number of members allowed in a team for some event, I could:
现在，如果我想在一个团队中允许某些成员的最大数量event，我可以：
- use the view
- do a join query (or maybe a stored procedure).
- 使用视图
- 做一个连接查询（或者可能是一个存储过程）。
What would be my advantages/disadvantages in each method?
我在每种方法中的优点/缺点是什么？
Another query: if data in table events and eventtypes gets updated, is there any overhead involved in updating the data in the view (considering it caches resultant data)?
另一个查询：如果表事件和事件类型中的数据被更新，更新视图中的数据是否有任何开销（考虑到它缓存结果数据）？
采纳答案by Harold L
A view is not stored separately: when you query a view, the view is replaced with the definition of that view. So and changes to the data in the tables will show up immediately via the view.
视图不单独存储：当您查询视图时，视图将替换为该视图的定义。因此，对表中数据的更改将立即通过视图显示出来。
In addition to the security feature pointed out earlier:
除了前面指出的安全功能：
If you're writing a large number of queries that would perform that join, it factors out that SQL code. Like doing some operations in a function used in several places, it can make your code easier to read/write/debug.
如果您正在编写大量将执行该连接的查询，则会考虑该 SQL 代码。就像在多个地方使用的函数中进行一些操作一样，它可以使您的代码更易于读/写/调试。
It would also allow you to change how the join is performed in the future in one place. Perhaps a 1-to-many relationship could become a many-to-many relationship, introducing an extra table in the join. Or you may decide to denormalize and include all of the eventtype fields in each event record so that you don't have to join each time (trading space for query execution time).
它还允许您在一个地方更改将来执行联接的方式。也许一对多关系可以变成多对多关系，在连接中引入一个额外的表。或者您可能决定非规范化并在每个事件记录中包含所有 eventtype 字段，以便您不必每次都加入（查询执行时间的交易空间）。
You could further split tables later, changing it to a 3-way join, and other queries using the view wouldn't have to be rewritten.
您可以稍后进一步拆分表，将其更改为 3 向联接，并且不必重写使用该视图的其他查询。
You could add new columns to the table(s) and change the view to leave out the new columns so that some older queries using "select *" don't break when you change the table definitions.
您可以向表中添加新列并更改视图以忽略新列，以便在更改表定义时使用“select *”的一些旧查询不会中断。
回答by Ignacio Vazquez-Abrams
You can restrict users to the view instead of the underlying table(s), thereby enhancing security.
您可以将用户限制为视图而不是基础表，从而增强安全性。
