將 Oracle 使用者遷移至 MySQL 適用的 Cloud SQL：資料類型、使用者和資料表

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

資料類型

MySQL 提供多種資料類型，這些類型與 Oracle 提供的資料類型完全相同或相似。下表列出最常見的 MySQL 資料類型，接著比較 Oracle 原始資料類型與對應的 MySQL 資料類型；如果系統不支援某個資料類型，則會列出替代資料類型。請注意，MySQL 5.7 資料庫的整個資料列限制為 65,535 個位元組 (取決於使用的字元集)。

MySQL 5.7 原始資料類型

MySQL 資料類型系列 MySQL 資料類型名稱 資料類型規格
字串/
字元		 
CHAR(n)
儲存剛好 n 個字元。 
VARCHAR(n)
可儲存不等量的字元，最多可儲存 n 個半形字元。 
BINARY
儲存正確的 n 位元組。 
VARBINARY(n)
可儲存變數數量的字元，最多可達 n 位元組。 
BLOB
可容納不同資料量的二進位大型物件。 
TEXT
VARCHAR 的特定變化版本，不必指定字元數上限。 
ENUM
這個字串物件具備從允許值清單中選擇的值，這些值是在建立資料表時，在資料欄規格中明確列舉。 
SET
字串物件可有零或多個值，每個值都必須從建立資料表時指定的允許值清單中選擇。
數字 
INT
最小值為 -2147483648 | 最大值為 2147483647。 
INTEGER
最小值為 -2147483648 | 最大值為 2147483647。 
TINYINT
最小值為 -128，最大值為 127。 
SMALLINT
最小值為 -32768 | 最大值為 32767。 
MEDIUMINT
最小值為 -8388608 | 最大值為 8388607。 
BIGINT
最小值為 -2^63 | 最大值為 2^63-1。 
DECIMAL(p,s)
可儲存任何具有 p 位數和 s 小數的值。 
NUMERIC(p,s)
可儲存任何具有 p 位數和 s 小數的值。 
FLOAT(m,d)
值最多可儲存 m 位數，其中 d 位數可位於小數點後方。 
DOUBLE(m,d)
值最多可儲存 m 位數，其中 d 位數可位於小數點後方。 
BIT(m)
儲存 m 位元值。m 的範圍為 1 到 64。
日期和時間 
DATE
- 值包含日期部分，但不包含時間部分。
- 以 'YYYY-MM-DD' 格式擷取並顯示 DATE 值。
- 支援的範圍為 '1000-01-01' 到 '9999-12-31'。		 
DATETIME
- 同時包含日期和時間的值。
- 以 'YYYY-MM-DD HH:MM:SS' 格式擷取並顯示 DATETIME 值。
- 支援的範圍為 '1000-01-01 00:00:00' 到 '9999-
12-31 23:59:59'。		 
TIMESTAMP
- 同時包含日期和時間的值。
- TIMESTAMP 的範圍為 '1970-01-01 00:00:01' (世界標準時間) 到 '2038-01-19 03:14:07' (世界標準時間)。		 
TIME
- 值的範圍從 '-838:59:59' 到 '838:59:59'。
- 小時部分可能會很大，因為 TIME 類型不僅可用於表示時段 (必須小於 24 小時)，還可用於表示兩個事件之間的經過時間或時間間隔 (可能會大於 24 小時，甚至為負值)。		 
YEAR
- YEAR 1 個位元組的類型，用於表示年份值。
- 可宣告為 YEAR 或 YEAR(n)，且顯示寬度為 n 個半形字元。
JSON 
JSON
文字 JSON 資料做為資料類型。

空間 (幾何)		 
GEOMETRY
當您要使用下表中列出的資料模型時，請指定要使用的資料欄類型。 
POINT
(x,y) 值。 
LINESTRING
(pt1, pt2) 行。 
POLYGON
一系列點，實際上是封閉路徑。 
MULTIPOINT
POINT 值的集合。 
MULTI-LINESTRING
LINE 值的集合。 
MULTIPOLYGON
POLYGON 值的集合。 
GEOMETRY-COLLECTION
幾何圖形資料類型的集合。
Logical 
BOOLEAN
- 可儲存 true 或 false 值。
- 可接受 TRUE、'1' 和 1 等值，視為 true。
- 使用 1 個位元組的儲存空間，可儲存 NULL。

Oracle 到 MySQL 資料類型轉換

Oracle 資料類型群組 Oracle 資料類型名稱 Oracle 資料類型規格 MySQL 與 Oracle 的對應項目 MySQL 對應/替代
字串/
字元		 
CHAR(n)
大小上限為 2000 位元組。 是 
CHAR(n)

CHARACTER(n)
大小上限為 2000 位元組。 是 
CHARACTER(n)

NCHAR(n)
大小上限為 2000 位元組。 是 
NCHAR(n)

VARCHAR(n)
大小上限為 2000 位元組。 是 
VARCHAR(n)

NCHAR
VARYING(n)
長度可變的 UTF-8 字串，大小上限為 4000 位元組。
 是 
NCHAR
VARYING(n)

VARCHAR2(n)
11g
大小上限為 4000 位元組，在 PL/SQL 中為 32 KB。 否 
VARCHAR(n)

VARCHAR2(n)
12g
大小上限為 32767 位元組 MAX_STRING_SIZE=EXTENDED。 否 
VARCHAR(n)

NVARCHAR2(n)
大小上限為 4000 位元組。 否 
VARCHAR(n)

LONG
大小上限為 2 GB。 是 
LONG

RAW(n)
大小上限為 2000 位元組。 否 
VARBINARY(n)

LONG RAW
大小上限為 2 GB。 否 
LONGTEXT
數字 
NUMBER
浮點數。 否 
NUMERIC/DECIMAL(p,s)
數字 
NUMBER
浮點數。 否 
NUMERIC/DECIMAL(p,s)

NUMBER(*)
浮點數。 否 
DOUBLE

NUMERIC(p,s)
精確度範圍為 1 到 38。 是 
NUMERIC(p,s)

FLOAT(p)
浮點數。 是 
FLOAT(p)

DEC(p,s)
固定點數。 是 
DEC(p,s)

DECIMAL(p,s)
固定點數。 是 
DECIMAL(p,s)

INT
38 位數整數。 是 
INT

INTEGER
38 位數整數。 是 
INTEGER

SMALLINT
38 位數整數。 是 
SMALLINT

REAL
浮點數。 是 
REAL

DOUBLE PRECISION
浮點數。 是 
DOUBLE PRECISION
日期與時間 
DATE
儲存日期和時間資料 (年、月、日、時、分和秒)。 是 
DATE

TIMESTAMP(p)
日期和時間 (含小數)。 是 
TIMESTAMP

TIMESTAMP(p)
WITH TIME ZONE
日期和時間，含小數和時區。 否 
DATETIME(n)

INTERVAL
YEAR(p)
TO MONTH
日期間隔。 否 
VARCHAR(n)

INTERVAL
DAY(p)
TO SECOND(s)
日期和時間間隔。 否 
VARCHAR(n)
Logical 
BOOLEAN
值為 TRUE、FALSE 和 NULL。無法指派至資料庫資料表欄。 是 
BOOLEAN
XML 
XMLTYPE
XML 資料。 否 
LONGTEXT
LOB 
BFILE
指向二進位檔的指標，大小上限為 4 GB。 否 
VARCHAR(255)

CLOB
字元大型物件，檔案大小上限為 4 GB。 否 
LONGTEXT

BLOB
二進位大型物件，大小上限為 4 GB。 是 
BLOB

NCLOB
變長式 Unicode 字串，檔案大小上限為 4 GB。 否 
LONGTEXT
ROWID 
ROWID
實際資料列位址。 否 
CHAR(n)

UROWID(n)
邏輯資料列位址的通用資料列 ID。 否 
VARCHAR(n)
Spatial 
SDO_GEOMETRY
空間物件的幾何描述。 否 
N/A

SDO_TOPO_GEOMETRY
說明拓樸結構幾何圖形。 否 
N/A

SDO_GEORASTER
您可以將光柵格線或圖片物件儲存在單一資料列中。 否 
N/A
媒體類型 
ORDDicom
支援音訊資料的儲存和管理。 否 
N/A

ORDDicom
支援儲存及管理數位醫學影像和通訊 (DICOM)。 否 
N/A

ORDDoc
支援任何類型的媒體資料儲存和管理。 否 
N/A

ORDImage
支援圖片資料的儲存和管理。 否 
N/A

ORDVideo
支援儲存及管理影片資料。 否 
N/A

使用者定義的類型

Oracle 將使用者定義類型 (UDT) 稱為 OBJECT TYPES，並使用 PL/SQL 進行管理。使用者定義的類型可讓使用者建立應用程式專用的複雜資料類型，這些類型是根據內建 Oracle 資料類型清單建立，並從中延伸。

以下是 Oracle 的 UDT 範例：

SQL> CREATE OR REPLACE TYPE EMP_PHONE_NUM AS OBJECT (
     PHONE_NUM VARCHAR2(11));

轉換附註

MySQL 不允許使用者建立專屬的定義類型。

使用者和資料表

本節將說明如何建立使用者並指派權限，以及為何需要將 Oracle 資料表轉換為 MySQL 適用的 Cloud SQL 資料表。

建立使用者和權限

Oracle 資料庫使用者帳戶 (Oracle 的「使用者」和「結構描述」相同) 可用於驗證及連線至資料庫工作階段，而授權存取權則會針對特定資料庫物件和權限，在個別使用者層級設定。

一般來說，資料庫使用者分為兩種：

  • 管理員：管理資料庫執行個體、使用者和資源。
  • 使用者帳戶：提供邏輯作業，例如應用程式。

管理員會將權限授予使用者和應用程式使用者帳戶，以便存取資料庫物件。您可以為使用者授予 Oracle 資料庫權限，以便執行特定作業 (例如建立工作階段/連線) 或特定資料庫物件 (例如特定資料表上的 SELECT，或特定儲存程序上的 EXECUTE)。

轉換考量事項

  • MySQL CREATE USER 語法與 Oracle 語法不同，因此無法直接遷移。此外，兩個資料庫的使用者架構也不同。
  • 管理員在 MySQL 中建立使用者時，必須指定使用者需要連線的伺服器，才能存取資料庫。伺服器可以是特定 IP 位址或 DNS，也可以使用萬用字元 % 符號，允許從所有來源連線。
  • 由於 MySQL 使用者名稱是使用者名稱和允許使用者連線的伺服器之間的兩部分附件，因此您可以使用相同名稱建立使用者，但使用者名稱的用戶端位址 (IP/DNS) 來自不同的伺服器。
  • 建立使用者後，您可以授予使用者存取特定資料庫或結構描述的資料庫物件權限。
  • 您也可以根據 Cloud SQL for MySQL 允許的權限，授予使用者其他權限 (例如，超級使用者會獲得 SUPER 和 FILE 以外的所有權限)。Oracle 使用者可以在 MySQL 中連結至專屬的資料表空間，因此這項功能不相關。

資料表

Oracle 資料表是由許多元素建構而成，例如資料欄資料類型、資料表限制、索引、區隔、Oracle 資料表專屬功能等等。如要成功遷移至 MySQL 適用的 Cloud SQL 資料庫資料表，所有 Oracle 資料表元素都必須轉換為 MySQL 資料表。部分元素可透過不修改或略微修改的方式轉換，但有些元素則必須完全修改。

從遷移的角度來看，將 PL/SQL 轉換為 MySQL 可能需要付出更多心力，因為將 Oracle 資料表轉換為 MySQL 資料表是相當重要的階段，對效能和資料大小有進一步的影響。

以下是 Oracle 和 MySQL 資料表與相關功能的主要差異。我們會在本系列的後續部分討論這些差異。

  • 區分大小寫 (資料表和欄名稱)
  • 建立資料表語法
  • 資料表和索引中繼資料
  • 限制支援
  • 支援的資料類型和限制
  • 索引
  • 分區與分區管理
  • 資料表和索引維護
  • 臨時資料表
  • 瀏覽次數
  • 可見和不可見資料欄 (Oracle 12c)
  • 資料表和欄位字元集