从“显示表”MySQL 查询中选择数据

Select data from "show tables" MySQL query

提问by

Is it possible to select from show tablesin MySQL?

是否可以从show tablesMySQL 中进行选择？

SELECT * FROM (SHOW TABLES) AS `my_tables`

Something along these lines, though the above does not work (on 5.0.51a, at least).

沿着这些路线的东西，虽然以上不起作用（至少在 5.0.51a 上）。

回答by Andrew

I think you want SELECT * FROM INFORMATION_SCHEMA.TABLES

我想你想要 SELECT * FROM INFORMATION_SCHEMA.TABLES

See 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 Lucas Oman

Not that I know of, unless you select from INFORMATION_SCHEMA, as others have mentioned.

不是我所知道的，除非你从 中选择INFORMATION_SCHEMA，正如其他人提到的那样。

However, the SHOWcommand is pretty flexible, E.g.:

但是，该SHOW命令非常灵活，例如：

SHOW tables like '%s%'

回答by Ivan Ferrer

To count:

计算：

SELECT COUNT(*) as total FROM (SELECT TABLE_NAME as tab, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY tab) tables;

To list:

列出：

SELECT TABLE_NAME as table, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY table;

回答by S?ren Kuklau

SELECT * FROM INFORMATION_SCHEMA.TABLES

That should be a good start. For more, check INFORMATION_SCHEMA Tables.

这应该是一个好的开始。有关更多信息，请查看INFORMATION_SCHEMA 表。

回答by Bill Karwin

You can't put SHOWstatements inside a subquery like in your example. The only statement that can go in a subquery is SELECT.

您不能SHOW像示例中那样将语句放入子查询中。可以进入子查询的唯一语句是SELECT.

As other answers have stated, you can query the INFORMATION_SCHEMA directly with SELECTand get a lot more flexibility that way.

正如其他答案所述，您可以直接查询 INFORMATION_SCHEMASELECT并以这种方式获得更大的灵活性。

MySQL's SHOWstatements are internally just queries against the INFORMATION_SCHEMA tables.

MySQL 的SHOW语句在内部只是对 INFORMATION_SCHEMA 表的查询。

User @physicalattraction has posted this comment on most other answers:

用户@physicalattraction 在大多数其他答案上发布了此评论：

This gives you (meta)information about the tables, not the contents of the table, as the OP intended. – physicalattraction

这为您提供了有关表格的（元）信息，而不是表格的内容，正如 OP 所期望的那样。– 物理吸引力

On the contrary, the OP's question does notsay that they want to select the data in all the tables. They say they want to select from the result of SHOW TABLES, which is just a list of table names.

相反，OP的问题并没有说他们要选择所有表中的数据。他们说他们想从 的结果中进行选择SHOW TABLES，这只是一个表名列表。

If the OP does want to select all data from all tables, then the answer is no, you can't do it with one query. Each query must name its tables explicitly. You can't make a table name be a variable or the result of another part of the same query. Also, all rows of a given query result must have the same columns.

如果 OP 确实想要从所有表中选择所有数据，那么答案是否定的，您不能通过一个查询来完成。每个查询必须明确命名其表。您不能使表名成为变量或同一查询的另一部分的结果。此外，给定查询结果的所有行必须具有相同的列。

So the only way to select all data from all tables would be to run SHOW TABLESand then for each table named in that result, run another query.

因此，从所有表中选择所有数据的唯一方法是运行SHOW TABLES，然后为该结果中命名的每个表运行另一个查询。

回答by Bob Stein

You may be closer than you think — SHOW TABLES already behaves a lot like SELECT:

你可能比你想象的更接近—— SHOW TABLES 的行为已经很像 SELECT：

$pdo = new PDO("mysql:host=$host;dbname=$dbname",$user,$pass);
foreach ($pdo->query("SHOW TABLES") as $row) {
    print "Table $row[Tables_in_$dbname]\n";
}

回答by Bob Stein

Have you looked into querying INFORMATION_SCHEMA.Tables? As in

您是否考虑过查询 INFORMATION_SCHEMA.Tables？如

SELECT ic.Table_Name,
    ic.Column_Name,
    ic.data_Type,
    IFNULL(Character_Maximum_Length,'') AS `Max`,
    ic.Numeric_precision as `Precision`,
    ic.numeric_scale as Scale,
    ic.Character_Maximum_Length as VarCharSize,
    ic.is_nullable as Nulls, 
    ic.ordinal_position as OrdinalPos, 
    ic.column_default as ColDefault, 
    ku.ordinal_position as PK,
    kcu.constraint_name,
    kcu.ordinal_position,
    tc.constraint_type
FROM INFORMATION_SCHEMA.COLUMNS ic
    left outer join INFORMATION_SCHEMA.key_column_usage ku
        on ku.table_name = ic.table_name
        and ku.column_name = ic.column_name
    left outer join information_schema.key_column_usage kcu
        on kcu.column_name = ic.column_name
        and kcu.table_name = ic.table_name
    left outer join information_schema.table_constraints tc
        on kcu.constraint_name = tc.constraint_name
order by ic.table_name, ic.ordinal_position;

回答by Matt Rogish

I think what you want is MySQL's information_schema view(s): http://dev.mysql.com/doc/refman/5.0/en/tables-table.html

我想你想要的是 MySQL 的 information_schema 视图：http: //dev.mysql.com/doc/refman/5.0/en/tables-table.html

回答by Brian

SELECT column_comment FROM information_schema.columns WHERE table_name = 'myTable' AND column_name = 'myColumnName'

This will return the comment on: myTable.myColumnName

这将返回评论：myTable.myColumnName

回答by Bridget Arrington

You can create a stored procedure and put the table names in a cursor, then loop through your table names to show the data.

您可以创建一个存储过程并将表名放在游标中，然后遍历表名以显示数据。

Getting started with stored procedure: http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx

存储过程入门：http: //www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx

Creating a cursor: http://www.mysqltutorial.org/mysql-cursor/

创建游标：http: //www.mysqltutorial.org/mysql-cursor/

For example,

例如，

CREATE PROCEDURE `ShowFromTables`()
BEGIN

DECLARE v_finished INTEGER DEFAULT 0;
DECLARE c_table varchar(100) DEFAULT "";

DECLARE table_cursor CURSOR FOR 
SELECT table_name FROM information_schema.tables WHERE table_name like 'wp_1%';

DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET v_finished = 1;

OPEN table_cursor;

get_data: LOOP

FETCH table_cursor INTO c_table;

IF v_finished = 1 THEN 
LEAVE get_data;
END IF;

SET @s=CONCAT("SELECT * FROM ",c_table,";");

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END LOOP get_data;

CLOSE table_cursor;

END

Then call the stored procedure:

然后调用存储过程：

CALL ShowFromTables();