Both CAST and CONVERT functions are used in MySQL for data type conversion

Updated to 7 hours ago

In MySQL,CAST()andCONVERT()Functions are used for data type conversion. Although the two functions are used interchangeably in most cases, there are some slight differences between them.

Official document address

/doc/refman/8.4/en/#function_cast

CAST() function

CAST()Functions are data type conversion functions in the SQL standard. The basic syntax is as follows:

CAST(expression AS type)

CAST(timestamp_value AT TIME ZONE timezone_specifier AS DATETIME[(precision)])

timezone_specifier: [INTERVAL] '+00:00' | 'UTC'

expressionis the value or expression to be converted.
typeis the data type to be converted to.

For example, convert an integer to a string:

(root@localhost:mysql.sock)[(superdb)]>SELECT CAST(8860 AS CHAR) as v1;
+------+
| v1   |
+------+
| 8860 |
+------+
1 row in set (0.00 sec)

The cast function is useful for creating columns with specific types in CREATE TABLE. . . SELECT statement
The cast functions are useful for creating a column with a specific type in a CREATE TABLE … SELECT statement

(root@localhost:mysql.sock)[superdb]>CREATE TABLE t_new_table SELECT CAST('2024001' AS decimal(18,0)) as id,CAST('2000-01-01' AS DATE) AS col_1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

(root@localhost:mysql.sock)[superdb]>show create table t_new_table;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                           |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_new_table | CREATE TABLE `t_new_table` (
  `id` decimal(18,0) NOT NULL DEFAULT '0',
  `col_1` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


(root@localhost:mysql.sock)[superdb]>desc t_new_table;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | decimal(18,0) | NO   |     | 0       |       |
| col_1 | date          | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

(root@localhost:mysql.sock)[superdb]>select * from t_new_table;
+---------+------------+
| id      | col_1      |
+---------+------------+
| 2024001 | 2000-01-01 |
+---------+------------+
1 row in set (0.00 sec)

Cast to signed or unsigned 64-bit integer

use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer

(root@localhost:mysql.sock)[superdb]>SELECT 8-9 as v1,CAST(8 - 9 AS SIGNED) as v2, CAST(8 - 9 AS UNSIGNED) as v3;
+----+----+----------------------+
| v1 | v2 | v3                   |
+----+----+----------------------+
| -1 | -1 | 18446744073709551615 |
+----+----+----------------------+
1 row in set (0.00 sec)

Starting with MySQL 8.0.22, CAST() supports using the AT TIMEZONE operator to retrieve TIMESTAMP values in UTC. The only supported time zone is UTC; this can be specified as "+000:00" or "UTC". The only return type supported by this syntax is DATETIME, with optional precision specifiers ranging from 0 to 6 (including 0 to 6)

Beginning with MySQL 8.0.22, CAST() supports retrieval of a TIMESTAMP value as being in UTC, using the AT TIMEZONE operator. The only supported time zone is UTC; this can be specified as either of '+00:00' or 'UTC'. The only return type supported by this syntax is DATETIME, with an optional precision specifier in the range of 0 to 6, inclusive.

TIMESTAMP values that use timezone offsets are also supported.


(root@localhost:mysql.sock)[superdb]> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| CST                |
+--------------------+
1 row in set (0.00 sec)

(root@localhost:mysql.sock)[superdb]> CREATE TABLE t_cast_timezone (col_convert_datetime TIMESTAMP);
Query OK, 0 rows affected (0.06 sec)

(root@localhost:mysql.sock)[superdb]> INSERT INTO t_cast_timezone VALUES ROW(CURRENT_TIMESTAMP),ROW('2024-06-15 14:50:15');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

(root@192.168.80.85)[superdb]> TABLE t_cast_timezone;
+----------------------+
| col_convert_datetime |
+----------------------+
| 2024-06-15 22:06:07  |
| 2024-06-15 14:50:15  |
+----------------------+
2 rows in set (0.00 sec)

(root@localhost:mysql.sock)[superdb]> SELECT CAST(col_convert_datetime AT TIME ZONE '+00:00' AS DATETIME) AS u FROM t_cast_timezone;
+---------------------+
| u                   |
+---------------------+
| 2024-06-15 14:06:07 |
| 2024-06-15 06:50:15 |
+---------------------+
2 rows in set (0.00 sec)

CONVERT() function

CONVERT()Functions are provided in some database systems such as MySQL, but may not be available in others. The basic syntax is as follows:

CONVERT(expression, type)

or (in some database systems)

CONVERT(type, expression)

But please note that in MySQL,CONVERT()Function syntax andCAST()similar:

CONVERT(expression, type)

expressionis the value or expression to be converted.
typeis the data type to be converted to.

For example, in MySQL, convert integers to strings withCAST()The same example of the function:

(root@localhost:mysql.sock)[superdb]>SELECT CONVERT(123890,CHAR) as v1;
+--------+
| v1     |
+--------+
| 123890 |
+--------+
1 row in set (0.01 sec)

For example, in MySQL, an example of converting an integer to a double-precision decimal type

decimalTypes can store a large number of numbers and have configurable precision. For example,decimal(18,2)Can store up to 18 digits, with 2 digits after the decimal point

(root@localhost:mysql.sock)[superdb]>SELECT CONVERT(123890,decimal(18,2)) as v1;
+-----------+
| v1        |
+-----------+
| 123890.00 |
+-----------+
1 row in set (0.00 sec)

-- Example of converting a string to a double precision decimal type

(root@localhost:mysql.sock)[superdb]>SELECT CONVERT(123890,decimal(18,2)) as v1,CONVERT('123890',decimal(18,2)) as v2;
+-----------+-----------+
| v1        | v2        |
+-----------+-----------+
| 123890.00 | 123890.00 |
+-----------+-----------+
1 row in set (0.00 sec)

Convert date string to date type

(root@localhost:mysql.sock)[superdb]>SELECT CONVERT('2024-06-13', DATE) as v1,CONVERT('2024-06-13 13:16:24', DATETIME) as v2;
+------------+---------------------+
| v1         | v2                  |
+------------+---------------------+
| 2024-06-13 | 2024-06-13 13:16:24 |
+------------+---------------------+
1 row in set (0.00 sec)

Character set conversion is also useful before the alphabetical case conversion of binary strings. LOWER() and UPPER() are invalid when applied directly to binary strings, because the concept of lettercase does not apply.

Character set conversion is also useful preceding lettercase conversion of binary strings. LOWER() and UPPER() are ineffective when applied directly to binary strings because the concept of lettercase does not apply. To perform lettercase conversion of a binary string, first convert it to a nonbinary string using a character set appropriate for the data stored in the string

(root@localhost:mysql.sock)[superdb]>SET @str = BINARY 'New York';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost:mysql.sock)[superdb]>SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));
+--------------------------+------------------------------------+
| LOWER(@str)              | LOWER(CONVERT(@str USING utf8mb4)) |
+--------------------------+------------------------------------+
| 0x4E657720596F726B       | new york                           |
+--------------------------+------------------------------------+
1 row in set (0.00 sec)

Use CONVERT() for character set conversion (Note: CONVERT() may not support character set conversion in some database systems, but the CONVERT() function of MySQL can do so with the support of the USING clause)

The difference between the two

SQL Standard：CAST()is a function in the SQL standard, andCONVERT()Not all database systems support it.
grammar: Although in MySQLCONVERT()Syntax andCAST()Similar, but may differ in other databases.
Function: In some databases,CONVERT()Additional features or options may be provided inCAST()Not available in. But in MySQL, these two functions are very similar in functionality.
readability:sometimes,CONVERT()It may be considered more readable because it is closer to type conversion syntax in many programming languages. However, sinceCAST()is a function in the SQL standard, so it is usually more recommended.

Summarize

In MySQL,CAST()andCONVERT()Both can be used for data type conversion, and they are very similar in functionality. However, sinceCAST()is a function in the SQL standard, so it is generally recommended. But in other database systems, you may need to check the availability and functional differences between the two functions.