View metadata refresh in MySQL

Question

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'
• Oracle: ALTER VIEW MyView COMPILE

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

Answer 1

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`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
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)
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: @
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: latin1
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 !!!