MySQL: How to determine a value type

mariadbMySQL

I am looking for some function that will return the type of given value/result like TYPE(value). Similar as for charset/collation for which there are functions CHARSET(value)//COLLATION(VALUE).

I know how to query a column type. But I am looking really for a function applicable to any value/result:

  1. select CONCAT('hello', 5)

  2. select IF(1, 'hello', 5) as v1, IF(0, 'hello', 5) as v2

  3. (select 'hello' AS v) union all (select '5')

  4. select 4.0 AS v

Does MySQL have any function to display the type?

Best Answer

A partial answer:

$ mysql -u root --column-type-info -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

Note the extra parameters. Now I will issue just one simple command; see what it comes back with:

mysql> SELECT * FROM things;
--------------
SELECT * FROM things
--------------

ERROR 1046 (3D000): No database selected
mysql> use try;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM things;
--------------
SELECT * FROM things
--------------

Field   1:  `thing_id`
Catalog:    `def`
Database:   `try`
Table:      `things`
Org_table:  `things`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY 

Field   2:  `name`
Catalog:    `def`
Database:   `try`
Table:      `things`
Org_table:  `things`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     96
Max_length: 7
Decimals:   0
Flags:      NOT_NULL 


+----------+---------+
| thing_id | name    |
+----------+---------+
|        2 | thing 2 |
|        3 | thing 3 |
|        4 | thing 1 |
+----------+---------+
3 rows in set (0.00 sec)

mysql>

I don't think there is a way to get the 'type' of an expression.

Related Question