Cast Functions and Operators

Cast functions and operators enable conversion of values from one data type to another. TiDB supports all of the cast functions and operators available in MySQL 8.0.

BINARY

The BINARY operator has been deprecated since MySQL 8.0.27. It is recommended to use CAST(... AS BINARY) instead both in TiDB and MySQL.

CAST

The CAST(<expression> AS <type> [ARRAY]) function is used to cast an expression to a specific type.

This function is also used to create Multi-valued indexes.

The following types are supported:

Examples:

The following statement converts a binary string from a HEX literal to a CHAR.

SELECT CAST(0x54694442 AS CHAR);

+--------------------------+
| CAST(0x54694442 AS CHAR) |
+--------------------------+
| TiDB                     |
+--------------------------+
1 row in set (0.0002 sec)

The following statement casts the values of the a attribute extracted from the JSON column to an unsigned array. Note that casting to an array is only supported as part of an index definition for multi-valued indexes.

CREATE TABLE t (
    id INT PRIMARY KEY,
    j JSON,
    INDEX idx_a ((CAST(j->'$.a' AS UNSIGNED ARRAY)))
);
INSERT INTO t VALUES (1, JSON_OBJECT('a',JSON_ARRAY(1,2,3)));
INSERT INTO t VALUES (2, JSON_OBJECT('a',JSON_ARRAY(4,5,6)));
INSERT INTO t VALUES (3, JSON_OBJECT('a',JSON_ARRAY(7,8,9)));
ANALYZE TABLE t;

 EXPLAIN SELECT * FROM t WHERE 1 MEMBER OF(j->'$.a')\G
*************************** 1. row ***************************
           id: IndexMerge_10
      estRows: 2.00
         task: root
access object: 
operator info: type: union
*************************** 2. row ***************************
           id: ├─IndexRangeScan_8(Build)
      estRows: 2.00
         task: cop[tikv]
access object: table:t, index:idx_a(cast(json_extract(`j`, _utf8mb4'$.a') as unsigned array))
operator info: range:[1,1], keep order:false, stats:partial[j:unInitialized]
*************************** 3. row ***************************
           id: └─TableRowIDScan_9(Probe)
      estRows: 2.00
         task: cop[tikv]
access object: table:t
operator info: keep order:false, stats:partial[j:unInitialized]
3 rows in set (0.00 sec)

CONVERT

The CONVERT() function is used to convert between character sets.

Example:

SELECT CONVERT(0x616263 USING utf8mb4);

+---------------------------------+
| CONVERT(0x616263 USING utf8mb4) |
+---------------------------------+
| abc                             |
+---------------------------------+
1 row in set (0.0004 sec)

MySQL compatibility

• TiDB does not support cast operations on SPATIAL types. For more information, see #6347.
• TiDB does not support AT TIME ZONE for CAST(). For more information, see #51742.
• CAST(24 AS YEAR) returns 2 digits in TiDB and 4 digits in MySQL. For more information, see #29629.