How to find data type of output in MySQL?

by

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment