(SOLVED) MYSQL: how to get the list of all the fields produced by a SELECT?-CodePudding

I need to know, given a MySQL SELECT, no matter how complex is and how many tables are joined, if there is a way to get the list of the fields resulting, especially if there are asterisks instead of the field list. For example:

SELECT a.*, b.field1, b.field2, b.field3, c.* 
FROM table1 a, table2 b, table3 c 
WHERE a.id=b.id 
AND b.id NOT IN (SELECT c2.id_table3 FROM table3 c2 WHERE ...);`

where

table1

id	alpha	beta	gamma	delta
--	---	----	----	-----
--	---	----	----	-----

table2

id	field1	field2	field3	field4	field5
--	----	------	------	------	------
--	----	------	------	------	------
--	----	------	------	------	------

table3

id_table3	epsilon	zeta
---------	-----	----
---------	-----	----

I don't know if there is a special query, something like DESCRIBE or SHOW FIELDS FROM table, which could make obtain, according to the example, an output like this

result
id
alpha
beta
gamma
delta
field1
field2
field3
id_table3
epsilon
zeta

I tried with SHOW FIELDS FROM (SELECT ....) but I get a syntax error.

Thanks in advance to everybody could help me or give me helpful hints

CodePudding user response：

You can get metadata about columns in a query result from many programming interfaces. You don't say which language or API you are using.

If you just want to know it for an ad hoc query you are running in the MySQL client, you can use:

mysql --column-type-info

Then run your query interactively in the mysql client. Here's an example:

mysql> select * from mytable;
Field   1:  `pk`
Catalog:    `def`
Database:   `test`
Table:      `mytable`
Org_table:  `mytable`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 3
Decimals:   0
Flags:      NOT_NULL PRI_KEY NO_DEFAULT_VALUE NUM PART_KEY 

Field   2:  `name`
Catalog:    `def`
Database:   `test`
Table:      `mytable`
Org_table:  `mytable`
Type:       BLOB
Collation:  utf8mb4_0900_ai_ci (255)
Length:     262140
Max_length: 5
Decimals:   0
Flags:      BLOB 


 ----- ------- 
| pk  | name  |
 ----- ------- 
|   3 | hello |
...

This is more information than simply a list of column names, but it might be useful to you.

Re your comment:

Since you are using PDO, I direct you to this page: https://www.php.net/manual/en/pdostatement.getcolumnmeta.php

PDOStatement::getColumnMeta — Returns metadata for a column in a result set

There's a code example on that page showing how to use it.

For other readers: There are similar functions for most other programming interfaces to MySQL. You should read through the documentation for the API you're using.

CodePudding user response：

Create a view:

create view foo as (
    -- your query
)

Then:

show columns for foo

To not leave the view lying around, you could either drop view Foo afterwards, or wrap the above 2 statements is begin; and rollback;.

See live demo.