MySQL 视图如何工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2878227/
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
How do MySQL views work?
提问by John Nall
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?
另外为什么我不能在我的视图中使用子查询？
回答by OMG Ponies
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:
视图的工作方式类似于表格，但它不是表格。它从不存在；它只是在您引用视图名称时运行的准备好的 SQL 语句。IE：
CREATE VIEW foo AS
SELECT * FROM bar
SELECT * FROM foo
...is equivalent to running:
...相当于运行：
SELECT x.*
FROM (SELECT * FROM bar) x
A MySQLDump will never contain rows to be inserted into a view...
MySQLDump 永远不会包含要插入到视图中的行...
Also why can't I use subqueries in my view????
另外为什么我不能在我的视图中使用子查询？？？？
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
可悲的是，这是（尽管有问题）设计的。MySQL 视图有许多限制，记录在案：http: //dev.mysql.com/doc/refman/5.0/en/create-view.html
So if it's just an imaginary table/prepared statement does that mean it theoretically has the same performance (or even less) as a normal table/query?
因此，如果它只是一个虚构的表/准备好的语句，这是否意味着它在理论上具有与普通表/查询相同（甚至更低）的性能？
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 supportgiven the limited view functionality (which only began in v5 IIRC, very late to the game).
不可以。一个表可以有关联的索引，这可以使数据检索更快（插入/更新有一定的成本）。一些数据库支持“物化”视图，这些视图可以应用索引——鉴于有限的视图功能（仅在 v5 IIRC 中开始，游戏很晚），MySQL不支持这并不奇怪）。
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 ..., notWHERE LOWER(t.column) LIKE ...), the optimizer may push the criteria (called a predicate) onto the original query - making it faster.
因为视图是派生表，所以视图的性能取决于它所基于的查询。如果该查询很糟糕，性能问题就会滚雪球……也就是说，在查询视图时 - 如果 WHERE 子句中的视图列引用未包含在函数中（IE: WHERE v.column LIKE ..., notWHERE LOWER(t.column) LIKE ...），优化器可能会推送条件（称为谓词）到原始查询 - 使其更快。
回答by Jeach
I ran into the same problem also (to my surprise, because my search seems to indicate that Oracle and MS do support it).
我也遇到了同样的问题（令我惊讶的是，因为我的搜索似乎表明 Oracle 和 MS 确实支持它）。
I get around this limitation (at least for now, until proven non-usable) by creating two additional views for my final view.
我通过为我的最终视图创建两个额外的视图来绕过这个限制（至少现在，直到被证明不可用）。
Example:
例子：
CREATE VIEW Foo1 AS
SELECT * FROM t ORDER BY ID, InsertDate DESC
CREATE VIEW Foo2 AS
SELECT * FROM Foo1 GROUP BY ID
CREATE VIEW Foo AS
SELECT * FROM Foo2 ORDER BY ID
The example above basically has a table 't' which is a temporal table containing all the revisions. My 'Foo' (view) basically is a simple view of only my most current revisions of each record. Seems to work alright for now!
上面的例子基本上有一个表“t”，它是一个包含所有修订的临时表。我的“Foo”（视图）基本上只是我对每条记录的最新修订的简单视图。现在似乎工作正常！
Update:
更新：
I don't know if this is another bug in MySQL 5.1, but the above example doesn't in fact work! The 'Foo1' works as expected, but the 'Foo2' seems to ignore the order prior to grouping so my end result is not what is intended. I even get the same result if I change the 'DESC' for 'ASC' (surprisingly).
我不知道这是否是 MySQL 5.1 中的另一个错误，但上面的例子实际上不起作用！'Foo1' 按预期工作，但 'Foo2' 似乎忽略了分组之前的顺序，所以我的最终结果不是预期的。如果我将“DESC”更改为“ASC”（令人惊讶），我什至会得到相同的结果。
Also, if you read the 17.5.1. View Syntaxsection, it clearly states:
另外，如果您阅读了17.5.1。查看语法部分，它明确指出：
"A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries."
“可以从多种 SELECT 语句创建视图。它可以引用基表或其他视图。它可以使用连接、UNION 和子查询。”
I'm going to update my database to 5.6 and try it again!
我要把我的数据库更新到 5.6，然后再试一次！
回答by eagle275
The difference is :
区别在于：
for view you can only have subqueries in the where - part, not in the from - part so a
对于视图，您只能在 where - 部分中有子查询，而不能在 from - 部分中有子查询
CREATE VIEW v AS SELECT * FROM foo WHERE id IN (SELECT id FROM bar)
would work - but at the same time you get a read-only view ... A simple view on a single table would allow to update "through" the view to the underlying table
会工作 - 但同时你会得到一个只读视图......单个表上的简单视图将允许“通过”视图更新到基础表
