在MySQL中使用视图的优势 [英] Advantage of using Views in MySQL

问题描述

我了解到，可以使用视图来创建自定义的表视图"(可以这么说)，以聚合来自多个表的相关数据.

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

现在，如果我想在某个团队中允许某个event成员的最大数量，我可以:

Now if I want to maximum number of members allowed in a team for some event, I could:

• 使用视图
• 执行联接查询(或可能是存储过程).

每种方法的优点/缺点是什么?

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)?

推荐答案

视图不是单独存储的:查询视图时，该视图将替换为该视图的定义.因此，对表中数据的更改将立即通过视图显示.

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:

如果您要编写大量执行连接的查询，则会排除该SQL代码.就像在多个地方使用的函数中执行某些操作一样，它可以使您的代码更易于读取/编写/调试.

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.

它还使您可以在一处更改将来执行联接的方式.一对多关系可能会变成多对多关系，从而在联接中引入一个额外的表.或者，您可以决定对所有事件类型字段进行非规范化并将其包括在每个事件记录中，以使您不必每次都联接(查询执行时间的交易空间).

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).

稍后您可以进一步拆分表，将其更改为3向联接，并且不必重写使用该视图的其他查询.

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.

您可以将新列添加到表中，并更改视图以保留新列，以便在更改表定义时使用"select *"的某些较旧的查询不会中断.

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.

这篇关于在MySQL中使用视图的优势的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！