开发过程中使用MySQL和Oracle的差异

前言

小型项目中使用MySQL的占比还是相对较高的，但是也不排除随着项目的扩大，产品的丰富，或者甲方的财大气粗，有可能会有MySQL换成Oracle。那么这两者对于开发者而言，有什么差异化的地方呢。

官方文档

• MySQL5.7 https://dev.mysql.com/doc/refman/5.7/en/
• MySQL8.4 https://dev.mysql.com/doc/refman/8.4/en/
• Oracle12 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/
• Oracle 19 https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/

关键字和保留字

如果表或者字段有关键字或保留字，MySQL用 `KeyWord` 来标识，而Oracle用 “KeyWord” 来标识。
为了不必要的兼容性工作，尽量避免使用这些关键字和保留字。

MySQL关键字：https://dev.mysql.com/doc/refman/8.4/en/keywords.html
Oracle保留字：https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Oracle-SQL-Reserved-Words.html

常用数据类型

数据类型	MySQL	Oracle
字符串	VARCHAR	VARCHAR2
数字	BIT, TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE	NUMBER
大文本	TEXT, MEDIUMTEXT, LONGTEXT	CLOB
日期	DATETIME	DATE
枚举	ENUM	-
二进制	BLOB, MEDIUMBLOB,LONGBLOB	BLOB

常用DDL（仅差异部分）

DDL功能	MySQL	Oracle
自增字段

常用DML（仅差异部分）

DML功能	MySQL	Oracle
数据合并	MERGE INTO	REPLACE INTO
子查询	– 子查询必须指定别名 SELECT * FROM (SELECT * FROM t) t1	– 子查询可以不指定别名 SELECT * FROM (SELECT * FROM t)
TOP n 查询	SELECT * FROM t ORDER BY f LIMIT n	SELECT * FROM（SELECT * FROM t ORDER BY f） WHERE ROWNUM > n
分页查询	SELECT * FROM t ORDER BY f LIMIT 页号*分页大小, 分页大小	SELECT * FROM（SELECT * FROM t ORDER BY f） WHERE ROWNUM >= 页号*分页大小 AND ROWNUM < 页号*(分页大小 + 1)
递归查询	– tree的列名可以不指定，默认使用root的列名 WITH RECURSIVE tree AS (  SELECT root.id, root.name, root.parent_id  FROM t_dept root  WHERE root.parent_id is null  UNION ALL  SELECT sub.id, sub.name, sub.parent_id  FROM t_dept, tree  WHERE sub.parent_id = tree.id ) SELECT * FROM tree;	– tree的列名必须指定 WITH tree (id,name,parent_id) AS (  SELECT root.id, root.name, root.parent_id  FROM t_dept root  WHERE root.parent_id is null  UNION ALL  SELECT sub.id, sub.name, sub.parent_id  FROM t_dept, tree  WHERE sub.parent_id = tree.id ) SELECT * FROM tree; – 使用CONNECT BY实现递归查询 SELECT id, name, parent_id FROM t_dept START WITH parent_id is null CONNECT BY PRIOR id = parent_id

常用函数

函数	MySQL	Oracle	说明
逻辑判断	IF(expr1,expr2,expr3)	-	expr1为true，返回expr2，否则返回expr3
空值判断	IFNULL(a, b) 或 COALESCE(a, b, c)	NVL(a,b) 或 COALESCE(a, b, c)	返回第一个不为null的值
同值判断	NULLIF(a, b)	NULLIF(a, b)	如果两个值相同，返回null，如果不同，返回a
字符串截取	SUBSTR(ch, pos, length) SUBSTRING(ch, pos, length)	SUBSTR(ch, pos, length)	截取ch，从pos开始，共length位
字符串查找	LOCATE(substr, str)	INSTR(str, substr)	在str中查找substr的位置，如果结果为0，说明找不到
正则匹配替换	-	REGEXP_REPLACE
字符串长度	CHAR_LENGTH(str) LENGTH(str)	LENGTH(str)	MySQL的LENGTH(chr)表示字节长度: CHAR_LENGTH(‘A’) = 1 LENGTH(‘A’) = 1 CHAR_LENGTH(‘我’) = 1 LENGTH(‘我’) = 3 而Oracle中: LENGTH(‘我’) = 1
大小写转换	UPPER(string)、LOWER(string)	UPPER(string)、LOWER(string)
向上取整	CEILING(f) CEIL(f)	CEIL(f)	CEIL(110.01) = 111
向下取整	FLOOR(f)	FLOOR(f)	FLOOR(110.99) = 110
数字精度	TRUNC(f, p)	TRUNC(f, p)	TRUNC(15.79,1) = 15.7
四舍五入	ROUND(f, p)	ROUND(f, p)	ROUND(15.153,1) = 15.2
获取当前时间	NOW() SYSDATE() CURDATE() CURTIME() CURRENT_DATE() CURRENT_DATE CURRENT_TIME() CURRENT_TIME CURRENT_TIMESTAMP() CURRENT_TIMESTAMP	SYSDATE CURRENT_DATE CURRENT_TIMESTAMP	NOW()表示整个SQL语句(包含存储过程)执行的时间 SYSDATE()表示该函数调用时的时间
日期转字符	DATE_FORMAT(date,format)	TO_CHAR	DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%s’) TO_CHAR(SYS_DATE, ‘yyyy-mm-dd hh24:mi:ss’)
日期加减	DATE_ADD(date, days) DATE_SUB(date, days) ADDDATE(date, days) SUBDATE(expr, days) ADDTIME(expr1, expr2) SUBTIME(expr1, expr2)	DATE +/- N/ ADD_MONTHS
日期互减	DATEDIFF(expr1, expr2) TIMEDIFF(expr1,expr2)	-
时间截取	YEAR(date) MONTH(date) DAYOFWEEK(date) DAYOFMONTH(date) WEEK(date[,mode]) DAY(date) HOUR(time) MINUTE(time)	TRUNC(date, fmt)
时间抽取	YEAR	EXTRACT
字符转日期	STR_TO_DATE, CAST(XX AS DATE)	TO_DATE
字符转整数	CAST(XX AS SIGNED INTEGER)	TO_NUMBER
字符转时间戳	STR_TO_DATE	TO_TIMESTAMP
记录聚合	GROUP_CONCAT	LISTAGG
行号	ROW_NUMBER() OVER (ORDER BY col1)	ROWNUM