HoRain云--MySQL与Oracle语法核心差异详解-开发者社区

🎬 HoRain 云小助手：个人主页

⛺️生活的理想，就是为了理想的生活!

⛳️ 推荐

前些天发现了一个超棒的服务器购买网站，性价比超高，大内存超划算！忍不住分享一下给大家。点击跳转到网站。

⛳️ 推荐

MySQL 与 Oracle 语法核心区别详解

MySQL 与 Oracle 语法核心区别详解

一、基础查询差异

1. FROM子句要求

-- Oracle：必须包含FROM子句，无表时需用虚拟表 SELECT SYSDATE FROM DUAL; -- MySQL：允许省略FROM直接查询 SELECT NOW();

2. 分页查询实现

-- Oracle：使用ROWNUM伪列，需嵌套子查询 SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM employees ORDER BY id ) t WHERE ROWNUM <= 20 ) WHERE rn > 10; -- MySQL：直接通过LIMIT简化分页 SELECT * FROM employees ORDER BY id LIMIT 10, 10; -- 跳过10条，取10条

二、数据类型差异

1. 字符串类型

-- Oracle VARCHAR2(4000) -- 变长字符串，最大4000字节（12c后可扩展至32767） CHAR(2000) -- 定长字符串，最大2000字节 -- 注意：Oracle不区分NULL和空字符串，空字符串会被转换为NULL -- MySQL VARCHAR(65535) -- 变长字符串，最大65535字节 CHAR(255) -- 定长字符串，最大255字节 -- 注意：MySQL区分NULL和空字符串

2. 数值类型

-- Oracle NUMBER(10,2) -- 总共10位，小数点后2位 -- MySQL DECIMAL(10,2) -- 等同于Oracle的NUMBER INT -- 整数类型

3. 日期类型

-- Oracle DATE -- 包含日期和时间 TIMESTAMP -- 包含日期、时间和时区 -- MySQL DATE -- 仅包含日期（YYYY-MM-DD） DATETIME -- 包含日期和时间（YYYY-MM-DD HH:MM:SS） TIMESTAMP -- 包含日期和时间，范围较小

三、自增主键实现

-- MySQL：通过AUTO_INCREMENT自动管理 CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); -- Oracle：需手动创建序列实现自增 CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1; CREATE TABLE users ( id NUMBER PRIMARY KEY, name VARCHAR2(50) ); -- 插入数据时使用序列 INSERT INTO users VALUES (user_seq.NEXTVAL, 'John');

四、字符串与日期函数

1. 字符串函数

-- 获取字符串长度 -- Oracle SELECT LENGTH('Hello') FROM DUAL; -- 返回5 -- MySQL SELECT CHAR_LENGTH('Hello'); -- 返回5 -- 查找子串位置（注意参数顺序相反） -- Oracle SELECT INSTR('Hello World', 'World') FROM DUAL; -- 返回7 -- MySQL SELECT LOCATE('World', 'Hello World'); -- 返回7

2. 日期函数

-- 获取当前时间 -- Oracle SELECT SYSDATE FROM DUAL; SELECT SYSTIMESTAMP FROM DUAL; -- MySQL SELECT NOW(); SELECT CURRENT_TIMESTAMP; -- 日期格式化 -- Oracle SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- MySQL SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

五、高级语法支持

1. 递归查询

-- Oracle：提供专门的START WITH ... CONNECT BY PRIOR语法 SELECT employee_id, manager_id, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id; -- MySQL：8.0+版本支持WITH RECURSIVE WITH RECURSIVE employee_hierarchy AS ( SELECT employee_id, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.manager_id, eh.level + 1 FROM employees e INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM employee_hierarchy;

2. 公共表表达式(CTE)

-- Oracle：支持WITH语法 WITH dept_avg AS ( SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id ) SELECT e.name, e.salary, d.avg_sal FROM employees e JOIN dept_avg d ON e.department_id = d.department_id; -- MySQL：8.0+版本支持WITH RECURSIVE WITH dept_avg AS ( SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id ) SELECT e.name, e.salary, d.avg_sal FROM employees e JOIN dept_avg d ON e.department_id = d.department_id;

六、DML语句差异

1. 删除语句

-- Oracle：两种格式都合法，可以使用别名 DELETE FROM table_name; DELETE table_name; DELETE FROM table_name t WHERE t.id = 1; -- MySQL：必须使用DELETE FROM格式，不能直接在DELETE后使用别名 DELETE FROM table_name; DELETE FROM table_name WHERE id = 1; -- 不能写成：DELETE table_name t WHERE t.id = 1;

2. 更新语句

-- Oracle：支持在UPDATE中使用子查询 UPDATE employees e SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) WHERE department_id = 10; -- MySQL：语法类似，但有一些限制 UPDATE employees e JOIN (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) d ON e.department_id = d.department_id SET e.salary = d.avg_sal WHERE e.department_id = 10;

七、其他重要差异

1. 字符串拼接

-- Oracle：使用||操作符 SELECT 'Hello' || ' ' || 'World' FROM DUAL; -- MySQL：使用CONCAT函数或||（需设置PIPES_AS_CONCAT） SELECT CONCAT('Hello', ' ', 'World'); -- 或设置：SET sql_mode='PIPES_AS_CONCAT'; -- 然后：SELECT 'Hello' || ' ' || 'World';

2. 事务控制

-- Oracle：默认开启事务，需要显式COMMIT UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10; COMMIT; -- 或 ROLLBACK; -- MySQL：取决于存储引擎，InnoDB支持事务 -- 默认自动提交，可通过设置关闭 SET autocommit = 0; UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10; COMMIT; -- 或 ROLLBACK;

3. 空值处理

-- Oracle：空字符串会被转换为NULL INSERT INTO test_table (name) VALUES (''); -- 实际存储为NULL -- MySQL：区分空字符串和NULL INSERT INTO test_table (name) VALUES (''); -- 存储为空字符串 INSERT INTO test_table (name) VALUES (NULL); -- 存储为NULL