Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Like other RDBMS, view meta data is stored at view creation time.

From CREATE VIEW in the MySQL docs

The view definition is “frozen” at creation time, so changes to the underlying tables afterward [sic] do not affect the view definition.

I can't see a way to refresh this view meta data cleanly, unlike other RDBMS:

  • SQL Server: EXEC sp_refreshview 'MyView'

The system here appears to be "do this manually" with DROP/CREATE which isn't my style. The usual crowd who do this manual process are on leave so it falls to me this week.

I don't have a minion to nominate either: so any solutions please...

share|improve this question
up vote 1 down vote accepted

To see the view's definition, run one of the following:

  • SHOW CREATE VIEW viewname\G
  • SELECT * from information_schema.views where table_name='viewname'\G

Here is a quick example:

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `age` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
1 row in set (0.00 sec)

mysql> select * from mytable;
| id | age |
|  1 |  10 |
|  2 |  15 |
|  3 |  20 |
|  4 |   5 |
4 rows in set (0.00 sec)

mysql> create view mytable_tens as select * from mytable where MOD(age,10) = 0;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from mytable_tens;
| id | age |
|  1 |  10 |
|  3 |  20 |
2 rows in set (0.00 sec)

mysql> show create view mytable_tens\G
*************************** 1. row ***************************
                View: mytable_tens
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `mytable_tens` AS select `mytable`.`id` AS `id`,`mytable`.`age` AS `age` from `mytable` where ((`mytable`.`age` % 10) = 0)
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> select * from information_schema.views where table_name='mytable_tens'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: johnlocke
          TABLE_NAME: mytable_tens
     VIEW_DEFINITION: select `johnlocke`.`mytable`.`id` AS `id`,`johnlocke`.`mytable`.`age` AS `age` from `johnlocke`.`mytable` where ((`johnlocke`.`mytable`.`age` % 10) = 0)
             DEFINER: @
COLLATION_CONNECTION: latin1_swedish_ci
1 row in set (0.02 sec)

Whichever method you choose, you can visibly see the query making up the view. You can sculpt the CREATE OR REPLACE VIEW using whatever is shown.

Hope this helps, and Welcome to the DBA MinionExchange !!!

share|improve this answer
Thanks, but this isn't "clean". Sigh. – gbn Dec 23 '11 at 18:10
@gbn : I hear ya. Even as a MySQL DBA, I can easily say that MySQL is not ANSI compliant, or anything compliant for that matter. Maybe this should go in Meta as "What Constitutes Proper Database Administration?" – RolandoMySQLDBA Dec 23 '11 at 18:18
Ah well, I'll have a minion work on this ;-) Cheers – gbn Jan 3 '12 at 9:50
>Rolando, what's that "\G" at the end of each query you wrote? – Rafa Jun 30 '12 at 12:28

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.