1

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

m3t4l
  • 41
  • 4
  • There's probably a better way, but presumably you could create a view from your query, use `describe` or `show fields` with the view, then drop the view. – EdmCoff Jan 03 '23 at 20:07

2 Answers2

2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • thank you, this is a very useful directive. In the meanwhile, my project manager gave me more information and told me to use PHP language and PDO connection; I should use the prepare() method containing the query and then the execute() method...so how could I use the option --column-type-info? – m3t4l Jan 03 '23 at 20:23
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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • thank you, but I have no permission to create view – m3t4l Jan 03 '23 at 20:12
  • @m3t4l then create a local instance of MySQL and give yourself sysadmin privileges, export the schema from the target instance and load it into your instance, then run these commands. If you're a developer, you should have your own copy anyway. – Bohemian Jan 03 '23 at 20:16
  • the create view will get a `Duplicate column name` error for some queries – ysth Jan 03 '23 at 20:31