CAST(expr AS type) 可用作mysql 查询时结果的类型转换,参数意思为CAST(待转换的数据 AS 期望转换的目标类型)
sql 可转换的数据类型列表:
数据库类型 | 类型说明 |
---|---|
DATETIME | 年月日,时分秒 |
DATE | 年月日 |
TIME | 时分秒 |
SIGNED | 整形 |
CHAR | 字符串 |
DECIMAL(a,b) | 小数,可精确位数 |
1:DATETIME–>字符串转具体时间
SELECT CAST('2019-08-29 16:50:21' as DATETIME) as result;
2019-08-29 16:50:21
2:DATE–>字符串转具体日期
SELECT CAST('2019-08-29 16:50:21' as date) as result;
2019-08-29
3:TIME–>字符串转具体时分秒
SELECT CAST('2019-08-29 16:50:21' as TIME) as result;
16:50:21
4:SIGNED–>字符串转整形数字
SELECT CAST('20' as UNSIGNED) as result;
20
5:CHAR -->数字转字符串
SELECT CAST(20 as CHAR) as result;
6:DECIMAL(a,b)–>字符串转小数,可精确小数位
SELECT CAST(20.678787 as DECIMAL(10,3)) as result;
20.679