如何获取 MySQL 视图列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2834016/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get a list of MySQL views?
提问by AnnanFay
I'm looking for a way to list all views in a database.
我正在寻找一种方法来列出数据库中的所有视图。
Initially I found and tried an answer on the MySQL forums:
最初我在 MySQL 论坛上找到并尝试了一个答案：
SELECT table_name
FROM information_schema.views
WHERE information_schema.views.table_schema LIKE 'view%';
How ever this doesn't work, returning an empty set. (I know they're in there!)
这怎么行不通，返回一个空集。（我知道他们在那里！）
These also fail:
这些也失败了：
mysql> use information_schema;
Database changed
mysql> select * from views;
ERROR 1102 (42000): Incorrect database name 'mysql.bak'
mysql> select * from tables;
ERROR 1102 (42000): Incorrect database name 'mysql.bak'
Why isn't this working?
为什么这不起作用？
回答by
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
回答by Valerie Parham-Thompson
Here's a way to find all the views in everydatabase on your instance:
这是一种在您的实例上的每个数据库中查找所有视图的方法：
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_TYPE LIKE 'VIEW';
回答by zloctb
select * FROM information_schema.views\G;
回答by Sameera Sampath
This will work.
这将起作用。
USE INFORMATION_SCHEMA;
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_TYPE LIKE 'VIEW';
回答by Manuel Jordan
To complementabout to get more info about a specific view
为了配合即将获得有关特定视图更多信息
Even with the two valid answers
即使有两个有效的答案
SHOW FULL TABLES IN your_db_name WHERE TABLE_TYPE LIKE 'VIEW';
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'your_db_name';
You can apply the following (I think is better):
您可以应用以下内容（我认为更好）：
SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA LIKE 'your_db_name';
is better work directly with information_schema.VIEWS(observe now is VIEWSand not TABLESanymore), thus you can retrieve more data, use DESC VIEWSfor more details:
更好地直接使用information_schema.VIEWS（现在观察是VIEWS而不是TABLES），因此您可以检索更多数据，DESC VIEWS用于更多详细信息：
+----------------------+---------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(64) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | YES | | NULL | |
| TABLE_NAME | varchar(64) | YES | | NULL | |
| VIEW_DEFINITION | longtext | YES | | NULL | |
| CHECK_OPTION | enum('NONE','LOCAL','CASCADED') | YES | | NULL | |
| IS_UPDATABLE | enum('NO','YES') | YES | | NULL | |
| DEFINER | varchar(93) | YES | | NULL | |
| SECURITY_TYPE | varchar(7) | YES | | NULL | |
| CHARACTER_SET_CLIENT | varchar(64) | NO | | NULL | |
| COLLATION_CONNECTION | varchar(64) | NO | | NULL | |
+----------------------+---------------------------------+------+-----+---------+-------+
For example observe the VIEW_DEFINITIONfield, thus you can use in action:
例如观察该VIEW_DEFINITION字段，因此您可以在操作中使用：
SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA LIKE 'your_db_name';
Of course you have more fields available for your consideration.
当然，您还有更多可用字段供您考虑。
回答by Bill Rios
Try moving that mysql.bakdirectory out of /var/lib/mysqlto say /root/or something. It seems like mysql is finding that and it may be causing that ERROR 1102 (42000): Incorrect database name 'mysql.bak'error.
尝试将该mysql.bak目录移出/var/lib/mysql说/root/或某事。似乎 mysql 正在发现它，它可能会导致该ERROR 1102 (42000): Incorrect database name 'mysql.bak'错误。
回答by Martin Hilton
The error your seeing is probably due to a non-MySQL created directory in MySQL's data directory. MySQL maps the database structure pretty directly onto the file system, databases are mapped to directories and tables are files in those directories.
您看到的错误可能是由于在 MySQL 的数据目录中创建了非 MySQL 目录。MySQL 将数据库结构直接映射到文件系统上，数据库映射到目录，表是这些目录中的文件。
The name of the non-working database looks suspiciously like someone has copied the mysql database directory to a backup at some point and left it in MySQL's data directory. This isn't a problem as long as you don't try and use the database for anything. Unfortunately the information schema scans all of the databases it finds and finds that this one isn't a real database and gets upset.
非工作数据库的名称看起来很可疑，就像有人在某个时候将 mysql 数据库目录复制到备份并将其留在 MySQL 的数据目录中。只要您不尝试将数据库用于任何目的，这不是问题。不幸的是，信息模式扫描了它找到的所有数据库，并发现这不是一个真正的数据库，因此感到不安。
The solution is to find the mysql.bak directory on the hard disk and move it well away from MySQL.
解决办法是在硬盘上找到mysql.bak目录，把它从MySQL移开。
回答by Thanh Nguyen
Another way to find all View:
查找所有视图的另一种方法：
SELECT DISTINCT table_name FROM information_schema.TABLES WHERE table_type = 'VIEW'
SELECT DISTINCT table_name FROM information_schema.TABLES WHERE table_type = 'VIEW'
回答by Shiv kumar ojha
If you created any view in Mysql databases then you can simply see it as you see your all tables in your particular database.
如果您在 Mysql 数据库中创建了任何视图，那么您可以像查看特定数据库中的所有表一样简单地查看它。
write:
写：
--mysql> SHOW TABLES;
you will see list of tables and views of your database.
您将看到数据库的表和视图列表。
