cast() 和 convert() 函数非常常用,它们可以转换数据的数据类型、字符集以及排序校对规则。
数据类型转换
cast() 和 convert() 函数可以将 value 值,从当前数据类型转换为另一种数据类型。
基本语法:
CAST(expr AS type)
CONVERT(expr, type)
mysql> select now(), cast(now() as date), convert(now(), date);
+---------------------+---------------------+----------------------+
| now() | cast(now() as date) | convert(now(), date) |
+---------------------+---------------------+----------------------+
| 2023-02-16 14:52:54 | 2023-02-16 | 2023-02-16 |
+---------------------+---------------------+----------------------+
1 row in set (0.00 sec)
当无符号的整数,减去另一个大于它的整数时,会报错。
mysql> select cast(2 as unsigned) - 5;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(2 as unsigned) - 5)'
这是因为如果其中有一个,确定了是无符号的话,若相减的结果小于 0,就会报错。
解决方法:最好将两个数都确切转换成有符号(signed)的
mysql> select cast(2 as signed) - cast(5 as signed);
+---------------------------------------+
| cast(2 as signed) - cast(5 as signed) |
+---------------------------------------+
| -3 |
+---------------------------------------+
1 row in set (0.00 sec)
如果被转换的数据为 null,返回值也是null。
大多数情况下,convert(expr, type) 和 cast(expr as type) 等价。但是,convert 不支持 multi-valued 索引创建和 array 关键词。
字符集转换
cast() 和 convert() 函数可以将 value 值,从当前的字符集转换为另一种字符集。
基本语法:
CONVERT(expr USING transcoding_name)
CONVERT(string, CHAR[(N)] CHARACTER SET charset_name)
CAST(string AS CHAR[(N)] CHARACTER SET charset_name)
示例:
mysql> select convert('ab' using utf8mb4), convert('ab', char character set utf8mb4), cast('ab' as char character set utf8mb4);
+-----------------------------+-------------------------------------------+------------------------------------------+
| convert('ab' using utf8mb4) | convert('ab', char character set utf8mb4) | cast('ab' as char character set utf8mb4) |
+-----------------------------+-------------------------------------------+------------------------------------------+
| ab | ab | ab |
+-----------------------------+-------------------------------------------+------------------------------------------+
1 row in set (0.00 sec)
此外,它们还可以设置字符的排序校对规则(collation)。
SELECT CONVERT('ab' USING utf8mb4) COLLATE utf8mb4_bin;
SELECT CONVERT('ab', CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;
SELECT CAST('ab' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;