MySQL 如何获得MySQL视图的结构？

阅读更多：MySQL 教程

背景

在使用MySQL数据库的时候，我们经常需要查看表的结构以便进行数据操作。但是，有时候我们还需要查看视图的结构以便更好地理解数据库的设计和逻辑。然而，MySQL提供的SHOW命令并不能直接得到视图的结构信息。那么，应该如何获得MySQL视图的结构呢？

解决方案

一个可行的解决方案是使用INFORMATION_SCHEMA视图来获取MySQL视图的结构信息。在MySQL中，INFORMATION_SCHEMA是一个提供元数据信息的数据库。通过查询INFORMATION_SCHEMA视图，我们可以获得MySQL数据库中所有对象的结构信息，包括表、视图、存储过程等等。

具体来说，我们可以通过查询INFORMATION_SCHEMA.VIEWS视图来获得MySQL视图的结构信息。下面是一个示例代码：

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'mydatabase' AND TABLE_NAME = 'myview';

上述示例代码中，我们使用了SELECT语句查询了INFORMATION_SCHEMA.VIEWS视图，并通过WHERE条件过滤出了名为myview的视图在mydatabase数据库中的结构信息。可以看出，通过INFORMATION_SCHEMA视图获得MySQL视图的结构信息非常简单。

示例

为了更好地展示如何获得MySQL视图的结构信息，下面给出一个详细的示例代码。假设我们有一个名为person_view的视图，它的结构如下：

CREATE VIEW `person_view` AS
SELECT `person`.`id` AS `id`,`person`.`name` AS `name`,`person`.`age` AS `age`,`department`.`name` AS `department`
FROM (`person` JOIN `department` ON((`person`.`dep_id` = `department`.`id`)));

有了这个person_view视图后，我们就可以使用上文提到的SELECT语句，查询INFORMATION_SCHEMA.VIEWS视图来获取它的结构信息：

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'mydatabase' AND TABLE_NAME = 'person_view';

上述代码会返回如下结果：

+--------------+--------------+-----------------------------------------------+------+-----+---------------------+---------------------+---------------+--------+---------------------------------+--------------+----------------+-------------+---------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+--------------+-------------+-----------------+--------------+-----------------+---------------------+---------------------+--------------------+----------------+-------------------+-----------------+-------------------+-----------------------+---------------------+------------------------+-----------------+----------------+--------------+--------------------+----------------+--------------+---------------+-----------------------+----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                                    | VIEW | DEFINER | SECURITY_TYPE       | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | CHECK_OPTION | DEFINER_CHARACTER_SET | DESCRIPTION  | IS_UPDATABLE   | VIEW_DEFINITION | CHECKSUM      | CREATE_OPTIONS |        |                     |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |                | CREATED             | LAST_ALTERED |    SQL_MODE | DEFINER_ACCOUNT | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION | ALGORITHM | DEFINER_EXTERNAL_ROUTINE | SQL_MODE_ALTER | SQL_MODE_CREATE | SQL_MODE_DROP | SQL_MODE_INSERT | SQL_MODE_SELECT_INTO | SQL_MODE_VIEW | CHECK_OPTION | IS_UPDATABLE | IS_INSERTABLE | IS_DELETABLE | IS_DROP_OR_ALTERABLE |
+--------------+--------------+-----------------------------------------------+------+-----+---------------------+---------------------+---------------+--------+---------------------------------+--------------+----------------+-------------+---------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+--------------+-------------+-----------------+--------------+-----------------+---------------------+---------------------+--------------------+----------------+-------------------+-----------------+-------------------+-----------------------+---------------------+------------------------+-----------------+----------------+--------------+--------------------+----------------+--------------+---------------+-----------------------+----------------------+
| def          | mydatabase   | person_view                                   | NO   | root@localhost | DEFINER             | latin1              | latin1_swedish_ci     | NONE       | utf8                    |               |                | NULL            | 3096239992           |                | NULL... | `person_view` AS (`SELECT `person`.`id` AS `id`,`person`.`name` AS `name`,`person`.`age` AS `age`,`department`.`name` AS `department` FROM (`person` JOIN `department` ON((`person`.`dep_id` = `department`.`id`)))`) |     0 |
+--------------+--------------+-----------------------------------------------+------+-----+---------------------+---------------------+---------------+--------+---------------------------------+--------------+----------------+-------------+---------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+---------------------+--------------+-------------+-----------------+--------------+-----------------+---------------------+---------------------+--------------------+----------------+-------------------+-----------------+-------------------+-----------------------+---------------------+------------------------+-----------------+----------------+--------------+--------------------+----------------+--------------+---------------+-----------------------+----------------------+

可以看到，上述代码返回了一个包含person_view视图结构信息的结果集，包括视图所在的数据库、视图名称、视图定义语句等内容。通过对这些信息的分析，我们可以更加深入地了解MySQL视图的结构和设计。同样的方法，我们也可以获得其他MySQL视图的结构信息。