MYSQL中CAST函数
CAST函数用于将值从一种转换数据类型的方法
语法:
SELECT CAST(x AS type);
x: 要处理的数据
type: 要转换的数据类型, 取值下方表格
type取值
| 值 | 描述 |
|---|---|
| DATE | 将value转换成'YYYY-MM-DD'格式 |
| TIME | 将value转换成'HH：MM：SS'格式 |
| DATETIME | 将value转换成'YYYY-MM-DD HH：MM：SS'格式 |
| CHAR | 将value转换成CHAR(固定长度的字符串)格式 |
| SIGNED | 将value转换成INT(有符号的整数)格式 |
| UNSIGNED | 将value转换成INT(无符号的整数)格式 |
| BINARY | 将value转换成二进制格式 |
| DECIMAL | 将value转换成FLOAT(浮点数)格式 |
例:
- DATE
SELECT CAST(NOW() AS DATE);
-- 结果: 2023-04-03
-- NOW()函数返回当前日期和时间
SELECT CAST('2023/04/03' AS DATE);
-- 结果: 2023-04-03
- TIME
SELECT CAST('2023/04/03 16:00:00' AS TIME);
-- 结果: 16:00:00
- DATETIME
SELECT CAST('2023/04/03' AS DATETIME);
-- 结果: 2023-04-03 00:00:00
- CHAR
SELECT CAST(500 AS CHAR);
- SIGNED
SELECT CAST('9.9' AS SIGNED);
-- 结果: 9
SELECT CAST('-9.9' AS SIGNED);
-- 结果: -9
SELECT CAST(9.9 AS SIGNED);
-- 结果: 10
SELECT CAST(-9.2 AS SIGNED);
-- 结果: -9
- UNSIGNED
SELECT CAST(-9.9 AS UNSIGNED);
-- 结果: 0
SELECT CAST('9.9' AS UNSIGNED);
-- 结果: 9
SELECT CAST(9.9 AS UNSIGNED);
-- 结果: 10
SELECT CAST(9.2 AS UNSIGNED);
-- 结果: 9
- BINARY
SELECT CAST(99 AS Binary);
-- 结果: 99
- DECIMAL
DECIMAL(数值精度，小数点保留长度)
数值精度是总的数字位数, 例: 9.2222数值精度为5
DECIMAL(9,2)可以存储最多具有7位整数和2位小数的数字, 例: 1234567.89
SELECT CAST('9.2' AS DECIMAL);
-- 结果: 9
SELECT CAST('9.9' AS DECIMAL);
-- 结果: 10
SELECT CAST(1234567.89 AS DECIMAL(9,2));
-- 结果: 1234567.89
SELECT CAST(1234567.89 AS DECIMAL(8,2));
-- 结果: 999999.99