在 MySQL 中使用 SELECT 语句获取表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8334493/
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
Get table names using SELECT statement in MySQL
提问by Mike Chamberlain
In MySQL, I know I can list the tables in a database with:
在 MySQL 中，我知道我可以列出数据库中的表：
SHOW TABLES
However, I want to insert these table names into another table, for instance:
但是，我想将这些表名插入到另一个表中，例如：
INSERT INTO metadata(table_name) SHOW TABLES /* does not work */
Is there a way to get the table names using a standard SELECT statement, something like:
有没有办法使用标准 SELECT 语句获取表名，例如：
INSERT INTO metadata(table_name) SELECT name FROM table_names /* what should table_names be? */
回答by Murilo Garcia
To get the name of all tables use:
要获取所有表的名称，请使用：
SELECT table_name FROM information_schema.tables;
To get the name of the tables from a specific database use:
要从特定数据库中获取表的名称，请使用：
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'your_database_name';
Now, to answer the original question, use this query:
现在，要回答原始问题，请使用以下查询：
INSERT INTO table_name
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'your_database_name';
For more details see: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
有关更多详细信息，请参阅：http: //dev.mysql.com/doc/refman/5.0/en/information-schema.html
回答by Nthalk
Try:
尝试：
select * from information_schema.tables
See: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
参见：http: //dev.mysql.com/doc/refman/5.0/en/information-schema.html
回答by Abs
if we have multiple databases and we need to select all tables for a particular database we can use TABLE_SCHEMAto define database name as:
如果我们有多个数据库并且我们需要选择特定数据库的所有表，我们可以使用TABLE_SCHEMA将数据库名称定义为：
select table_name from information_schema.tables where TABLE_SCHEMA='dbname';
select table_name from information_schema.tables where TABLE_SCHEMA='dbname';
回答by James Williams
Besides using the INFORMATION_SCHEMA table, to use SHOW TABLES to insert into a table you would use the following
除了使用 INFORMATION_SCHEMA 表，要使用 SHOW TABLES 插入到表中，您将使用以下内容
<?php
$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);
$arrayCount = 0
while ($row = mysql_fetch_row($result)) {
$tableNames[$arrayCount] = $row[0];
$arrayCount++; //only do this to make sure it starts at index 0
}
foreach ($tableNames as &$name {
$query = "INSERT INTO metadata (table_name) VALUES ('".$name."')";
mysql_query($query);
}
?>
回答by GolezTrol
Take a look at the table TABLESin the database information_schema. It contains information about the tables in your other databases. But if you're on shared hosting, you probably don't have access to it.
查看TABLES数据库中的表information_schema。它包含有关其他数据库中表的信息。但是，如果您使用的是共享主机，则可能无法访问它。
回答by Andrey
I think you can get the data you want from INFORMATION_SCHEMA TABLES.
我认为您可以从 INFORMATION_SCHEMA TABLES 中获取您想要的数据。
You can find more info here: http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
您可以在此处找到更多信息：http: //dev.mysql.com/doc/refman/5.0/en/tables-table.html
回答by Steven Soroka
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'DATABASE'
回答by sbrbot
MySQL INFORMATION_SCHEMA.TABLEStable contains data about both tables (not temporary but permanent ones) and views. The column TABLE_TYPEdefines whether this is record for table or view (for tables TABLE_TYPE='BASE TABLE'and for views TABLE_TYPE='VIEW'). So if you want to see from your schema (database) tables only there's the following query :
MySQLINFORMATION_SCHEMA.TABLES表包含有关两个表（不是临时的而是永久的）和视图的数据。该列TABLE_TYPE定义这是表记录还是视图记录（对于表TABLE_TYPE='BASE TABLE'和视图TABLE_TYPE='VIEW'）。因此，如果您只想从架构（数据库）表中查看以下查询：
SELECT *
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='myschema'
回答by Robert Sinclair
I think it may be helpful to point out that if you want to select tables that contain specific words you can easily do it using the SELECT(instead of SHOW). Below query easily narrows down the search to tables that contain "keyword"
我认为指出如果您想选择包含特定单词的表格，您可以使用SELECT(而不是SHOW)轻松完成它可能会有所帮助。下面的查询很容易将搜索范围缩小到包含“关键字”的表
SELECT *
FROM information_schema.tables
WHERE table_name like "%keyword%"
回答by Smith
There is yet another simpler way to get table names
还有另一种更简单的方法来获取表名
SHOW TABLES FROM <database_name>
