How to find data type of output in MySQL?

All we need is an easy explanation of the problem, so here it is.

I was reading about the CONCAT() function, and I noticed this:

mysql> SELECT CONCAT(14.3);
        -> '14.3'

While running this command in MySQL 8.0 Command Line Client, the output didn’t have quotes. So I wanted to verify the data type of the output (I assume it is a string data type). Please help me find the data type of the output.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can use the column-type-info option to the mysql client. It reports the data types of the query results:

% mysql --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.26 MySQL Community Server - GPL

...

mysql> SELECT CONCAT(14.3);
Field   1:  `CONCAT(14.3)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     20
Max_length: 4
Decimals:   31
Flags:      


+--------------+
| CONCAT(14.3) |
+--------------+
| 14.3         |
+--------------+

Method 2

mysql> SELECT CONCAT(14.3);
+--------------+
| CONCAT(14.3) |
+--------------+
| 14.3         |
+--------------+

Some notes:

  • Since it is left justified, it is probably a string.

  • CONCAT() generates a string.

  • "14.3" and 14.3 can act as either a string or a number:

      mysql> SELECT CONCAT(14.3) + 0, "14.3" + 0;
      +------------------+------------+
      | CONCAT(14.3) + 0 | "14.3" + 0 |
      +------------------+------------+
      |             14.3 |       14.3 |
      +------------------+------------+
    
      mysql> SELECT CONCAT(14.3) + 0, "14.3";
      +------------------+------+
      | CONCAT(14.3) + 0 | 14.3 |
      +------------------+------+
      |             14.3 | 14.3 |
      +------------------+------+
    
      mysql> SELECT 14.3 AS a_number, "14.3" AS a_string;
      +----------+----------+
      | a_number | a_string |
      +----------+----------+
      |     14.3 | 14.3     |
      +----------+----------+
    
  • Since things work either way, why do you care about the type? (I may need to dig deeper after you answer that.)

  • I don’t believe there is an introspective function for datatype.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply