Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
225 views
in Technique[技术] by (71.8m points)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...