存在的問題

在向MySQL數(shù)據(jù)庫表中插入或更新記錄時，有時會出現(xiàn)字段超長的問題，包括但不限于以下場景：

• 處理上游系統(tǒng)發(fā)送的交易信息，將多個字段拼成一個JSON或其他格式的字符串；在增加字段，或數(shù)據(jù)較長時，寫入或更新數(shù)據(jù)庫時可能超長；
• 調(diào)用下游系統(tǒng)的服務(wù)，返回的部分字段（如錯誤信息等）較長時，導(dǎo)致更新數(shù)據(jù)庫記錄失敗。

問題解決與優(yōu)化建議

1. JSON等格式的字段

有業(yè)務(wù)含義的重要字段，不建議通過JSON字符串格式保存在一個數(shù)據(jù)庫字段中。

假如需要將字段以JSON字符串格式保存在一個數(shù)據(jù)庫字段中，建議只保存相對不重要，且不需要作為唯一的查詢條件的字段，在進行保存時也需要考慮字段超長問題，及新舊數(shù)據(jù)與新舊代碼相互之間的兼容問題。

1.1. MySQL字符串字段長度

半角英文字母、數(shù)字、符號等常見字符，1個字符占用1個字節(jié)；1個漢字字符占用3個字節(jié)。

在utf8字符集下，1個字符最多占用3個字節(jié)，不支持占用4個字節(jié)的字符。

在utf8mb4字符集下，1個字符最多占用4個字節(jié)，可以保存emoji表情等占用4個字節(jié)的字符。

MySQL字符串字段的最大長度如下所示：

需要注意，CHAR、VARCHAR類型字段的最大長度的單位為字符數(shù)，能夠保存的漢字數(shù)量等于最大支持字符數(shù)；

TEXT等類型字段的最大長度的單位為字節(jié)數(shù)，能夠保存的漢字數(shù)量不超過最大支持字節(jié)數(shù)的1/3。

1.2. MySQL使用較長的字符串類型字段影響

MySQL、MariaDB較新版本支持JSON類型字段，其他版本需要使用字符串類型字段保存。

MySQL中長度超過768字節(jié)的固定長度字段被編碼為變長字段，例如VARCHAR（超過768字節(jié)）、TEXT等，變長字段被稱為頁外列（off-page），不是保存在InnoDB的B+樹索引中，而是保存在溢出頁（overflow page）中。在溢出頁中，變長字段的值以單鏈表形式存儲。

對于保存JSON形式的字符串類型字段，由于需要保存較多內(nèi)容，很可能屬于變長字段。

保存JSON形式的字符串類型字段不適合在查詢時作為唯一的查詢條件，

原因如下：

• 保存JSON形式的字符串類型字段不適合創(chuàng)建索引：一是JSON字符串中的字段順序不固定，通過like進行最左匹配查詢，很難從保存JSON形式的字段中查詢到需要的數(shù)據(jù)；二是因為InnoDB索引支持的長度有限（在MySQL InnoDB默認配置下，索引支持的最大長度為768字節(jié)）；
• 僅通過變長字段進行查詢時，無法通過B+樹結(jié)構(gòu)的索引進行查詢，而是需要在單鏈表形式的溢出頁中逐條進行查詢，查詢效率會非常低。

查詢變長字段，與不查詢變長字段相比，開銷會更大，耗時會更長。

因為查詢變長字段時，會增加從溢出頁中查詢數(shù)據(jù)的步驟；且需要返回的數(shù)據(jù)量可能較大，數(shù)據(jù)返回耗時會增加。

在查詢包含變長字段的數(shù)據(jù)庫表時，假如不需要獲取變長字段，則不應(yīng)該在SQL語句中指定查詢變長字段。

2. 可以截斷的字段

對于截斷后不影響使用的字段，在寫入或更新數(shù)據(jù)庫時，可對存在超長風險的字段按照數(shù)據(jù)庫字段長度進行截斷；

JDK中的String.substring()方法，commons-lang3中的StringUtils.substring()、StringUtils.truncate()方法，參數(shù)中的數(shù)字單位都是字符數(shù)，不是字節(jié)數(shù)。

在Java中對字符串進行截取時，建議使用StringUtils.truncate()方法。

MySQL中的CHAR、VARCHAR類型的最大長度，也是字符數(shù)，不是字節(jié)數(shù)。

因此在Java中對字符串根據(jù)MySQL的字符串類型字段長度進行截取時，兩者的長度是一致的。

例如MySQL中的字段為VARCHAR(200)，則可使用以下方式進行截取，將截取結(jié)果寫入數(shù)據(jù)庫。

StringUtils.truncate("xxx", 200);

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論