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.

NameDescription
BINARYCast a string to a binary string
CAST()Cast a value as a certain type
CONVERT()Cast a value as a certain type

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:

TypeDescriptionWhether it can be used with multi-valued indexes
BINARY(n)Binary stringNo
CHAR(n)Character stringYes, but only if a length is specified
DATEDateYes
DATETIME(fsp)Date/time, where fsp is optionalYes
DECIMAL(n, m)Decimal number, where n and m are optional and are 10 and 0 if not specifiedNo
DOUBLEDouble precision floating-point numberNo
FLOAT(n)Floating-point number, where n is optional and should be between 0 and 53No
JSONJSONNo
REALFloating-point numberYes
SIGNED [INTEGER]Signed integerYes
TIME(fsp)TimeYes
UNSIGNED [INTEGER]Unsigned integerYes
YEARYearNo

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.