DBA【八股文】04：MySQL和Oracle的区别【核心语法、数据类型、函数与运算符、高级特性】

Oracle 和 MySQL 作为两种主流的关系型数据库，在 SQL 语法和功能特性上存在诸多差异。

以下从​​核心语法、数据类型、函数与运算符、高级特性​​等维度，系统总结两者的主要区别（附示例说明）：

一、核心语法差异

1、自增列

（1）Oracle

传统方式通过 SEQUENCE（序列）+ 触发器（Trigger）实现自增；Oracle 12c 及以上支持 IDENTITY 列（简化版自增）。示例（12c+）：

CREATE TABLE t (id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);

（2）MySQL

直接通过 AUTO_INCREMENT 属性实现，简单高效。示例：

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY);

2、分页查询

（1）Oracle

依赖 ROWNUM 伪列（表示返回的行号），需通过子查询嵌套实现分页。示例（查询第 11-20 条数据）：

SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM emp t WHERE ROWNUM <= 20
) WHERE rn > 10;

（2）MySQL

使用 LIMIT 关键字直接指定偏移量和数量。示例（同上）：

SELECT * FROM emp LIMIT 10, 10; -- 偏移量 10，取 10 条

3、外连接语法

（1）Oracle

支持 (+) 操作符表示外连接（左/右外连接）。示例（左外连接）：

SELECT a.name, b.dept FROM emp a, dept b WHERE a.dept_id = b.id(+);

（2）MySQL

强制使用标准 LEFT JOIN/RIGHT JOIN 语法。示例（同上）：

SELECT a.name, b.dept FROM emp a LEFT JOIN dept b ON a.dept_id = b.id;

4、临时表

（1）Oracle

支持​​全局临时表（GTT）​​，数据仅在会话或事务期间存在，且定义对所有会话可见。示例：

CREATE GLOBAL TEMPORARY TABLE temp_emp (
  id NUMBER,
  name VARCHAR2(50)
) ON COMMIT PRESERVE ROWS; -- 事务提交后保留数据

（2）MySQL

仅支持​​会话级临时表​​，数据仅在当前会话可见，且定义对其他会话不可见。示例：

CREATE TEMPORARY TABLE temp_emp (
  id INT,
  name VARCHAR(50)
); -- 会话关闭后自动删除

二、数据类型差异

三、函数与运算符差异

1、字符串拼接

（1）Oracle

使用 || 运算符（默认行为），或 CONCAT(str1, str2)（仅支持两个参数）。示例：

SELECT 'Hello' || ' ' || 'World' FROM DUAL; -- 结果："Hello World"

（2）MySQL

默认 || 表示逻辑或（需设置 sql_mode=PIPES_AS_CONCAT 改为拼接）；推荐使用 CONCAT(str1, str2, …)（支持多参数）。示例：

SELECT CONCAT('Hello', ' ', 'World'); -- 结果："Hello World"（默认行为）
SET sql_mode='PIPES_AS_CONCAT'; SELECT 'Hello' || ' ' || 'World'; -- 结果同上（需修改模式）

2、字符串长度

（1）Oracle

LENGTH(str) 返回字符数（按字符集编码计算，如中文占 1 字符）；
LENGTHB(str) 返回字节数（如 UTF-8 中文占 3 字节）。

（2）MySQL

CHAR_LENGTH(str) 返回字符数；
LENGTH(str) 返回字节数（与 Oracle 的 LENGTHB 类似）。

3、空值处理

（1）Oracle

使用 NVL(expr, default_val) 处理空值（若 expr 为 NULL，返回 default_val）。

（2）MySQL

使用 IFNULL(expr, default_val)（功能与 NVL 一致），或标准 SQL 的 COALESCE(expr1, expr2, …)（支持多个参数）。

4、日期函数

（1）Oracle

• SYSDATE：返回当前日期时间（DATE 类型，精度秒）；
• SYSTIMESTAMP：返回当前时间戳（TIMESTAMP WITH TIME ZONE 类型，含时区）；
• MONTHS_BETWEEN(date1, date2)：计算两个月份之间的月数差（结果为浮点数）。

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2023-01-01', 'YYYY-MM-DD')) AS months_diff FROM DUAL;

（2）MySQL

• NOW()/CURRENT_TIMESTAMP()：返回当前日期时间（DATETIME 或 TIMESTAMP 类型）；
• TIMESTAMPDIFF(unit, start, end)：计算两个时间的时间差（unit 支持 YEAR/MONTH/DAY 等）。

示例（计算月份差）：

SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', CURDATE()) AS months_diff;

5、条件判断

（1）Oracle

使用 DECODE(expr, val1, res1, val2, res2, …, default) 函数实现多条件判断（类似 CASE WHEN）。 示例：

SELECT DECODE(salary, 10000, '高', 8000, '中', '低') AS level FROM emp;

（2）MySQL

无 DECODE 函数，需用标准 CASE WHEN 语法替代。 示例（同上）：

SELECT 
  CASE 
    WHEN salary = 10000 THEN '高' 
    WHEN salary = 8000 THEN '中' 
    ELSE '低' 
  END AS level 
FROM emp;

6、类型转换

（1）Oracle

使用 TO_CHAR(date, format) 转换日期为字符串，TO_DATE(str, format) 转换字符串为日期。 示例：

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS now_str FROM DUAL;
SELECT TO_DATE('2023-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS date_val FROM DUAL;

（2）MySQL

使用 DATE_FORMAT(date, format) 转换日期为字符串，STR_TO_DATE(str, format) 转换字符串为日期。 示例（同上）：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS now_str;
SELECT STR_TO_DATE('2023-01-01 12:00:00', '%Y-%m-%d %H:%i:%s') AS date_val;

四、高级特性差异

1、序列

（1）Oracle

原生支持 SEQUENCE 对象，用于生成全局唯一的递增值（可独立于表存在）。示例：

CREATE SEQUENCE seq_emp START WITH 1 INCREMENT BY 1;
SELECT seq_emp.NEXTVAL FROM DUAL; -- 获取下一个值（结果：1）

（2）MySQL

无原生 SEQUENCE，但 8.0+ 支持通过 AUTO_INCREMENT 模拟自增，或通过存储过程/触发器手动实现序列（需额外代码）。

2、窗口函数

（1）Oracle

10g 及以上版本支持窗口函数（如 ROW_NUMBER()、RANK()、SUM() OVER() 等），用于复杂分组计算。 示例（计算员工工资排名）：

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank 
FROM emp;

（2）MySQL

仅 8.0+ 版本支持窗口函数（功能与 Oracle 类似，但部分函数名称或行为可能不同）。

3、物化视图

（1）Oracle

支持物化视图（物理存储查询结果，定期刷新），用于优化复杂查询性能。 示例：

CREATE MATERIALIZED VIEW mv_emp_dept 
BUILD IMMEDIATE 
REFRESH COMPLETE EVERY 1 DAY 
AS SELECT e.name, d.dept_name FROM emp e JOIN dept d ON e.dept_id = d.id;

（2）MySQL

不支持物化视图，需通过触发器、事件调度（EVENT）或定时任务手动维护汇总表。

4、触发器

（1）Oracle

支持行级触发器（FOR EACH ROW）和语句级触发器，可在触发时访问 :OLD（旧值）和 :NEW（新值）。 示例（更新前记录旧值）：

CREATE TRIGGER trg_emp_update 
BEFORE UPDATE ON emp 
FOR EACH ROW 
BEGIN
  :NEW.update_time := SYSDATE; -- 自动更新修改时间
END;

（2）MySQL

语法类似，但 :OLD/:NEW 需改为 OLD.column/NEW.column，且不支持 FOR EACH ROW 显式声明（默认行级触发）。 示例（同上）：

CREATE TRIGGER trg_emp_update 
BEFORE UPDATE ON emp 
FOR EACH ROW 
BEGIN
  SET NEW.update_time = NOW(); -- 自动更新修改时间
END;

五、总结

Oracle 和 MySQL 的差异本质上源于设计定位：

Oracle 是企业级数据库，强调功能全面性和高可靠性；MySQL 是轻量级开源数据库，注重易用性和性能。

实际开发中需根据业务场景选择，若涉及复杂事务、大数据量分析，Oracle 更合适；若为互联网高并发场景，MySQL（或其衍生版如 TiDB）更灵活。