如何从SELECT查询(而不是表)显示列?

I get a syntax error when I run the following:

当我运行以下代码时，会出现语法错误:

show columns from (select * from (select * from my_table) as T)

How can I show the columns from a query that I wrote, rather than from a table?

如何显示我编写的查询中的列，而不是表中的列?

4 个解决方案

#1

METHOD 1: Temporary table

方法1:临时表

The answers already posted about using a temporary table will usually be the most appropriate solution. But there is an important point that if the query is run as-is, all the joins etc. will be processed, which could potentially take a long time in some cases. Fortunately, MySQL allows LIMIT 0 to return no rows and the documentation states that this "quickly returns an empty set". The following stored procedure will do this job for you by taking a SQL query string as input, wrapping it with LIMIT 0, running the dynamic query to produce a temporary table and then showing its columns:

关于使用临时表的回答通常是最合适的解决方案。但是有一点很重要，如果查询按原样运行，那么将处理所有连接等，这在某些情况下可能需要很长时间。幸运的是，MySQL允许LIMIT 0不返回任何行，并且文档表明这“快速返回一个空集”。下面的存储过程将使用SQL查询字符串作为输入来完成这项工作，将其包装为LIMIT 0，运行动态查询生成临时表，然后显示其列:

CREATE PROCEDURE showColumns(IN sqlToShow TEXT)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tempTable;
    SET @sqlLimit0 = CONCAT('CREATE TEMPORARY TABLE tempTable AS (SELECT * FROM (',
                            sqlToShow, ') subq LIMIT 0)');
    PREPARE stmt FROM @sqlLimit0;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SHOW COLUMNS FROM tempTable;
END;

One important point is the query passed in should not have a semi-colon at the end. (If necessary, the stored procedure could be modified to remove trailing semi-colons but I wanted to keep it simple.)

重要的一点是传入的查询末尾不应该有分号。(如果需要，可以修改存储过程以删除拖尾半冒号，但我想保持简单。)

Here is a live demo showing it in action: http://rextester.com/NVWY58430

这里是一个现场演示:http://rextester.com/NVWY58430。

METHOD 2: INFORMATION_SCHEMA.COLUMNS

方法2:INFORMATION_SCHEMA.COLUMNS

The same information returned by SHOW COLUMNS can also be obtained directly from the INFORMATION_SCHEMA.COLUMNS table:

SHOW列返回的相同信息也可以直接从INFORMATION_SCHEMA中获取。列的表:

SELECT TABLE_NAME AS `Table`,
       COLUMN_NAME AS `Field`,
       COLUMN_TYPE AS `Type`,
       IS_NULLABLE AS `Null`,
       COLUMN_KEY AS `Key`, 
       COLUMN_DEFAULT AS `Default`,
       EXTRA AS `Extra`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = SCHEMA() -- This uses the current schema
  AND `TABLE_NAME` IN ('table1', 'table2', 'etc.');
   -- ...or could go even further and restrict to particular columns in tables if desired

The above suffers from the disadvantages of requiring the table (and optionally column) names to be entered manually and not showing alias names in the SELECT but it does the basic job. Its advantages are it doesn't require the user to have permission to create a temporary table and the information returned could be extended to provide further column information such as maximum character length, numeric precision/scale, column comments etc.

以上所述的缺点是，需要手动输入表(和可选列)名称，而在SELECT中不显示别名，但它执行基本工作。它的优点是不需要用户拥有创建临时表的权限，并且返回的信息可以扩展以提供更多的列信息，如最大字符长度、数字精度/比例、列注释等。

#2

I am using Java to retrieve columns from MySql query.

我正在使用Java从MySql查询中检索列。

The best way in Java to get column information for a result set is to use the ResultSetMetaData interface:

Java中获取结果集列信息的最佳方法是使用ResultSetMetaData接口:

PreparedStatement stmt = null;
ResultSet result = null;
ResultSetMetaData meta = null;

try {
    stmt = con.prepareStatement("SELECT * FROM MyTable"); 
    result = stmt.executeQuery();
} catch (SQLException e) {
    System.out.println("SQLException: "+e.getMessage());
    System.exit(1);
}
System.out.println("Successful query");

try {
    meta = result.getMetaData();
    System.out.println("Total columns: " + meta.getColumnCount());
    System.out.println("Name of column 1: " + meta.getColumnName(1));
    System.out.println("Type of column 1: " + meta.getColumnTypeName(1));

    System.out.println("Name of column 2: " + meta.getColumnName(2));
    System.out.println("Type of column 2: " + meta.getColumnTypeName(2));
} catch (SQLException e) {
    System.out.println("SQLException: "+e.getMessage());
    System.exit(1);
}
System.out.println("Successful metadata report");

My table is declared:

我的表是宣称:

CREATE TABLE `MyTable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Output of my example Java code:

我的示例Java代码的输出:

Successful query
Total columns: 2
Name of column 1: id
Type of column 1: BIGINT UNSIGNED
Name of column 2: name
Type of column 2: VARCHAR
Successful metadata report

You can get other information about result set columns besides their names and data types. See http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSetMetaData.html for full reference docs on the ResultSetMetaData interface.

除了它们的名称和数据类型之外，您还可以获得关于结果集列的其他信息。请参阅http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSetMetaData.html获取ResultSetMetaData接口上的完整参考文档。

#3

Looks like this statement only accepts existing tables.

看起来这个语句只接受现有的表。

So I created a new temporary table with my query and got the column names from there.

因此，我用我的查询创建了一个新的临时表，并从那里得到了列名。

/*if the exporting table was created before, then delete it*/
DROP TABLE IF EXISTS exportTable;

/*create the temporary table (check if you have mySQL permission to do so)*/
CREATE TEMPORARY TABLE exportTable AS (your_query);

/*get result table (this is a table, the columns names are in the first column of this table ['Field'])*/
SHOW COLUMNS FROM exportTable;

The temporary table is created in session context, and will be droped when session is closed. The same is for the SHOW COLUMNS table. You might consider the impact of these table creations on the server disk.

临时表是在会话上下文中创建的，在会话关闭时将被拖放。显示列表也是如此。您可能会考虑这些表创建对服务器磁盘的影响。

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

您可以在创建表时使用临时关键字。临时表仅对当前会话可见，并在会话关闭时自动删除。这意味着两个不同的会话可以使用相同的临时表名，而不会相互冲突，也不会与同名的现有非临时表发生冲突。(现有表是隐藏的，直到删除临时表。)要创建临时表，必须具有创建临时表特权。

http://dev.mysql.com/doc/refman/5.7/en/create-table.html

#4

-1

try this -

试试这个,

SHOW COLUMNS FROM (select * from my_table) T

or Directly SHOW COLUMNS FROM my_table

或者直接显示my_table中的列。

#1