CHAR 還是 VARCHAR? 這是個問題！

語言: CN / TW / HK

時間 2021-10-01 00:30:46 51CTO

本文轉載自微信公眾號「明哥的IT隨筆」，作者 IT明哥。轉載本文請聯絡明哥的IT隨筆公眾號。

大家好，我是明哥!

今天我們來對比下 ORACLE 與 MYSQL 中不同字元資料型別的異同，以避免因前期資料結構設計時資料型別選用不當，造成後續儲存加工和處理資料時的各種坑。

ORACLE 中內建的字元資料型別主要有以下三大類：

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

MYSQL 中字元資料型別主要有：

可以看到：

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

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

-如果使用者傳入的實際的字串的長度，小於或等於 DDL 語句中宣告的該 VARCHAR 欄位的最大長度，則直接儲存使用者傳入的值;(實際儲存的資料，都是欄位值的實際長度和欄位的具體值);

如果使用者傳入的實際的字串的長度，大於 DDL 語句中宣告的該 VARCHAR 欄位的最大長度，ORACLE 和 MYSQL 都會報錯(注意：MYSQL中，是報錯還是截斷，跟超長欄位是正常欄位還是 trailing spaces，以及是否是 STRICT SQL MODE 有關);

ORACLE 和 MYSQL 中都是 VARCHAR(ORACLE 中是 VARCHAR2)的儲存空間使用率更好，因為 CHAR 型別欄位在實際長度不夠時會在末尾補充空格(blank-pads)並存儲這些 trailing blanks 而 VARCHAR 不會，所以 VARCHAR 可以更有效地儲存資料，空間使用率更高。

ORACLE 會將儲存的值原封不動地取出，不做任何加工，所以通過LENGTH()函式返回的，就是 DDL 語句中宣告的長度(當然底層儲存時 pad 了 space);
MYSQL 會將儲存的值最右邊的空格全部擷取後再取出,所以通過LENGTH()函式返回的，不是 DDL 語句中宣告的長度，而是欄位的實際有效長度(當然底層儲存時 pad 了 space);

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

ORACLE 和 MYSQL 都有對應變長字串的資料型別，如 VARCHAR/VARCHAR2, 兩者在資料的儲存和取出上的處理類似;
ORACLE 和 MYSQL 都有對應定長字串的資料型別，如 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

「其他文章」