CODEWITHSUNDEEP
mysqlsql
samuelbrody1249
samuelbrody1249

Reputation: 4767

Type introspection in mysql query

Is there a function in mysql that tells what type the evaluated expression/column/literal is in a query? I don't mean the information schema, but something like:

SELECT
    TYPE('Washington') as literal_type,
    TYPE(IF(col='a', col, 2)) as expression_type,
    TYPE(col) as col_type
FROM table

And I would get something llike:

VARCHAR      --      INT64          --      DATE

This is mainly for debugging purposes.

Upvotes: 1

Views: 172

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

No, there's no function for what you're describing.

The closest thing to what you're talking about is using the MySQL client with the --column-type-info option to display result set metadata.

But that wouldn't help you if you're using SQL programmatically.

mysql> select 'Washington' as literal_type, if(d = 9, d, 2) as expression_type, t as col_type from mytable;

Output:

Field   1:  `literal_type`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     40
Max_length: 10
Decimals:   31
Flags:      NOT_NULL 

Field   2:  `expression_type`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 1
Decimals:   0
Flags:      BINARY NUM 

Field   3:  `col_type`
Catalog:    `def`
Database:   `test`
Table:      `mytable`
Org_table:  `mytable`
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   0
Flags:      BINARY 


+--------------+-----------------+------------+
| literal_type | expression_type | col_type   |
+--------------+-----------------+------------+
| Washington   |               2 | 2020-11-21 |
+--------------+-----------------+------------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions