Syntax
CAST(expr AS type)
Description
CAST（）函数采用一种类型的值，并产生另一种类型的值，类似于CONVERT函数。
CAST（）和CONVERT（）之间的主要区别是CONVERT（expr，type）是ODBC语法，而CAST（expr as type）和CONVERT（...USING ...)` 是SQL92语法。
Examples
简单的转换：
SELECT CAST("abc" AS BINARY);
SELECT CAST("1" AS UNSIGNED INTEGER);
SELECT CAST(123 AS CHAR CHARACTER SET utf8)
注意，当不指定字符集而将字符强制转换为CHAR时，将使用collation_connection字符集排序规则。与CHAR CHARACTER SET一起使用时，将使用该字符集的默认排序规则。
SELECT COLLATION(CAST(123 AS CHAR));
+------------------------------+
| COLLATION(CAST(123 AS CHAR)) |
+------------------------------+
| latin1_swedish_ci |
+------------------------------+
SELECT COLLATION(CAST(123 AS CHAR CHARACTER SET utf8));
+-------------------------------------------------+
| COLLATION(CAST(123 AS CHAR CHARACTER SET utf8)) |
+-------------------------------------------------+
| utf8_general_ci |
+-------------------------------------------------+
如果您还想更改排序规则，则必须使用COLLATE运算符：
SELECT COLLATION(CAST(123 AS CHAR CHARACTER SET utf8)
COLLATE utf8_unicode_ci);
+-------------------------------------------------------------------------+
| COLLATION(CAST(123 AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci) |
+-------------------------------------------------------------------------+
| utf8_unicode_ci |
+-------------------------------------------------------------------------+
使用CAST（）将ENUM字段排序为CHAR而不是内部数值：
CREATE TABLE enum_list (enum_field enum('c','a','b'));
INSERT INTO enum_list (enum_field)
VALUES('c'),('a'),('c'),('b');
SELECT * FROM enum_list
ORDER BY enum_field;
+------------+
| enum_field |
+------------+
| c |
| c |
| a |
| b |
+------------+
SELECT * FROM enum_list
ORDER BY CAST(enum_field AS CHAR);
+------------+
| enum_field |
+------------+
| a |
| b |
| c |
| c |
+------------+
从MariaDB 5.5.31开始，以下内容将触发警告，因为x'aa'和'X'aa'不再表现为数字。以前，在所有版本的MySQL中，均不会触发警告，因为它们的确错误地表现为数字：
SELECT CAST(0xAA AS UNSIGNED), CAST(x'aa' AS UNSIGNED), CAST(X'aa' AS UNSIGNED);
+------------------------+-------------------------+-------------------------+
| CAST(0xAA AS UNSIGNED) | CAST(x'aa' AS UNSIGNED) | CAST(X'aa' AS UNSIGNED) |
+------------------------+-------------------------+-------------------------+
| 170 | 0 | 0 |
+------------------------+-------------------------+-------------------------+
1 row in set, 2 warnings (0.00 sec)
Warning (Code 1292): Truncated incorrect INTEGER value: 'xAA'
Warning (Code 1292): Truncated incorrect INTEGER value: 'xAA'
强制转换：
SELECT CAST(2019-01-04 INTERVAL AS DAY_SECOND(2)) AS "Cast";
+-------------+
| Cast |
+-------------+
| 00:20:17.00 |
+-------------+