SQL 数据类型转换

SQL 数据类型转换学习笔记

在 SQL 中，数据类型转换是将数据从一种类型转换为另一种类型的过程。数据类型转换可以分为 隐式转换 和 显式转换。

1. 隐式转换

隐式转换是数据库自动进行的类型转换，通常发生在表达式中涉及不同数据类型的操作时。

示例：

-- 隐式转换：将字符串 '17-12月 1980' 转换为日期类型
SELECT hiredate
FROM tmp_emp
WHERE hiredate = '17-12月 1980';

-- 隐式转换：将字符串 '12' 转换为数字类型，然后与 20 相加
SELECT '12' + 20 FROM dual; -- 结果：32

注意：

• 隐式转换依赖于数据库的自动类型推断，可能会导致不可预见的错误。
• 隐式转换的性能较低，因为数据库需要额外的时间进行类型推断。

2. 显式转换

显式转换是通过 SQL 提供的转换函数手动将数据从一种类型转换为另一种类型。常见的显式转换函数包括：

（1）TO_DATE：将字符串转换为日期

-- 将字符串 '19801217' 转换为日期类型
SELECT hiredate
FROM tmp_emp
WHERE hiredate = TO_DATE('19801217', 'yyyymmdd');

参数说明：

• 第 1 个参数：源字符串。
• 第 2 个参数：日期格式模型（如 yyyymmdd）。

（2）TO_CHAR：将日期或数字转换为字符串

-- 将当前日期转换为字符串
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') FROM dual;

-- 将数字 123.45 转换为字符串
SELECT TO_CHAR(123.45, '999.99') FROM dual; -- 结果：'123.45'

（3）TO_NUMBER：将字符串转换为数字

-- 将字符串 '123.45' 转换为数字
SELECT TO_NUMBER('123.45') FROM dual; -- 结果：123.45

---

3. 隐式转换 vs 显式转换

特性	隐式转换	显式转换
定义	数据库自动进行的类型转换	通过 SQL 函数手动进行的类型转换
性能	较低（需要类型推断）	较高（明确指定类型）
可读性	较差（隐藏了类型转换逻辑）	较好（明确显示类型转换逻辑）
错误风险	较高（可能导致不可预见的错误）	较低（明确控制类型转换）
适用场景	简单的数据类型转换	复杂的数据类型转换

4. 综合示例

以下是一些综合示例，展示了隐式转换和显式转换的实际应用。

（1）查询指定日期的员工信息

-- 隐式转换
SELECT hiredate
FROM tmp_emp
WHERE hiredate = '17-12月 1980';

-- 显式转换
SELECT hiredate
FROM tmp_emp
WHERE hiredate = TO_DATE('19801217', 'yyyymmdd');

（2）将数字转换为字符串并进行拼接

-- 隐式转换
SELECT '员工编号：' || empno
FROM tmp_emp;

-- 显式转换
SELECT '员工编号：' || TO_CHAR(empno)
FROM tmp_emp;

（3）将字符串转换为数字并进行计算

-- 隐式转换
SELECT '12' + 20 FROM dual; -- 结果：32

-- 显式转换
SELECT TO_NUMBER('12') + 20 FROM dual; -- 结果：32

---

5. 注意事项

1. 隐式转换的风险：

   • 隐式转换可能导致不可预见的错误，例如将非数字字符串转换为数字时会报错。
   • 隐式转换的性能较低，尤其是在大数据量查询时。

2. 显式转换的优势：

   • 显式转换明确指定了数据类型，提高了代码的可读性和可维护性。
   • 显式转换可以避免隐式转换带来的错误风险。

3. 日期格式模型：

   • 在使用 TO_DATE 或 TO_CHAR 函数时，必须指定正确的日期格式模型，否则会导致转换失败。

6. 练习

以下是一些练习题及其解答。

练习 1

将字符串 ‘2023-10-01’ 转换为日期类型，并查询该日期的员工信息

SELECT *
FROM tmp_emp
WHERE hiredate = TO_DATE('2023-10-01', 'yyyy-mm-dd');

练习 2

将当前日期转换为字符串，并拼接为固定格式

SELECT '今天是：' || TO_CHAR(SYSDATE, 'yyyy-mm-dd') FROM dual;

练习 3

将字符串 ‘123.45’ 转换为数字，并计算其与 100 的差值

SELECT TO_NUMBER('123.45') - 100 FROM dual; -- 结果：23.45

总结

1. 隐式转换：数据库自动进行的类型转换。
2. 显式转换：通过 SQL 函数手动进行的类型转换。
3. 常用转换函数：TO_DATE、TO_CHAR、TO_NUMBER。