MySQL视图如何工作? [英] How do MySQL views work?

问题描述

创建视图时，我基本上是在创建一个新表，当其中一个表中的数据发生变化时，该表将自动进行处理.正确吗?

When I create a view I am basically making a new table that will automatically be transacted upon when data in one of the tables it joins changes; is that correct?

为什么我不能在视图中使用子查询?

Also why can't I use subqueries in my view?

推荐答案

视图的工作方式类似于表，但它不是表.它永远不存在；它只是在引用视图名称时运行的一条准备好的SQL语句. IE浏览器:

A view works like a table, but it is not a table. It never exists; it is only a prepared SQL statement that is run when you reference the view name. IE:

CREATE VIEW foo AS
  SELECT * FROM bar

SELECT * FROM foo

...相当于运行:

SELECT x.* 
  FROM (SELECT * FROM bar) x

MySQLDump将永远不会包含要插入视图中的行...

A MySQLDump will never contain rows to be inserted into a view...

为什么我不能在我的视图中使用子查询??

Also why can't I use subqueries in my view????

可悲的是，这是出于(尽管是有问题的)设计. MySQL视图有很多限制，已记录在案: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

That, sadly, is by (albeit questionable) design. There's numerous limitations for MySQL views, which are documented: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

号
一个表可以具有关联的索引，这可以使数据检索更快(在插入/更新时要付出一定的代价).某些数据库支持物化"视图，这些视图可以对其应用索引-考虑到有限的视图功能(仅在v5中开始)，MySQL 不支持不足为奇IIRC，比赛进行得很晚).

No.
A table can have indexes associated, which can make data retrieval faster (at some cost for insert/update). Some databases support "materialized" views, which are views that can have indexes applied to them - which shouldn't be a surprise that MySQL doesn't support given the limited view functionality (which only began in v5 IIRC, very late to the game).

由于视图是派生表，因此视图的性能仅与基于其构建的查询一样好.如果该查询很烂，性能问题将只是滚雪球……也就是说，在查询视图时-如果WHERE子句中的视图列引用未包装在函数中(即:WHERE v.column LIKE ...，不是 WHERE LOWER(t.column) LIKE ...)，优化器可以将条件(称为谓词)推送到原始查询中-使其更快.

Because a view is a derived table, the performance of the view is only as good as the query it is built on. If that query sucks, the performance issue will just snowball... That said, when querying a view - if a view column reference in the WHERE clause is not wrapped in a function (IE: WHERE v.column LIKE ..., not WHERE LOWER(t.column) LIKE ...), the optimizer may push the criteria (called a predicate) onto the original query - making it faster.

这篇关于MySQL视图如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！