2

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.

1
  • 2
    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.
    – Akina
    Mar 20, 2022 at 17:26

3 Answers 3

3

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         |
+--------------+
4
1
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.

2
  • I wish MySQL had a function like Python's type(). Mar 22, 2022 at 17:12
  • 1
    @RandomPerson - I have wanted such, too. Php has a similar function.
    – Rick James
    Mar 22, 2022 at 17:29
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.