MySQL數(shù)據(jù)庫字段超長問題的解決
存在的問題
在向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ù)庫字段中,建議只保存相對不重要,且不需要作為唯一的查詢條件的字段,在進(jìn)行保存時也需要考慮字段超長問題,及新舊數(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(n) | 255 | 字符數(shù) |
VARCHAR(n) | 65535 | 字符數(shù) |
TINYTEXT | 255 | 字節(jié)數(shù) |
TEXT | 65535 | 字節(jié)數(shù) |
MEDIUMTEXT | 16777215 | 字節(jié)數(shù) |
LONGTEXT | 4294967295 | 字節(jié)數(shù) |
需要注意,CHAR、VARCHAR類型字段的最大長度的單位為字符數(shù),能夠保存的漢字?jǐn)?shù)量等于最大支持字符數(shù);
TEXT等類型字段的最大長度的單位為字節(jié)數(shù),能夠保存的漢字?jǐn)?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進(jìn)行最左匹配查詢,很難從保存JSON形式的字段中查詢到需要的數(shù)據(jù);二是因?yàn)镮nnoDB索引支持的長度有限(在MySQL InnoDB默認(rèn)配置下,索引支持的最大長度為768字節(jié));
- 僅通過變長字段進(jìn)行查詢時,無法通過B+樹結(jié)構(gòu)的索引進(jìn)行查詢,而是需要在單鏈表形式的溢出頁中逐條進(jìn)行查詢,查詢效率會非常低。
查詢變長字段,與不查詢變長字段相比,開銷會更大,耗時會更長。
因?yàn)椴樵冏冮L字段時,會增加從溢出頁中查詢數(shù)據(jù)的步驟;且需要返回的數(shù)據(jù)量可能較大,數(shù)據(jù)返回耗時會增加。
在查詢包含變長字段的數(shù)據(jù)庫表時,假如不需要獲取變長字段,則不應(yīng)該在SQL語句中指定查詢變長字段。
2. 可以截?cái)嗟淖侄?/h3>
對于截?cái)嗪蟛挥绊懯褂玫淖侄危趯懭牖蚋聰?shù)據(jù)庫時,可對存在超長風(fēng)險(xiǎn)的字段按照數(shù)據(jù)庫字段長度進(jìn)行截?cái)啵?/p>
JDK中的String.substring()方法,commons-lang3中的StringUtils.substring()、StringUtils.truncate()方法,參數(shù)中的數(shù)字單位都是字符數(shù),不是字節(jié)數(shù)。
在Java中對字符串進(jìn)行截取時,建議使用StringUtils.truncate()方法。
MySQL中的CHAR、VARCHAR類型的最大長度,也是字符數(shù),不是字節(jié)數(shù)。
因此在Java中對字符串根據(jù)MySQL的字符串類型字段長度進(jìn)行截取時,兩者的長度是一致的。
例如MySQL中的字段為VARCHAR(200),則可使用以下方式進(jìn)行截取,將截取結(jié)果寫入數(shù)據(jù)庫。
StringUtils.truncate("xxx", 200);
總結(jié)
以上為個人經(jīng)驗(yàn),希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
MySQL數(shù)據(jù)遷移使用MySQLdump命令
今天小編就為大家分享一篇關(guān)于MySQL數(shù)據(jù)遷移使用MySQLdump命令,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來看看吧2018-10-10Navicat無法連接MySQL報(bào)錯1251的解決方案
這篇文章主要為大家詳細(xì)介紹了Navicat無法連接MySQL報(bào)錯1251的解決方案,文中解決方法介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2023-12-12MySQL的隱式鎖(Implicit Lock)原理實(shí)現(xiàn)
MySQL的InnoDB存儲引擎中隱式鎖是一種自動管理的鎖,用于保證事務(wù)在行級別操作時的數(shù)據(jù)一致性和安全性,本文主要介紹了MySQL的隱式鎖(Implicit Lock)原理實(shí)現(xiàn),感興趣的可以了解一下2025-03-03Mysql中find_in_set()函數(shù)用法詳解以及使用場景
前幾天在sql查詢的時候,想要判斷數(shù)據(jù)庫中表的某一列中的值是否在List集合中,接觸到了find_in_set的使用,用起來方便快捷,下面這篇文章主要給大家介紹了關(guān)于Mysql中find_in_set()函數(shù)用法詳解以及使用場景的相關(guān)資料,需要的朋友可以參考下2023-03-03