Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

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`)
) 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 !!!

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
add comment

Your Answer

 
discard

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.