类型转换

类型转换是数据处理中的基础操作，本篇介绍各数据库的类型转换方法。

📊 支持情况

1. 显式类型转换

CAST 语法

-- MySQL
SELECT CAST('123' AS UNSIGNED) AS int_val;
SELECT CAST('2024-01-14' AS DATE) AS date_val;
SELECT CAST('123.45' AS DECIMAL(10,2)) AS decimal_val;

-- Oracle
SELECT CAST('123' AS NUMBER) FROM dual;
SELECT CAST('2024-01-14' AS DATE) FROM dual;

-- Hive
SELECT CAST('123' AS INT);
SELECT CAST('2024-01-14' AS DATE);

CONVERT 语法

-- MySQL - 指定转换格式
SELECT CONVERT('2024-01-14', DATE);
SELECT CONVERT('123.45', DECIMAL(10,2));
SELECT CONVERT('hello' USING utf8);

-- Oracle
SELECT TO_NUMBER('123') FROM dual;
SELECT TO_DATE('2024-01-14', 'YYYY-MM-DD') FROM dual;
SELECT TO_CHAR(12345.67, '999,999.99') FROM dual;

2. 日期类型转换

字符串转日期

-- MySQL
SELECT STR_TO_DATE('2024-01-14 10:30:00', '%Y-%m-%d %H:%i:%s');

-- Oracle
SELECT TO_DATE('2024-01-14 10:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- Hive
SELECT to_date('2024-01-14');
SELECT from_unixtime(unix_timestamp('2024-01-14', 'yyyy-MM-dd'));

日期转字符串

-- MySQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');

-- Oracle
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- Hive
SELECT date_format(NOW(), 'yyyy-MM-dd HH:mm:ss');

3. 数字类型转换

精度控制

-- MySQL - 数值精度
SELECT CAST('123.4567' AS DECIMAL(10,2));  -- 123.46
SELECT ROUND(CAST('123.4567' AS DECIMAL(10,2)), 2);

-- Oracle
SELECT ROUND(TO_NUMBER('123.4567'), 2) FROM dual;

-- Hive
SELECT round(cast('123.4567' as double), 2);

进制转换

-- MySQL
SELECT CONV('FF', 16, 10);  -- 255 (16进制转10进制)
SELECT CONV('255', 10, 16);  -- FF (10进制转16进制)

-- Oracle
SELECT TO_NUMBER('FF', 'XX') FROM dual;
SELECT TO_CHAR(255, 'XX') FROM dual;

4. 实战案例

场景1：数据清洗

-- MySQL - 清洗脏数据
SELECT
    CAST(COALESCE(price, 0) AS DECIMAL(10,2)) AS clean_price,
    CAST(date_str AS DATE) AS clean_date,
    CAST(quantity AS UNSIGNED) AS clean_quantity
FROM raw_data;

场景2：格式化输出

-- Oracle - 格式化数字
SELECT
    TO_CHAR(amount, 'L999,999,999.99') AS formatted_amount,
    TO_CHAR(salary, '999,999') AS formatted_salary
FROM employees;

场景3：JSON类型转换

-- MySQL - JSON转数值
SELECT
    JSON_EXTRACT(data, '$.price') AS raw_price,
    CAST(JSON_EXTRACT(data, '$.price') AS UNSIGNED) AS price_int
FROM orders;

场景4：时间戳转换

-- Hive - 时间戳转换
SELECT
    from_unixtime(unix_timestamp(ts_col)) AS datetime_val,
    cast(ts_col as date) AS date_val,
    cast(ts_col as timestamp) AS timestamp_val
FROM timestamp_data;

5. 隐式转换

自动转换

-- MySQL - 数字与字符串
SELECT '1' + 1;  -- 2（字符串转数字）

-- 注意事项
SELECT 'abc' + 1;  -- 1（返回警告但返回0）
SELECT 1 = '1';   -- 1（相等）

避免隐式转换

-- ✅ 显式转换
WHERE CAST(col AS SIGNED) = 123;

-- ❌ 隐式转换（可能不走索引）
WHERE col = 123;  -- 如果col是字符串类型

⚠️ 注意事项

1. 转换失败

-- MySQL - 无效转换返回0
SELECT CAST('abc' AS UNSIGNED);  -- 0

-- Oracle - 报错
SELECT TO_NUMBER('abc') FROM dual;  -- ORA-01722

-- 使用安全转换
SELECT NULLIF(CAST('abc' AS UNSIGNED), 0);  -- NULL

2. 时区转换

-- 日期时间转换时注意时区
SELECT CONVERT_TZ(CAST('2024-01-14 10:00:00' AS DATETIME), 'UTC', '+08:00');

3. 精度丢失

-- 大数转换
SELECT CAST('9999999999999999999' AS UNSIGNED);  -- 可能溢出

📝 练习题

-- 建表
CREATE TABLE data_convert (
    id INT,
    str_num STRING,
    str_date STRING,
    amount_str STRING
);

INSERT INTO data_convert VALUES
(1, '123', '2024-01-14', '99.99'),
(2, '456', '2024-01-15', '199.99'),
(3, 'abc', 'invalid', '299.99');

要求：

1. 将字符串数字转换为整数
2. 将字符串日期转换为日期类型
3. 将金额转换为两位小数
4. 安全转换（无效值返回NULL）

-- 参考答案（MySQL）
-- 1. 字符串转整数
SELECT CAST(str_num AS UNSIGNED) AS int_val FROM data_convert;

-- 2. 字符串转日期
SELECT CAST(str_date AS DATE) AS date_val FROM data_convert;

-- 3. 金额格式化
SELECT CAST(amount_str AS DECIMAL(10,2)) AS amount FROM data_convert;

-- 4. 安全转换
SELECT
    NULLIF(CAST(str_num AS UNSIGNED), 0) AS safe_int,
    NULLIF(CAST(str_date AS DATE), 0) AS safe_date
FROM data_convert;