MySQL - GROUP_CONCAT() on names of temporary table columns?

I cannot find the syntax to accomplish this. I have created several temporary tables, and I'd like to parse the column names as a standard list for use elsewhere in my application.

SELECT COLUMN_NAMES FROM INFORMATION_SCHEMA...

does not work on temp tables it seems. However:

SHOW COLUMNS FROM TEMP_TBL

does return a valid query. But I guess it is not compatible for use in a subquery. Essentially what I'd like is for something like this to work.

SELECT GROUP_CONCAT(FIELD) FROM
(
    SHOW COLUMNS FROM TEMP_OB
);

Since SHOW COLUMNS returns a query, I thought it would be possible to subquery a SHOW COLUMNS statement, but it is not. Does anyone know of a way to query and group the properties of fields in temporary tables for use as a regular query? I'd like to have ColdFusion store & evaluate these variables in other contexts while querying temporary tables.

Solution

This all depends on what you are trying to achieve. As you have already seen you can't use SHOW as a subquery. Thankfully there are other ways to get that columnlist. So first of all here's some cfscript I've put together to create a temporary table and fill it with some dummy data.

<cfscript>
    datasource = 'foobar';
    qry = new Query(datasource=datasource);
    // Make sure the temporary table isn't there first
    qry.execute(sql='DROP TEMPORARY TABLE IF EXISTS foo');
    // Create it
    qry.execute(sql='CREATE TEMPORARY TABLE foo (col1 INT,col2 INT,col3 INT,col4 INT)');
    // Insert some data
    qry.execute(sql='INSERT INTO foo VALUES (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5)');
</cfscript>

Next up you can do a few more queries to get the column list that you are looking for

<cfscript>    
    // Select the data and use prefix to get column list
    qr = qry.execute(sql='SELECT * FROM foo');
    writedump(qr.getPrefix().columnlist);

    // Also get the columns from SHOW COLUMNS using valuelist
    colqr = qry.execute(sql='SHOW COLUMNS FROM foo');
    writedump(ValueList(colqr.getResult().field));
</cfscript>

This can also of course be achieved in cfml, I just prefer cfscript.

<!--- SELECT method to get column names --->
<cfquery name="qr" datasource="#datasource#">
    SELECT * FROM foo
</cfquery>
<cfdump var="#qr.columnlist#">

<!--- SHOW COLUMNS method to get column names --->
<cfquery name="colqr" datasource="#datasource#">
    SHOW COLUMNS FROM foo
</cfquery>
<cfdump var="#ValueList(colqr.field)#">

So while one of the above will hopefully fit in with your current logic, it may well be that what you are ultimately achieving can be done in a better way but hopefully this can point you in a good direction.