mysql 查询全部视图_mysql中怎么查看所有的视图名？

本帖最后由 jan_1985 于 2014-2-12 16:49 编辑

当前用户有权限查看的整个数据库的视图的名称与定义语句都会列出来。

mysql> select * from information_schema.VIEWS;

+---------------+--------------+------------+------------------------------------------------------------------------+--------------+--------------+----------+---------------+----------------------+----------------------+

| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION                                                        | CHECK_OPTION | IS_UPDATABLE | DEFINER  | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |

+---------------+--------------+------------+------------------------------------------------------------------------+--------------+--------------+----------+---------------+----------------------+----------------------+

| NULL          | test         | v          | select `test`.`a`.`id` AS `id`,`test`.`a`.`bd` AS `t2` from `test`.`a` | NONE         | YES          | root@% | DEFINER       | utf8                 | utf8_general_ci      |

+---------------+--------------+------------+------------------------------------------------------------------------+--------------+--------------+----------+---------------+----------------------+----------------------+

1 row in set (0.02 sec)

show create view v; 和show create table v; 效果一样

mysql> show create table v;

+------+------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

| View | Create View                                                                                                                        | character_set_client | collation_connection |

+------+------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

| v    | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v` AS select `a`.`id` AS `id`,`a`.`bd` AS `t2` from `a` | utf8                 | utf8_general_ci      |

+------+------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

1 row in set (0.00 sec)