快捷導(dǎo)航

MYSQL中varchar和TEXT的相關(guān)問題詳析

更新時(shí)間：2022年12月15日 11:07:51 作者：Lee5488

varchar 和 text 是 MySQL 字符存儲(chǔ)爭(zhēng)議比較多的領(lǐng)域,下面這篇文章主要給大家介紹了關(guān)于MYSQL中varchar和TEXT,文中介紹的非常詳細(xì),需要的朋友可以參考下

起因

引發(fā)原因：門店需求新增自提門店，自提門店需要加自提點(diǎn)圖片，在渠道店上引入了圖片地址img_url 的字段，字段值定義為text not null 但是因?yàn)閠ext字段不能設(shè)置默認(rèn)值，所以未設(shè)置默認(rèn)值；

上線時(shí)候先上線了數(shù)據(jù)庫字段，代碼還未上線，線上新增渠道店的時(shí)候?qū)е略撟侄螢閚ull所以不能新增渠道店報(bào)錯(cuò)。

解決方案：后緊急工單更改了img_url字段設(shè)置字段類型為varchar(2000) 默認(rèn)值為'' 線上可以新增渠道店；

問題分析：text和varchar

根據(jù)阿里巴巴開發(fā)手冊(cè)嵩山版 MySQL 數(shù)據(jù)庫篇

建表規(guī)約第八條：

【強(qiáng)制】 varchar 是可變長字符串，不預(yù)先分配存儲(chǔ)空間，長度不要超過 5000，如果存儲(chǔ)長度

大于此值，定義字段類型為 text ，獨(dú)立出來一張表，用主鍵來對(duì)應(yīng)，避免影響其它字段索引效

率

第十三條：

【推薦】字段允許適當(dāng)冗余，以提高查詢性能，但必須考慮數(shù)據(jù)一致。冗余字段應(yīng)遵循：

1）不是頻繁修改的字段。

2）不是唯一索引的字段。

3）不是 varchar 超長字段，更不能是 text 字段。

正例：各業(yè)務(wù)線經(jīng)常冗余存儲(chǔ)商品名稱，避免查詢時(shí)需要調(diào)用 IC 服務(wù)獲取。

ORM 映射

第一條：

【強(qiáng)制】在表查詢中，一律不要使用 * 作為查詢的字段列表，需要哪些字段必須明確寫明。

說明：1）增加查詢分析器解析成本。2）增減字段容易與 resultMap 配置不一致。3）無用字段增加網(wǎng)絡(luò)

消耗，尤其是 text 類型的字段。

mysql的官方文檔

mysql的官方文檔上寫了varchar類型最長可以指定為65535字節(jié)，

但我們建表的時(shí)候，實(shí)際上因?yàn)橹付俗址膯栴}，如果是在utf8的字符編碼下，實(shí)際上最大的長度只能為 21845，因?yàn)閡tf8一個(gè)字符占3個(gè)字節(jié)

utf8編碼下
Column length too big for column 'varchar1' (max = 21845); use BLOB or TEXT instead

utf8-mb4編碼下
Column length too big for column 'varchar1' (max = 16383); use BLOB or TEXT instead

但是其實(shí)65535的限制是在一行數(shù)據(jù)里面限制的總數(shù)為65535，即：
如果我們有門店表：貨號(hào)指定為21845長度，品名指定為21845長度，還是不能建表成功，因?yàn)?5535的限制是一行的所有字段值加起來的限制。
（此處不僅限制varchar，是除了BLOBs的類型都會(huì)被限制）
會(huì)出現(xiàn)如下報(bào)錯(cuò)：Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.
This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

TEXT數(shù)據(jù)類型

可以存儲(chǔ)1-4GB字節(jié)長度，MySQL并不把TEXT數(shù)據(jù)存儲(chǔ)在內(nèi)存中，而是存儲(chǔ)與磁盤中，所以MySQL每次必須從磁盤讀取，導(dǎo)致它比varchar要慢

TINYTEXT 255字節(jié) (2KB)

TEXT 65,535字節(jié) (64KB)

MEDIUMTEXT 16,777,215字節(jié)(16M)

LONGTEXT 4,294,967,295字節(jié)(4GB)

附：MYSQL中varchar和TEXT差異點(diǎn)

text 字段，MySQL不允許有默認(rèn)值。建立索引必須給出前綴索引長度。

varchar 允許有默認(rèn)值，對(duì)索引長度沒限制。

注：InnoDB 引擎單一字段索引的默認(rèn)長度最大為 767 字節(jié)，MyISAM 為 1000 字節(jié)。例如字符編碼是 utf8，那么 varchar 的索引最大長度是 256 個(gè)字符。超出限制會(huì)導(dǎo)致索引創(chuàng)建不成功，轉(zhuǎn)而需要?jiǎng)?chuàng)建前綴索引。設(shè)置InnoDB_large_prefix = 1 可以增大限制，允許索引使用動(dòng)態(tài)壓縮，但是表的 row_format 必須是 compressed 或者 dynamic?？梢允顾饕虚L度大于767bytes，但是總長度不能大于 3072 bytes。