How to SHOW COLUMNS from a SELECT query (rather than a table)?

I get a syntax error when I run the following:

show columns from (select * fro开发者_运维问答m (select * from my_table) as T)

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

---

METHOD 1: Temporary table

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:

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

METHOD 2: INFORMATION_SCHEMA.COLUMNS

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

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.

---

I am using Java to retrieve columns from MySql query.

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

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:

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.

---

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

---

I use something like this:

create procedure showFields(s text)
begin
set @showfields_var:=concat('create temporary table tmp_showfields as select * from (',s,')a where 0'); 
prepare phrase from @showfields_var;execute phrase;
show columns from tmp_showfields;
drop temporary table tmp_showfields;
end

---

try this -

SHOW COLUMNS FROM (select * from my_table) T

or Directly SHOW COLUMNS FROM my_table