How to find data type of output in MySQL?

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.

• Akina: 1. The datatype system in MySQL is soft/weak. So the value (and user-defined variable) datatype can be altered, and the value itself can be adjusted depends on the current execution context. 2. The output displayed by the client is formatted text always, so, even when the output datatype is other than string, you try to investigate not the output but its textual representation. The format which you want to determine exists in the data transferring channel between the server ind the client only.

Answer 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 |
+--------------+

• Random Person: I am using Windows. Collation was this in my case: cp850_general_ci (4). The collation depends on what? Also, please share the link of the relevant documentation.
• Bill Karwin: When there is no table referenced, the collation comes from the session. See dev.mysql.com/doc/refman/8.0/en/charset-connection.html
• Random Person: Can you please share documentation link regarding --column-type-info?
• Bill Karwin: dev.mysql.com/doc/refman/8.0/en/…

Answer 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.

• Random Person: I wish MySQL had a function like Python's type().
• Rick James: @RandomPerson - I have wanted such, too. Php has a similar function.

Answer 3

Also an option: If you're using MySQL Workbench, then in the Query Results window you can click on the "Field Types" button:

picture of the Field Types button

It shows Type and other info for each column selected in your query.

I used it just now to see the different return types of some json functions:

example of the Field Types output