將 Oracle 使用者遷移至 MySQL 適用的 Cloud SQL：查詢、儲存的程序、函式和觸發事件

本文是一系列文章之一，提供有關規劃及執行 Oracle® 11g/12c 資料庫遷移至 Cloud SQL for MySQL 5.7 版本第二代執行個體的相關重要資訊和指引。本系列包含以下部分：

查詢

Oracle 和 MySQL 適用的 Cloud SQL 支援 ANSI SQL 標準。一般來說，只要使用基本語法元素 (例如，不指定任何標量函式或任何其他 Oracle 擴充功能)，就能輕鬆遷移 SQL 陳述式。以下章節將說明常見的 Oracle 查詢元素，以及相應的 MySQL 適用 Cloud SQL 等效元素。

基本 SELECT 和 FROM 語法

Oracle 功能名稱或語法名稱 Oracle 總覽或實作 MySQL 支援 MySQL 對應或替代解決方案
資料擷取的 SQL 基本語法 
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
是 
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT 用於輸出沖印相片 
SELECT 1 FROM DUAL
是 
SELECT 1
OR
SELECT 1 FROM DUAL
欄別別名 
SELECT COL1 AS C1
是 
SELECT COL1 AS C1
OR
SELECT COL1 C1
資料表名稱
區分大小寫		 不區分大小寫
(例如，資料表名稱可以是 orders 和/或 ORDERS)。		 否 根據定義的資料表名稱區分大小寫 (例如，資料表名稱只能是 orders 或 ORDERS)。

您可以進一步瞭解 MySQL 的 SELECT 語法。

  • 內嵌檢視
    • 內嵌檢視畫面 (也稱為衍生資料表) 是 SELECT 陳述式，位於 FROM 子句中，用於做為子查詢。
    • 內嵌檢視畫面可移除複合計算或排除彙整作業，同時將多個個別查詢濃縮為單一簡易查詢，進而簡化複雜查詢。
    • 轉換注意事項：Oracle 內嵌檢視表不需要使用別名，但 MySQL 需要為每個內嵌檢視表使用特定別名。

下表列出從 Oracle 轉換為 MySQL 的範例，以內嵌檢視畫面呈現。

Oracle 11g/12c 
SQL> SELECT FIRST_NAME,
            DEPARTMENT_ID,
            SALARY,
            DATE_COL
     FROM EMPLOYEES,
          (SELECT SYSDATE AS DATE_COL FROM DUAL);

輸出結果會與下列內容相似：
FIRST_NAME           DEPARTMENT_ID     SALARY DATE_COL
-------------------- ------------- ---------- ---------
Steven                          90      24000 30-JUL-19
Neena                           90      17000 30-JUL-19
Lex                             90      17000 30-JUL-19
MySQL 適用的 Cloud SQL 5.7
沒有內嵌檢視畫面的別名：
mysql> SELECT FIRST_NAME,
              DEPARTMENT_ID,
              SALARY,
              DATE_COL
       FROM EMPLOYEES, (SELECT SYSDATE() AS DATE_COL FROM DUAL);


ERROR 1248 (42000): Every derived table must have its own alias

在內嵌檢視畫面中新增別名：
mysql> SELECT FIRST_NAME,
              DEPARTMENT_ID,
              SALARY,
              DATE_COL
       FROM EMPLOYEES, (SELECT SYSDATE() AS DATE_COL FROM DUAL) AS A1;

輸出結果會與下列內容相似：
+-------------+---------------+----------+---------------------+
| FIRST_NAME  | DEPARTMENT_ID | SALARY   | DATE_COL            |
+-------------+---------------+----------+---------------------+
| Steven      |            90 | 23996.00 | 2019-07-30 09:28:00 |
| Neena       |            90 | 22627.00 | 2019-07-30 09:28:00 |
| Lex         |            90 | 22627.00 | 2019-07-30 09:28:00 |

JOIN 陳述式

MySQL JOIN 陳述式支援 Oracle JOIN 陳述式，但 FULL JOIN 子句除外。此外，MySQL JOIN 陳述式支援使用其他語法，例如 USING 子句、WHERE 子句 (取代 ON 子句)，以及在 JOIN 陳述式中使用 SUBQUERY。

下表列出 JOIN 轉換的範例。

Oracle JOIN 類型 由 MySQL 支援 MySQL JOIN 語法 
INNER JOIN
是 
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

CROSS JOIN
是 
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D

FULL JOIN
否 如要解決這個問題，建議您使用 UNION 搭配 LEFT 和 RIGHT JOIN 陳述式。 
LEFT JOIN
[ OUTER ]
是 
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

RIGHT JOIN
[ OUTER ]
是 
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

SUBQUERY
是 
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN (SELECT * FROM DEPARTMENTS)D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

UNION、UNION ALL、INTERSECT 和 MINUS

MySQL 不支援 Oracle INTERSECT 和 MINUS 函式，但 UNION 和 UNION ALL 函式除外：

  • UNION：附加兩個以上 SELECT 陳述式的結果集，並刪除重複的記錄。
  • UNION ALL：附加兩個以上 SELECT 陳述式的結果集，但不刪除重複的記錄。
  • INTERSECT：只有在兩個資料集中都有記錄時，才會傳回兩個或多個 SELECT 陳述式的交集。
  • MINUS：比較兩個或多個 SELECT 陳述式，只傳回第一個查詢中其他陳述式未傳回的不同資料列。

轉換附註

將 Oracle INTERSECT 和 MINUS 函式轉換為 MySQL 時，請使用 JOIN 陳述式，並使用 IN 和 EXISTS 做為替代方案。

範例

Oracle 函式 Oracle 實作 MySQL 支援 MySQL 對應或替代解決方案 
UNION

SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2
是 
SELECT COL1 FROM TBL1
UNION
SELECT COL1 FROM TBL2

UNION ALL

SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2
是 
SELECT COL1 FROM TBL1
UNION ALL
SELECT COL1 FROM TBL2

INTERSECT

SELECT COL1 FROM TBL1
INTERSECT
SELECT COL1 FROM TBL2
否 
SELECT COL1 FROM TBL1
WHERE COL1 IN
(SELECT COL1 FROM TBL2)

MINUS

SELECT COL1 FROM TBL1
MINUS
SELECT COL1 FROM TBL2
否 
SELECT A.COL1
FROM TBL1 A LEFT JOIN TBL2 B
ON USING(COL1)
WHERE B.COL1 IS NULL

純量 (單一資料列) 和群組函式

MySQL 提供大量的標量 (單列) 和匯總函式。部分 MySQL 函式與 Oracle 函式相似 (名稱和功能相同，或名稱不同但功能相似)。雖然 MySQL 函式與 Oracle 函式可以有相同的名稱，但兩者可能會顯示不同的功能。

下表說明 Oracle 和 MySQL 在名稱和功能上是否相等 (以「是」指定)，以及建議轉換的情況 (所有「否」以外的情況)。

字元函式
Oracle 函式 Oracle 函式規格或實作 MySQL 等效項目 MySQL 對應函式 MySQL 函式規格或實作 
CONCAT(str1,str2)
傳回與 str2 連接的 str1：
CONCAT('A', 1) = A1
是 
CONCAT
等同於 Oracle：
CONCAT('A', 1) = A1

LOWER/UPPER
傳回 char，所有字母均為小寫或大寫：
LOWER('SQL') = sql
是 
LOWER/UPPER
等同於 Oracle：
LOWER('SQL') = sql

LPAD/RPAD(expr1,n,expr2)
傳回 expr1，左或右以 expr2 中的字元序列填入 n 個字元的長度：
LPAD('A',3,'*') = **A
是 
LPAD/RPAD
等同於 Oracle：
LPAD('A',3,'*') = **A

SUBSTR(char,p,n)
傳回 char 的一部分，從字元位置 p 開始，子字串長度為 n 個字元：
SUBSTR('MySQL', 3, 3)
= SQL
是 
SUBSTR(char,p,n)
等同於 Oracle：
SUBSTR('MySQL', 3, 3)
= SQL

INSTR(index,str)
傳回字串 str 的位置 (index)：
INSTR('MySQL', 'y')
= 2
是 
INSTR
等同於 Oracle：
INSTR('MySQL', 'y')
= 2

REPLACE(char,str1,str2)
傳回 char，其中每個搜尋字串都已替換為替換字串：
REPLACE('ORADB', 'ORA', 'MySQL')


= MySQLDB
是 
REPLACE(char,str1,str2)
等同於 Oracle：
REPLACE('ORADB', 'ORA', 'MySQL')


= MySQLDB

TRIM(str)
從字串中裁去開頭或結尾字元 (或兩者皆裁去)：
TRIM(both '-' FROM '-MySQL-')
= MySQL


TRIM('  MySQL ')
= MySQL
是 
TRIM(str)
等同於 Oracle：
TRIM(both '-' FROM '-MySQL-')
= MySQL


TRIM('  MySQL ')
= MySQL

LTRIM/RTRIM(str)
從字串的左側或右側移除所有出現在搜尋字串中的字元：
LTRIM('   MySQL', ' ')
= MySQL
部分支援 
LTRIM/RTRIM(str)
Oracle R/LTRIM 函式，除了參數 (空白或字串) 的替換。MySQL R/LTRIM 只會移除空格，只接受輸入字串：
LTRIM('   MySQL')
= MySQL

ASCII(char)
傳回資料庫字元集內 char 第一個字元的十進位表示法：
ASCII('A') = 65
是 
ASCII(char)
等同於 Oracle：
ASCII('A') = 65

CHR(char)
傳回 ASCII 程式碼值 (介於 0 和 225 之間的數值) 至字元：
CHR(65) = A
部分使用不同的函式名稱 
CHAR(char)
MySQL 會使用 CHAR 函式提供相同功能，因此您必須修改函式名稱：
CHAR(65) = A

LENGTH(str)
傳回指定字串的長度：

LENGTH ('MySQL') = 5
是 
LENGTH(str)
等同於 Oracle：
LENGTH('MySQL') = 5

REGEXP_REPLACE(str1,expr,str2)
搜尋字串中的規則運算式模式：
REGEXP_REPLACE('John', '[hn].', '1') = Jo1
否 不適用 僅支援 MySQL 8 以上版本，建議您盡可能使用 REPLACE 函式或轉換為應用程式層做為解決方法 
REGEXP_SUBSTR(str,expr)
透過搜尋字串中的規則運算式模式，擴充 SUBSTR 函式的功能：
REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?')
= https://console.cloud.google.com/
否 不適用 僅支援 MySQL 8 以上版本。如要解決這個問題，請盡可能使用 SUBSTR 函式，或將功能轉換至應用程式層。 
REGEXP_COUNT(str,expr)
傳回模式在來源字串中出現的次數。 否 不適用 如要採用其他解決方案，請將功能轉換至應用程式層。 
REGEXP_INSTR(index,expr)
搜尋字串位置 (索引) 的規則運算式模式。 否 不適用 僅支援 MySQL 8 以上版本。 
REVERSE(str)
傳回反轉的字串
REVERSE('MySQL')
= LQSyM
是 
REVERSE
等同於 Oracle：
REVERSE('MySQL')
= LQSyM
數值函式
Oracle 函式 Oracle 函式規格或實作 MySQL 等效項目 MySQL 對應函式 MySQL 函式規格或實作 
ABS(n)
n 的絕對值：
ABS(-4.6) = 4.6
是 
ABS
等同於 Oracle：
ABS(-4.6) = 4.6

CEIL(n)
傳回大於或等於 n 的最小整數：
CEIL(21.4) = 22
是 
CEIL
等同於 Oracle：
CEIL(21.4) = 22

FLOOR(n)
傳回等於或小於 n 的最大整數：
FLOOR(-23.7) = -24
是 
FLOOR
等同於 Oracle：
FLOOR(-23.7) = -24

MOD(m,n)
傳回 m 除以 n 後的餘數：
MOD(10, 3) = 1
是 
MOD(m,n)
等同於 Oracle：
MOD(10,3) = 1

ROUND(m,n)
傳回 m 四捨五入至小數點右側 n 個整數位數：
ROUND(1.39,1) = 1.4
是 
ROUND
等同於 Oracle：
ROUND(1.39,1) = 1.4

TRUNC(n1, n2)
 傳回 n1 截斷至 n2 小數位數：
TRUNC(99.999) = 99
TRUNC(99.999,0) = 99
部分使用不同的函式名稱 
TRUNCATE(n1, n2)
 MySQL TRUNCATE 函式必須接受輸入數字和整數，以指定小數點右側的準確度金額：
TRUNCATE(99.999,0) = 99
日期和時間函式
Oracle 函式 Oracle 函式規格或實作 MySQL 等效項目 MySQL 對應函式 MySQL 函式規格或實作 
SYSDATE
傳回資料庫伺服器所在作業系統的目前日期和時間設定：
SELECT SYSDATE
FROM DUAL
= 31-JUL-2019
部分支援 
SYSDATE()
MySQL SYSDATE() 必須包含括號，且傳回的日期/時間格式與 Oracle SYSDATE 函式不同：
SELECT SYSDATE()
FROM DUAL;
= 2019-01-31 10:01:01.0

請注意，您可以在工作階段層級變更日期/時間格式		 
SYSTIMESTAMP
傳回系統日期，包括秒數小數和時區：
SELECT SYSTIMESTAMP FROM DUAL
= 01-JAN-19 07.37.11.622187000 AM +00:00
部分使用不同的函式名稱 
CURRENT_TIMESTAMP
MySQL 傳回的日期/時間格式與 Oracle 不同。必須使用日期格式 (或其他日期函式) 才能符合原始日期/時間格式：
SELECT CURRENT_TIMESTAMP
FROM DUAL
= 2019-01-31 06:55:07

LOCAL_TIMESTAMP
以資料類型 TIMESTAMP 的值傳回目前的會話時區日期和時間：
SELECT LOCAL_TIMESTAMP
FROM DUAL
= 01-JAN-19 10.01.10.123456 PM
部分日期/時間格式不同。 
LOCAL_TIMESTAMP
MySQL 會傳回與 Oracle 不同的日期/時間格式。必須使用日期/時間格式 (或其他日期函式)，才能符合原始日期/時間格式：
SELECT LOCAL_TIMESTAMP
FROM DUAL
= 2019-01-01 10:01:01.0

CURRENT_DATE
以工作階段時區傳回目前日期：
SELECT CURRENT_DATE
FROM DUAL
= 31-JAN-19
部分日期/時間格式不同 
CURRENT_DATE
MySQL 傳回的日期/時間格式與 Oracle 不同。必須使用日期/時間格式 (或其他日期函式)，才能符合原始日期/時間格式：
SELECT CURRENT_DATE
FROM DUAL
= 2019-01-31

CURRENT_TIMESTAMP
以工作階段時區傳回目前的日期和時間：
SELECT CURRENT_TIMESTAMP
FROM DUAL
= 31-JAN-19 06.54.35.543146 AM +00:00
部分日期/時間格式不同 
CURRENT_TIMESTAMP
MySQL 傳回的日期/時間格式與 Oracle 不同。必須使用日期/時間格式 (或使用其他日期函式)，才能符合原始日期/時間格式：
SELECT CURRENT_TIMESTAMP
FROM DUAL
= 2019-01-31 06:55:07

ADD_MONTHS
傳回日期加上整數月：
ADD_MONTHS(SYSDATE, 1)
= 31-JAN-19
部分使用不同的函式名稱 
ADDDATE
為了達到相同的功能，MySQL 使用 ADDDATE 函式：
ADDDATE(SYSDATE(), 1)
= 2019-08-01 06:42:49.0

根據預設，MySQL 會傳回與 Oracle 不同的日期/時間和範圍/格式。必須使用日期/時間格式設定 (或其他日期函式)，才能符合原始日期/時間格式。
EXTRACT(日期部分) 從日期/時間或間隔運算式傳回指定日期/時間欄位的值：
EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019
是 EXTRACT (日期部分) 等同於 Oracle：
EXTRACT(YEAR FROM DATE '2019-01-31')
= 2019

LAST_DAY
傳回當月最後一天的日期：
LAST_DAY('01-JAN-2019')
= 31-JAN-19
部分日期/時間格式不同 
LAST_DAY
MySQL 傳回的日期/時間格式與 Oracle 不同。必須使用日期/時間格式 (或其他日期函式)，才能與原始日期/時間格式相符：
LAST_DAY('2019-01-01')
= 2019-01-31

MONTH_BETWEEN
傳回 date1 和 date2 之間的月份數量：
MONTHS_BETWEEN(
SYSDATE, SYSDATE-60)
= 1.96
部分使用不同的函式名稱 
PERIOD_DIFF(date1,date2)
MySQL PERIOD_DIFF 函式會傳回兩個期間 (格式為 YYMM 或 YYYYMM) 之間的月份差異，做為整數：
PERIOD_DIFF(
'201903', '201901')
= 2

如要取得與 Oracle MONTH_BETWEEN 函式相同的值，需要更精確的轉換
TO_CHAR (日期/時間) 將日期/時間或時間戳記資料類型轉換為 VARCHAR2 資料類型的值，格式為日期格式：
TO_CHAR(
SYSDATE,'DD-MM-YYYY HH24:MI:SS')
= 01-01-2019 10:01:01
部分使用不同的函式名稱 
DATE_FORMAT
MySQL DATE_FORMAT 函式會根據日期格式定義指定的格式設定日期：
DATE_FORMAT(
SYSDATE(),'%d-%m-%Y %H:%i:%s')
= 01-01-2019 10:01:01
編碼和解碼函式
Oracle 函式 Oracle 函式規格或實作 MySQL 等效項目 MySQL 對應函式 MySQL 函式規格或實作 
DECODE
使用 IF-THEN-ELSE 陳述式的功能，逐一將運算式與每個搜尋值進行比較 否 
CASE
使用 MySQL CASE 陳述式來達成類似的功能。 
DUMP
傳回 VARCHAR2 值，其中包含資料類型代碼、長度 (以位元組為單位) 以及指定運算式的內部表示法。 否 不適用 不支援。 
ORA_HASH
計算指定運算式的雜湊值。 否 
MD5/SHA
使用 MySQL MD5 產生 128 位元總和檢查碼，或使用 SHA 函式產生 160 位元總和檢查碼，以產生雜湊值
轉換函式
Oracle 函式 Oracle 函式規格或實作 MySQL 等效項目 MySQL 對應函式 MySQL 函式規格或實作 
CAST
將一個內建資料類型或集合型值轉換為另一個內建資料類型或集合型值：
CAST('1' as int) + 1
= 2
部分支援 
CAST
MySQL CAST 函式與 Oracle 功能類似，但在某些情況下，必須視是否需要明確或隱含轉換而調整：
CAST('1' AS SIGNED) + 1
= 2

CONVERT
將字元字串從一種字元集轉換為另一種字元集：
CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')
= ?? ?? ?? A B C
部分支援 
CONVERT
MySQL CONVERT 函式需要對語法和參數進行一些調整，才能傳回與 Oracle 相同的確切結果：
CONVERT('Ä Ê Í A B C ' USING utf8)
= Ä Ê Í A B C
TO_CHAR
(字串/數字)		 這個函式會將數字或日期轉換為字串：
TO_CHAR(22.73,'$99.9')
= $22.7
否 
FORMAT
MySQL FORMAT 函式會以「#,###.##」的格式從數字執行運算，將其四捨五入至特定小數位數，然後以字串的形式傳回結果，其功能與 Oracle 不同：
CONCAT('$',
FORMAT(22.73, 1))
= $22.7

TO_DATE
Oracle TO_DATE 函式會根據來源的日期/時間格式，將字串轉換為日期：
TO_DATE(
'2019/01/01', 'yyyy-mm-dd')
= 01-JAN-2019
部分使用不同的函式名稱和日期/時間格式 
STR_TO_DATE
MySQL STR_TO_DATE 函式會採用字串，並傳回以日期/時間格式指定的日期：
STR_TO_DATE(
'2019/01/01', '%Y/%m/%d')
= 2019-01-01

TO_NUMBER
將運算式轉換為 NUMBER 資料類型的值：
TO_NUMBER('01234')
= 1234
否 
CAST
您也可以使用 MySQL CAST 函式，以便傳回與 Oracle TO_NUMBER 相同的結果：
CAST('01234' as SIGNED)
= 1234
條件式 SELECT 函式
Oracle 函式 Oracle 函式規格或實作 MySQL 等效項目 MySQL 對應函式 MySQL 函式規格或實作 
CASE
CASE 陳述式會從一系列條件中選擇，並使用以下語法執行對應的陳述式：
CASE WHEN condition THEN result
[WHEN ...] [ELSE result]
END
是 
CASE
除了 CASE 函式之外，MySQL 也支援在 SELECT 陳述式中使用 IF/ELSE 條件處理：
CASE WHEN condition THEN result
[WHEN ...] [ELSE result]
END
空函式
Oracle 函式 Oracle 函式規格或實作 MySQL 等效項目 MySQL 對應函式 MySQL 函式規格或實作 
COALESCE
傳回運算式清單中的第一個非空值運算式：
COALESCE(
null, '1', 'a')
= a
是 
COALESCE
等同於 Oracle：
COALESCE(
null, '1', 'a')
= 1

NULLIF
比較 expr1 和 expr2m。如果兩者相等，函式會傳回空值。如果不相等，函式會傳回 expr1：
NULLIF('1', '2')
= a
是 
NULLIF
等同於 Oracle：
NULLIF('1', '2')
= a

NVL
在查詢結果中，將空值 (傳回為空白) 替換為字串： 
NVL(null, 'a')
= a
否 
IFNULL
MySQL 的等效函式是 IFNULL 函式，可將空值替換為指定字串：
IFNULL(null, 'a')
= a

NVL2
根據指定的
運算式是否為空值，判斷查詢傳回的值。		 否 
CASE
CASE 陳述式
會從一系列條件中選擇，並執行相應的陳述式：
CASE WHEN condition THEN result
[WHEN ...] [ELSE result]
END
環境和 ID 函式
Oracle 函式 Oracle 函式規格或實作 MySQL 等效項目 MySQL 對應函式 MySQL 函式規格或實作 
SYS_GUID
產生並傳回由 16 個位元組組成的全域唯一識別碼 (原始值)：
SELECT SYS_GUID()
FROM DUAL
= 8EFA4A31468B4C6DE05011AC0200009E
否 REPLACE 和 UUID 如要解決這個問題，請使用 MySQL REPLACE 和 UUID 函式模擬 Oracle SYS_GUID 函式：
REPLACE(
UUID(), '-', '')

UID
傳回可唯一識別工作階段使用者 (登入的使用者
) 的整數：
SELECT UID FROM DUAL
= 43
否 不適用 不適用 
USER
傳回目前工作階段使用者名稱的名稱：
SELECT USER FROM DUAL
= UserName
部分支援 
USER + INSTR + SUBSTR
MySQL USER 函式會傳回使用者名稱和連線伺服器 (root@IP)。如要只擷取使用者名稱，請使用其他支援函式：
SELECT
SUBSTR(USER(), 1, INSTR(USER(), '@') -1) FROM DUAL
= root

USERENV
傳回目前參數設定的目前使用者工作階段資訊：
SELECT USERENV('LANGUAGE')
FROM DUAL
= ENGLISH_AMERICA.AL32UTF8
否 
SHOW SESSION
VARIABLES
使用 MySQL SHOW SESSION
VARIABLES 陳述式，查看目前工作階段的設定：
SHOW SESSION VARIABLES LIKE '%collation%';
= utf8_general_ci

ROWID
Oracle 伺服器會為每個資料表中的每個資料列指派專屬的 ROWID，用於識別資料表中的資料列。ROWID 是資料列的位址，其中包含資料物件編號、資料列的資料區塊、資料列位置和資料檔案。 否 不適用 盡可能使用其他 MySQL 函式模擬相同功能。 
ROWNUM
傳回數字，代表 Oracle 從資料表或已彙整的資料表中選取資料列的順序。 否 不適用 盡可能使用其他 MySQL 函式或工作階段變數模擬相同功能。
匯總 (群組) 函式
Oracle 函式 Oracle 函式規格或
實作		 MySQL
等同		 MySQL 對應函式 MySQL 函式規格或實作 
AVG
傳回資料欄或運算式的平均值。 是 
AVG
等同於 Oracle 
COUNT
傳回查詢傳回的資料列數。 是 
COUNT
等同於 Oracle 
COUNT
(DISTINCT)
傳回資料欄或運算式中的不重複值數量。 是 
COUNT
(DISTINCT)
等同於 Oracle 
MAX
傳回資料欄或運算式的最大值。 是 
MAX
等同於 Oracle 
MIN
傳回資料欄或運算式的最小值。 是 
MIN
等同於 Oracle 
SUM
傳回資料欄或運算式的值總和。 是 
SUM
等同於 Oracle 
LISTAGG
透過連結度量值欄的值，以 ORDER BY 子句中指定的單一資料列，顯示每個群組內的資料：
SELECT LISTAGG(
DEPARTMENT_NAME, ', ')
WITHIN GROUP
(ORDER BY DEPARTMENT_NAME) DEPT
FROM DEPARTMENTS;


-- Single line results


= Accounting, Administration, Benefits, Construction
否 
GROUP_CONCAT
使用 MySQL GROUP_CONCAT 函式傳回與 Oracle 類似的結果，但在某些情況下，語法會有所不同：
SELECT GROUP_CONCAT(
DEPARTMENT_NAME ORDER BY DEPARTMENT_NAME SEPARATOR ', ') DEPT
FROM DEPARTMENTS;


-- Single line results


= Accounting, Administration, Benefits, Construction
Oracle 12c Fetch
Oracle 函式 Oracle 函式規格或
實作		 MySQL
等同		 MySQL 對應函式 MySQL 函式規格或實作 
FETCH
從多列查詢的結果集合中擷取資料列：
SELECT * FROM
EMPLOYEES
FETCH FIRST 10 ROWS ONLY;
否 LIMIT 使用 MySQL LIMIT 子句，只擷取特定的記錄組：
SELECT * FROM
EMPLOYEES
LIMIT 10;

基本篩選、運算子和子查詢

在轉換期間，基本篩選、運算子函式和子查詢都相對簡單，幾乎不需要額外付出任何努力。

轉換附註

請檢查並處理日期格式，因為 Oracle 和 MySQL 格式會傳回不同的預設結果：

  • Oracle SYSDATE 函式預設會傳回 01-AUG-19。
  • MySQL SYSDATE() 函式預設會傳回 2019-08-01 12:04:05。
  • 您可以使用 MySQL [DATE_FORMAT](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format) 或 [STR_TO_DATE](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date) 函式設定日期和時間格式。
Oracle 函式或子查詢 MySQL 同等項目 MySQL 對應函式或子查詢 MySQL 函式規格或實作 
EXISTS/NOT EXISTS
是 
EXISTS/NOT EXISTS

SELECT * FROM DEPARTMENTS D
WHERE EXISTS (SELECT 1
              FROM EMPLOYEES E
              WHERE
              E.DEPARTMENT_ID =
              D.DEPARTMENT_ID);

IN/NOT IN
是 
IN/NOT IN

SELECT * FROM DEPARTMENTS D
WHERE DEPARTMENT_ID IN
            (SELECT DEPARTMENT_ID
             FROM EMPLOYEES E);


-- OR


SELECT * FROM EMPLOYEES
WHERE (EMPLOYEE_ID, DEPARTMENT_ID)
IN((100, 90));

LIKE/NOT LIKE
是 
LIKE/NOT LIKE

SELECT * FROM EMPLOYEES
WHERE FIRST_NAME LIKE '_e_n%';

BETWEEN/NOT BETWEEN
是 
BETWEEN/NOT BETWEEN

SELECT * FROM EMPLOYEES
WHERE EXTRACT(YEAR FROM HIRE_DATE)
NOT BETWEEN 2001 and 2004;

AND/OR
是 
AND/OR

SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(100, 101)
AND (SALARY >= 1000 OR HIRE_DATE <= '2006-02-05');

SubQuery
是 
SubQuery
MySQL 支援 SELECT 層級中的子查詢，可用於 JOIN 陳述式，以及 WHERE/AND 子句中的篩選：
-- SELECT SubQuery
SELECT D.DEPARTMENT_NAME,
       (SELECT AVG(SALARY) AS AVG_SAL
        FROM EMPLOYEES E
        WHERE E.DEPARTMENT_ID =
              D.DEPARTMENT_ID) AVG_SAL
FROM DEPARTMENTS D;


-- JOIN SubQuery
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES E JOIN
     (SELECT *
      FROM DEPARTMENTS
      WHERE  LOCATION_ID = 2700) D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;


-- Filtering Subquery
SELECT FIRST_NAME,
       LAST_NAME,
       SALARY
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                FROM EMPLOYEES);
運算子 是 運算子 MySQL 支援所有基本運算子：
> | >= | < | <= | = | <> | !=

分析函式 (或時間區間和排名函式)

Oracle 分析函式會提供可根據一組資料列計算匯總值的功能，進而擴充標準 SQL 分析函式的功能。這些函式可套用至單一查詢運算式範圍內，邏輯上劃分成多組的結果。這類指標通常會與商業智慧報表和數據分析搭配使用，可提升查詢效能，不必使用更複雜的非分析 SQL 程式碼就能取得相同結果。

轉換附註

  • MySQL 5.7 版不提供分析函式，無法支援簡單的 SQL 陳述式轉換作業。不過，這項功能在 MySQL 8 版中僅部分新增，因此您可能需要考慮轉換分析函式，並在遷移程序中手動處理。
  • 您可以選擇重新編寫程式碼，移除使用分析函式的部分，改用較傳統的 SQL 程式碼解決方案，或將這項邏輯移至應用程式層。

下表列出 Oracle 常見的分析函式。

函式系列 相關函式 由 MySQL 5.7 支援
分析和排名 
RANK
AVERAGE_RANK
DENSE_RANK
RANK ROW_NUMBER
PERCENT_RANK
CUME_DIST
NTILE
FIRST_VALUE
LAST_VALUE
OVER (PARTITION BY...)
否
階層 
CONNECT BY
HIER_ANCESTOR
HIER_CHILD_COUNT
HIER_DEPTH
HIER_LEVEL
HIER_ORDER
HIER_PARENT
HIER_TOP
否
延遲 
LAG
LAG_VARIANCE LAG_VARIANCE_PERCENT LEAD
LEAD_VARIANCE LEAD_VARIANCE_PERCENT
否

一般資料表運算式 (CTE)

CTE 可讓您實作序列程式碼的邏輯，以便重複使用可能過於複雜或效率不佳的 SQL 程式碼。您可以為 CTE 命名，然後使用 WITH 子句在 SQL 陳述式的不同部分中重複使用。

轉換附註

  • MySQL 5.7 版本不支援 CTE，但 MySQL 8 版本支援。
  • 如要採用其他解決方案，請使用衍生資料表或子查詢，或重寫 SQL 陳述式，以便移除 CTE 功能。

範例

Oracle 
WITH DEPT_COUNT
(DEPARTMENT_ID, DEPT_COUNT) AS
(SELECT DEPARTMENT_ID,
        COUNT(*)
 FROM EMPLOYEES
 GROUP BY DEPARTMENT_ID)


SELECT E.FIRST_NAME ||' '|| E.LAST_NAME AS EMP_NAME,
       D.DEPT_COUNT AS EMP_DEPT_COUNT
FROM EMPLOYEES E JOIN DEPT_COUNT D
USING (DEPARTMENT_ID)
ORDER BY 2 DESC;
MySQL 
SELECT * FROM (
SELECT CONCAT(E.FIRST_NAME, ' ', E.LAST_NAME) AS EMP_NAME,
       (SELECT COUNT(*)
        FROM EMPLOYEES D
        WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
        GROUP BY DEPARTMENT_ID) AS EMP_DEPT_COUNT
FROM EMPLOYEES E
ORDER BY 2 DESC) TBL
WHERE EMP_DEPT_COUNT IS NOT NULL;

MERGE 陳述式

MERGE (或 UPSERT) 陳述式提供一種方法，可指定單一 SQL 陳述式，在單一 MERGE 作業中依條件執行 DML 作業，而非單一 DML 作業，以便個別執行。這個函式會從來源資料表選取記錄，然後指定邏輯結構，自動對目標資料表執行多個 DML 作業。這項功能可避免您使用多個插入、更新或刪除作業。請注意，MERGE 是決定性的陳述式，也就是說，一旦資料列經由 MERGE 陳述式處理，就無法再使用相同的 MERGE 陳述式處理。

轉換附註

與 Oracle 不同，MySQL 5.7 版不支援 MERGE 功能。為了部分模擬 MERGE 功能，MySQL 提供 REPLACE 和 INSERT… ON DUPLICATE KEY UPDATE 陳述式：

  • REPLACE：運作方式與 INSERT 陳述式相同，但如果資料表中的舊資料列與 PRIMARY KEY 或 UNIQUE 索引的新資料列具有相同的值，系統會先刪除舊資料列，再插入新資料列。

  • INSERT… ON DUPLICATE KEY UPDATE：如果插入的資料列會導致 PRIMARY KEY 或 UNIQUE 索引中的值重複，系統會針對舊資料列執行 UPDATE，以消除重複鍵例外狀況，例如：

    INSERT INTO tbl (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE tbl SET c=c+1 WHERE a=1;

另一個解決方案是將 MERGE 功能轉換為儲存程序，以便使用 INSERT、UPDATE 和 DELETE 指令，搭配例外狀況和重複項目處理機制來管理 DML 作業。

SQL 陳述式提示

Oracle 提供大量 SQL 查詢提示，讓使用者影響最佳化工具的行為和決策，以便產生更有效率的查詢執行計畫。Oracle 支援超過 60 種不同的資料庫提示。MySQL 提供有限的查詢提示。

一般來說，MySQL 5.7 版本支援兩種查詢提示：OPTIMIZER HINTS 和 INDEX HINTS。MySQL 最佳化器提示可讓您在個別 SQL 陳述式中控制最佳化器行為，例如：

SELECT /*+ NO_RANGE_OPTIMIZATION(tbl PRIMARY, col1_idx) */ col1 FROM tbl;

MySQL 5.7 版可用的最佳化器提示

提示名稱 提示總覽 適用範圍 
BKA, NO_BKA
影響批次鍵存取鍵彙整處理 查詢區塊、資料表 
BNL, NO_BNL
影響封鎖巢狀迴圈 join 處理作業 查詢區塊、資料表 
MAX_EXECUTION_TIME
限制陳述式執行時間 全球 
MRR, NO_MRR
影響多範圍讀取最佳化 資料表、索引 
NO_ICP
影響索引條件推送最佳化 資料表、索引 
NO_RANGE_OPTIMIZATION
影響範圍最佳化 資料表、索引 
QB_NAME
將名稱指派給查詢區塊 查詢區塊 
SEMIJOIN, NO_SEMIJOIN
會影響半相連策略 查詢區塊 
SUBQUERY
影響物化、IN 到 EXISTS 子查詢策略。 查詢區塊

MySQL 索引提示會向最佳化工具提供資訊，說明如何在查詢處理期間選擇索引。USE、FORCE 或 IGNORE 關鍵字可用於控制最佳化工具索引使用程序，例如：

SELECT * FROM tbl USE INDEX (col1_index, col2_index);
-- OR
SELECT * FROM tbl IGNORE INDEX (col1_index, col2_index);

轉換附註

由於 Oracle 和 MySQL 最佳化工具之間存在根本差異，且 Oracle 和 MySQL 查詢提示之間的重疊程度有限，因此建議您將任何 Oracle SQL 陳述式轉換為在目標 MySQL 資料庫中保留未指定查詢提示的陳述式。

透過 MySQL 工具 (例如用於即時效能資訊主頁的 MySQL Workbench) 和功能 (例如使用執行計畫檢查查詢，並根據用途調整執行個體或工作階段參數) 進行 MySQL 效能調整。

執行計畫

執行計畫的主要目的，是讓您深入瞭解查詢最佳化工具用於存取資料庫資料的選擇。查詢最佳化工具會為資料庫使用者產生 SELECT、INSERT、UPDATE 和 DELETE 陳述式的執行計畫，並讓管理員更清楚查看特定查詢和 DML 作業。當您需要調整查詢效能時，這些指標就特別實用，例如判斷索引效能或判斷是否有缺少的索引需要建立。

執行計畫可能會受到資料量、資料統計資料和執行個體參數 (全域或工作階段參數) 的影響。

轉換考量事項

執行計畫並非需要遷移的資料庫物件，而是用於分析 Oracle 和 MySQL 在相同資料集上執行相同陳述式時的效能差異。

MySQL 不支援與 Oracle 相同的執行計畫語法、功能或輸出內容。

範例

Oracle 執行計畫 
SQL> EXPLAIN PLAN FOR
     SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;


SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));


Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
MySQL 執行計畫 
mysql> EXPLAIN SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105;


+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | EMPLOYEES | NULL       | const | PRIMARY       | PRIMARY | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

預存程序、函式和觸發條件

PL/SQL 是 Oracle 擴充的程序語言，用於在資料庫中建立、儲存及套用以程式碼為基礎的解決方案。一般來說，資料庫儲存程序和函式是組成 ANSI SQL 和 SQL 擴充程序語言的程式碼元素，例如 Oracle 的 PL/SQL、PostgreSQL 的 PL/pgSQL，以及 MySQL 的 MySQL 程序語言。MySQL 會使用與資料庫相同的名稱，用於其擴充的程序化語言。

這些儲存程序和函式的目的，是針對更適合在資料庫中執行而非在應用程式中執行的必要條件提供解決方案 (例如效能、相容性和安全性)。雖然預存程序和函式都使用 PL/SQL，但預存程序主要用於執行 DDL/DML 作業，而函式主要用於執行運算以傳回特定結果。

從 PL/SQL 轉換為 MySQL 程序語言

從 Oracle PL/SQL 到 MySQL 程式碼遷移的角度來看，MySQL 程序實作方式與 Oracle 不同。因此，您必須遷移程式碼，將 Oracle 的 PL/SQL 功能轉換為 MySQL 的預存程序和函式。此外，MySQL 不支援 Oracle 套件和套件主體，因此在進行程式碼轉換時，請將這些元素轉換 (或剖析) 為單一 MySQL 程式碼單元。請注意，MySQL 預存程序和函式也稱為常式。

程式碼物件擁有者

在 Oracle 中，已儲存程序或函式的擁有者是特定使用者。在 MySQL 中，擁有者是特定結構定義 (由資料庫使用者在資料庫中建立)。

程式碼物件權限和安全性

在 Oracle 中，使用者必須具備 CREATE PROCEDURE 系統權限，才能建立已儲存的程序或函式 (如要以其他使用者身分建立程序或函式，資料庫使用者必須具備 CREATE ANY PROCEDURE 權限)。如要執行已儲存的程序或函式，資料庫使用者必須具備 EXECUTE 權限。

在 MySQL 中，使用者必須具備 CREATE ROUTINE 權限和 EXECUTE 權限，才能建立程式碼元素。MySQL DEFINER 子句會定義程式碼物件的使用者建立者，且使用者必須具備 CREATE ROUTINE 等適當權限。

MySQL 預存程序和函式語法

以下範例顯示 MySQL 儲存程序和函式的語法：

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement