MySQL类型转换函数 —— binary、cast()、convert()

MySQL类型转换函数 —— binary、cast()、convert()

官方文档 https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html

1、字符编码转换

CONVERT(expr USING transcoding_name)
CONVERT(string, CHAR[(N)] CHARACTER SET charset_name)
CAST(string AS CHAR[(N)] CHARACTER SET charset_name)

-- 未指定排序规则
SELECT CONVERT('test' USING utf8);
SELECT CONVERT('test', CHAR CHARACTER SET utf8);
SELECT CAST('test' AS CHAR CHARACTER SET utf8);

-- 指定排序规则
SELECT CONVERT('test' USING utf8) COLLATE utf8_bin;
SELECT CONVERT('test', CHAR CHARACTER SET utf8) COLLATE utf8_bin;
SELECT CAST('test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

collate是指定排序规则关键字，详细分析可参考 https://cloud.tencent.com/developer/article/1366841?fromSource=waitui

2、字符类型转换成二进制字符

CAST(expr AS BINARY)
CONVERT(expr USING BINARY)
BINARY expr

mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT BINARY 'a' = 'A';
        -> 0
mysql> SELECT 'a' = 'a ';
        -> 1
mysql> SELECT BINARY 'a' = 'a ';
        -> 0

2、字符类型转换

CAST(expr AS CHAR)
CONVERT(expr, CHAR)

-- 语法规则
CAST(expr AS type)
CONVERT(expr, type)

type:
BINARY  -- 二进制字符
CHAR    -- 字符
DATE    -- 日期
DATETIME -- 日期时间
TIME    -- 时间
DECIMAL -- 带精度数字
JSON    -- JSON
NCHAR   -- 类似CHAR，但会生成一个带有国家字符集的字符串。
SIGNED  -- 有符号整数值
UNSIGNED -- 无符号整数值