在MySQL中查看元数据刷新

Question

与其他RDBMS一样，视图元数据存储在视图创建时。

从CREATE VIEW到MySQL文档

视图定义在创建时被“冻结”，因此对底层表的更改(随后的碳化硅 )不会影响视图定义。

与其他RDBMS不同，我找不到一种干净地刷新此视图元数据的方法：

• Server：EXEC sp_refreshview 'MyView'
• 甲骨文：ALTER VIEW MyView COMPILE

这里的系统似乎是用DROP/CREATE“手动完成”，这不是我的风格。通常做这个手工操作的人都在休假，所以这个星期我就该请假了。

我也没有奴才可以提名:所以任何解决方案都请.

Answer 1

若要查看视图的定义，请运行以下命令之一：

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

下面是一个简单的例子：

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)

无论您选择哪种方法，都可以明显地看到构成视图的查询。您可以使用显示的任何内容来雕刻创建或替换视图。

希望这有帮助，欢迎来到DBA MinionExchange！