当前位置：文江博客话题详情

MySQL 视图如何工作？

发布于 2024-09-02 11:53:17 字数 97 浏览 10 评论 0原文

当我创建一个视图时，我基本上是在创建一个新表，当它所连接的表之一中的数据发生变化时，该表将自动进行事务处理；这是正确的吗？

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

神回复 2024-09-09 11:53:17

视图的工作方式类似于表，但它不是表。它从来不存在；它只是在引用视图名称时运行的准备好的 SQL 语句。 IE：

CREATE VIEW foo AS
  SELECT * FROM bar

SELECT * FROM foo

...相当于运行：

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

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

另外为什么我不能在视图中使用子查询？？？

可悲的是，这是（尽管有问题）设计的。 MySQL 视图有许多限制，已记录在案： http:// dev.mysql.com/doc/refman/5.0/en/create-view.html

因此，如果它只是一个虚构的表/准备好的语句，这是否意味着它理论上具有与正常情况相同的性能（甚至更低）表/查询？

编号
表可以具有关联的索引，这可以使数据检索更快（插入/更新会付出一定的代价）。一些数据库支持“物化”视图，这些视图可以应用索引 - 考虑到有限的视图功能（仅从 v5 开始），MySQL 不支持也就不足为奇了IIRC，比赛已经很晚了）。

由于视图是派生表，因此视图的性能取决于其构建的查询。如果该查询很糟糕，性能问题就会滚雪球......也就是说，在查询视图时 - 如果 WHERE 子句中的视图列引用未包装在函数中（IE：WHERE v.column LIKE .. .，不是 WHERE LOWER(t.column) LIKE ...），优化器可以将条件（称为谓词）推送到原始查询上 -使其更快。

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

...is equivalent to running:

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

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

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

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 support given the limited view functionality (which only began in v5 IIRC, very late to the game).

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.

回复收藏 0 原文

月隐月明月朦胧 2024-09-09 11:53:17

我也遇到了同样的问题（令我惊讶的是，因为我的搜索似乎表明 Oracle 和 MS 确实支持它）。

我通过为最终视图创建两个附加视图来绕过此限制（至少目前如此，直到证明不可用为止）。

示例：

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

上面的示例基本上有一个表“t”，它是包含所有修订的临时表。我的“Foo”（视图）基本上是一个简单的视图，仅包含我对每条记录的最新修订。目前看来工作正常！

更新：

我不知道这是否是 MySQL 5.1 中的另一个错误，但上面的示例实际上不起作用！ “Foo1”按预期工作，但“Foo2”似乎忽略了分组之前的顺序，因此我的最终结果不是预期的结果。如果我将“DESC”更改为“ASC”，我什至会得到相同的结果（令人惊讶）。

另外，如果您阅读17.5.1。查看语法部分，它明确指出：

“视图可以从多种 SELECT 语句创建。它可以引用基表或其他视图。它可以使用联接、UNION 和子查询。”

我将把我的数据库更新到 5.6，然后再试一次！

I ran into the same problem also (to my surprise, because my search seems to indicate that Oracle and MS do support it).

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!

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

Also, if you read the 17.5.1. View Syntax section, it clearly states:

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

I'm going to update my database to 5.6 and try it again!

回复收藏 0 原文

楠木可依 2024-09-09 11:53:17

区别在于：

对于视图，您只能在 where 部分中有子查询，而不能在 from 部分中有子查询，因此 a

CREATE VIEW v AS SELECT * FROM foo WHERE id IN (SELECT id FROM bar)

可以工作，但同时您会得到一个只读视图...单个表上的简单视图将允许“通过”视图更新到基础表

The difference is :

for view you can only have subqueries in the where - part, not in the from - part so a

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

回复收藏 0 原文

~没有更多了~