[[427024]]

本文轉載自微信公眾號「明哥的IT隨筆」，作者 IT明哥 。轉載本文請聯(lián)系明哥的IT隨筆公眾號。

1 前言

大家好，我是明哥!

今天我們來對比下 ORACLE 與 MYSQL 中不同字符數(shù)據(jù)類型的異同，以避免因前期數(shù)據(jù)結構設計時數(shù)據(jù)類型選用不當，造成后續(xù)存儲加工和處理數(shù)據(jù)時的各種坑。

2 ORACLE 與 MYSQL 中字符數(shù)據(jù)類型都有哪些

ORACLE 中內(nèi)置的字符數(shù)據(jù)類型主要有以下三大類：

• CHAR/NCHAR：存儲固定長度的字符串，其中NCHAR使用 national character set;
• VARCHAR2/VARCHAR/NVARCHAR2：存儲變長字符串，其中 VARCHAR 等同于 VARCHAR2，NVARCHAR2 使用 national character set;
• CLOB/NCLOB：存儲大小可達 4GB 的字符串，其中 NCLOB 使用 national character set;

MYSQL 中字符數(shù)據(jù)類型主要有：

• CHAR:存儲固定長度的字符串;
• VARCHAR：存儲變長字符串
• BLOB/TEXT：主要用來存儲大的字符串。

可以看到：

• 二者都有專門用來處理較大字符串的數(shù)據(jù)類型，如 CLOB/BLOB/TEXT;
• 二者都有處理定長字符串的數(shù)據(jù)類型，如 CHAR;
• 二者都有處理變長字符串的數(shù)據(jù)類型，如 VARCHAR;

以下我們重點對比下二者對定長和變長字段類型，即 CHAR 和 VARCHAR ，處理時的異同。

3 ORACLE 和 MYSQL 對 CHAR 和 VARCHAR 處理的相同點在哪里?

3.1 ORACLE 和 MYSQL 對定長字段類型 CHAR 的存儲處理類似，都會對用戶傳入的實際的字符串進行加工處理后再存儲：

• 如果用戶傳入的實際的字符串的長度，跟 DDL 語句中聲明的該 CHAR 字段的長度相等，則直接存儲用戶傳入的值;
• 如果用戶傳入的實際的字符串的長度，小于 DDL 語句中聲明的該 CHAR 字段的長度，ORACLE 和 MYSQL 都會在字段末尾補充空格(blank-pad),直到達到聲明的長度;
• 如果用戶傳入的實際的字符串的長度，大于 DDL 語句中聲明的該 CHAR 字段的長度，ORACLE 和 MYSQL 都會報錯退出(注意：MYSQL中，可以配置使用非 STRICT SQL MODE，此時會截斷超常字段然后存儲截斷后的值并告警，但不會報錯退出);

3.2 ORACLE 和 MYSQL 對變長字段類型 VARCHAR 的存儲處理類似，都不會對用戶傳入的實際的字符串進行加工處理，而是直接校驗存儲：

-如果用戶傳入的實際的字符串的長度，小于或等于 DDL 語句中聲明的該 VARCHAR 字段的最大長度，則直接存儲用戶傳入的值;(實際存儲的數(shù)據(jù)，都是字段值的實際長度和字段的具體值);

• 如果用戶傳入的實際的字符串的長度，大于 DDL 語句中聲明的該 VARCHAR 字段的最大長度，ORACLE 和 MYSQL 都會報錯(注意：MYSQL中，是報錯還是截斷，跟超長字段是正常字段還是 trailing spaces，以及是否是 STRICT SQL MODE 有關);

3.3 ORACLE 和 MYSQL 對變長字段類型 VARCHAR 的取出處理類似

• ORACLE 和 MYSQL 對變長字段類型 VARCHAR 的取出處理類似，都不會對實際存儲的字段值做加工處理，而是直接返回，因為存儲時已經(jīng)做了校驗和加工。

3.4 ORACLE 和 MYSQL 中都是 VARCHAR(ORACLE 中是 VARCHAR2)的存儲空間使用率更好

• ORACLE 和 MYSQL 中都是 VARCHAR(ORACLE 中是 VARCHAR2)的存儲空間使用率更好，因為 CHAR 類型字段在實際長度不夠時會在末尾補充空格(blank-pads)并存儲這些 trailing blanks 而 VARCHAR 不會，所以 VARCHAR 可以更有效地存儲數(shù)據(jù)，空間使用率更高。

4 ORACLE 和 MYSQL 對 CHAR 和 VARCHAR 處理的不同點在哪里?

4.1 ORACLE 和 MYSQL 對 CHAR 和 VARCHAR 處理的不同點，體現(xiàn)在如何取出 CHAR 類型的字段數(shù)據(jù)上：

• ORACLE 會將存儲的值原封不動地取出，不做任何加工，所以通過LENGTH()函數(shù)返回的，就是 DDL 語句中聲明的長度(當然底層存儲時 pad 了 space);
• MYSQL 會將存儲的值最右邊的空格全部截取后再取出,所以通過LENGTH()函數(shù)返回的，不是 DDL 語句中聲明的長度，而是字段的實際有效長度(當然底層存儲時 pad 了 space);

4.2 ORACLE 和 MYSQL 對 CHAR 和 VARCHAR 處理的不同點，體現(xiàn)在如何對比 CHAR 和 VARCHAR 類型的字段數(shù)據(jù)上：

• Oracle 對 CHAR/NCHAR 采用的是 “blank-padded comparison semantics” 模式：如果對比的字段長度不同， Oracle 會在較短字段的末尾補充空格使得兩者長度相同，然后再逐個字符進行比較。所以只有末尾空格數(shù)有差異的字段，對被認為是相等的字段;
• Oracle 對 VARCHAR2/NVARCHAR2 采取的是 “non-padded comparison semantics” 模式：只有兩個字段的長度相同且字符完全相同，才會被認為是相等的字段;
• MySQL 對 CHAR, VARCHAR, 和 TEXT 采取的都是類似 ORACLE 的 “blank-padded comparison semantics” 的模式：即如果對比的字段長度不同， Oracle 會在較短字段的末尾補充空格使得兩者長度相同，然后再逐個字符進行比較。所以只有末尾空格數(shù)有差異的字段，對被認為是相等的字段;

5 知識總結

• ORACLE 和 MYSQL 都有對應變長字符串的數(shù)據(jù)類型，如 VARCHAR/VARCHAR2, 兩者在數(shù)據(jù)的存儲和取出上的處理類似;
• ORACLE 和 MYSQL 都有對應定長字符串的數(shù)據(jù)類型，如 CHAR，兩者對 CHAR 類型字段的存儲處理類似，都會校驗字段長度，并在字段不夠時通過空格右補齊;
• ORACLE 在取出 CHAR 類型字段時，不會做額外處理，會將存儲的字段值直接取出;MYSQL 在取出 CHAR 類型字段時，會對存儲的字段值做額外處理，會將字段最右端的空格截掉再返回;
• Oracle 在對比 CHAR/NCHAR 類型字段時，采用的是 “blank-padded comparison semantics” 模式;
• Oracle 在對比 VARCHAR2/NVARCHAR2 類型字段時，采取的是 “non-padded comparison semantics” 模式;
• MySQL 在對比 CHAR, VARCHAR, 和 TEXT 類型字段時，采取的都是類似 ORACLE 的 “blank-padded comparison semantics” 的模式;

CHAR and varchar in mysql

CHAR in oracle and mysql