前言
小型項目中使用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，說明找不到 |
| 字符串長度 | 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 | |
| 編碼取字符 | STR_TO_DATE | TO_TIMESTAMP | |
| 正則匹配替換 | - | REGEXP_REPLACE | |
| 記錄聚合 | GROUP_CONCAT | LISTAGG | |
| 行號 | ROW_NUMBER() OVER (ORDER BY col1) | ROWNUM | |
)
