CHAR 還是 VARCHAR? 這是個(gè)問(wèn)題！

作者：IT明哥 2021-10-01 00:02:54

今天我們來(lái)對(duì)比下 ORACLE 與 MYSQL 中不同字符數(shù)據(jù)類(lèi)型的異同，以避免因前期數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)時(shí)數(shù)據(jù)類(lèi)型選用不當(dāng)，造成后續(xù)存儲(chǔ)加工和處理數(shù)據(jù)時(shí)的各種坑。

[[427024]]

本文轉(zhuǎn)載自微信公眾號(hào)「明哥的IT隨筆」，作者 IT明哥。轉(zhuǎn)載本文請(qǐng)聯(lián)系明哥的IT隨筆公眾號(hào)。

1 前言

大家好，我是明哥!

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

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

CHAR/NCHAR：存儲(chǔ)固定長(zhǎng)度的字符串，其中NCHAR使用 national character set;
VARCHAR2/VARCHAR/NVARCHAR2：存儲(chǔ)變長(zhǎng)字符串，其中 VARCHAR 等同于 VARCHAR2，NVARCHAR2 使用 national character set;
CLOB/NCLOB：存儲(chǔ)大小可達(dá) 4GB 的字符串，其中 NCLOB 使用 national character set;

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

CHAR:存儲(chǔ)固定長(zhǎng)度的字符串;
VARCHAR：存儲(chǔ)變長(zhǎng)字符串
BLOB/TEXT：主要用來(lái)存儲(chǔ)大的字符串。

可以看到：

二者都有專(zhuān)門(mén)用來(lái)處理較大字符串的數(shù)據(jù)類(lèi)型，如 CLOB/BLOB/TEXT;
二者都有處理定長(zhǎng)字符串的數(shù)據(jù)類(lèi)型，如 CHAR;
二者都有處理變長(zhǎng)字符串的數(shù)據(jù)類(lèi)型，如 VARCHAR;

以下我們重點(diǎn)對(duì)比下二者對(duì)定長(zhǎng)和變長(zhǎng)字段類(lèi)型，即 CHAR 和 VARCHAR ，處理時(shí)的異同。

3 ORACLE 和 MYSQL 對(duì) CHAR 和 VARCHAR 處理的相同點(diǎn)在哪里?

3.1 ORACLE 和 MYSQL 對(duì)定長(zhǎng)字段類(lèi)型 CHAR 的存儲(chǔ)處理類(lèi)似，都會(huì)對(duì)用戶(hù)傳入的實(shí)際的字符串進(jìn)行加工處理后再存儲(chǔ)：

如果用戶(hù)傳入的實(shí)際的字符串的長(zhǎng)度，跟 DDL 語(yǔ)句中聲明的該 CHAR 字段的長(zhǎng)度相等，則直接存儲(chǔ)用戶(hù)傳入的值;
如果用戶(hù)傳入的實(shí)際的字符串的長(zhǎng)度，小于 DDL 語(yǔ)句中聲明的該 CHAR 字段的長(zhǎng)度，ORACLE 和 MYSQL 都會(huì)在字段末尾補(bǔ)充空格(blank-pad),直到達(dá)到聲明的長(zhǎng)度;
如果用戶(hù)傳入的實(shí)際的字符串的長(zhǎng)度，大于 DDL 語(yǔ)句中聲明的該 CHAR 字段的長(zhǎng)度，ORACLE 和 MYSQL 都會(huì)報(bào)錯(cuò)退出(注意：MYSQL中，可以配置使用非 STRICT SQL MODE，此時(shí)會(huì)截?cái)喑Ｗ侄稳缓蟠鎯?chǔ)截?cái)嗪蟮闹挡⒏婢粫?huì)報(bào)錯(cuò)退出);

3.2 ORACLE 和 MYSQL 對(duì)變長(zhǎng)字段類(lèi)型 VARCHAR 的存儲(chǔ)處理類(lèi)似，都不會(huì)對(duì)用戶(hù)傳入的實(shí)際的字符串進(jìn)行加工處理，而是直接校驗(yàn)存儲(chǔ)：

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

如果用戶(hù)傳入的實(shí)際的字符串的長(zhǎng)度，大于 DDL 語(yǔ)句中聲明的該 VARCHAR 字段的最大長(zhǎng)度，ORACLE 和 MYSQL 都會(huì)報(bào)錯(cuò)(注意：MYSQL中，是報(bào)錯(cuò)還是截?cái)啵L(zhǎng)字段是正常字段還是 trailing spaces，以及是否是 STRICT SQL MODE 有關(guān));

3.3 ORACLE 和 MYSQL 對(duì)變長(zhǎng)字段類(lèi)型 VARCHAR 的取出處理類(lèi)似

ORACLE 和 MYSQL 對(duì)變長(zhǎng)字段類(lèi)型 VARCHAR 的取出處理類(lèi)似，都不會(huì)對(duì)實(shí)際存儲(chǔ)的字段值做加工處理，而是直接返回，因?yàn)榇鎯?chǔ)時(shí)已經(jīng)做了校驗(yàn)和加工。

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

ORACLE 和 MYSQL 中都是 VARCHAR(ORACLE 中是 VARCHAR2)的存儲(chǔ)空間使用率更好，因?yàn)?CHAR 類(lèi)型字段在實(shí)際長(zhǎng)度不夠時(shí)會(huì)在末尾補(bǔ)充空格(blank-pads)并存儲(chǔ)這些 trailing blanks 而 VARCHAR 不會(huì)，所以 VARCHAR 可以更有效地存儲(chǔ)數(shù)據(jù)，空間使用率更高。

4 ORACLE 和 MYSQL 對(duì) CHAR 和 VARCHAR 處理的不同點(diǎn)在哪里?

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

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

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

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

5 知識(shí)總結(jié)

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