1

In MYSQL for creating VIEWs

If I write the SELECT * FROM ... as a VIEW will the view store it forever as * or will it actually choose the current columns and store the query as that (as in instead of storing SELECT * it will store SELECT foo,bar,biz etc.)

I have read multiple times that using SELECT * FROM ... is bad practice for various reasons including those mentioned HERE and HERE. So i do wish to avoid those problems.

Ali Azam
  • 2,047
  • 1
  • 16
  • 25
electricjelly
  • 366
  • 1
  • 2
  • 14

1 Answers1

2

Creating stored views in MySQL can be fraught with unintended performance consequences. Consider carefully whether creating a stored view is an appropriate solution to the problem. Or whether creating a view is potentially creating a problem that is larger than the problem it was implemented to solve.

The question you asked could be answered with a simple test case, assuming table test.foo exists...

 USE test ; 

 CREATE VIEW voo AS SELECT * FROM foo ;

 SHOW CREATE VIEW voo ; 

 DROP VIEW voo ;
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thank you for the response I was not aware that there is such a thing as view that is not stored, and this may make me rewrite my question. Is a stored view different than a simply creating a view in phpmyadmin for example? – electricjelly Nov 28 '17 at 23:13
  • A *stored* view as opposed to an *inline* view. In the MySQL parlance, an inline view is referred to as a "derived table", e.g. **`SELECT * FROM (SELECT * FROM foo) voo`.** The bit between the parens there is an "inline view", assigned an alias "voo". We could create a *stored* view definition (as the example in the answer), and then `SELECT * FROM voo` to the same net effect (and performance issues). The difference is that in one, the definition of `voo` exists *inline* in the query, and is only available in that context. A *stored* view is available in other contexts. – spencer7593 Nov 28 '17 at 23:27