Question 21458 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search