MYSQL中varchar和TEXT的相關問題詳析

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

varchar 和 text 是 MySQL 字符存儲爭議比較多的領域,下面這篇文章主要給大家介紹了關于MYSQL中varchar和TEXT,文中介紹的非常詳細,需要的朋友可以參考下

起因

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

上線時候先上線了數(shù)據(jù)庫字段，代碼還未上線，線上新增渠道店的時候導致該字段為null所以不能新增渠道店報錯。

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

問題分析：text和varchar

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

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

【強制】 varchar 是可變長字符串，不預先分配存儲空間，長度不要超過 5000，如果存儲長度

大于此值，定義字段類型為 text ，獨立出來一張表，用主鍵來對應，避免影響其它字段索引效

率

第十三條：

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

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

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

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

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

ORM 映射

第一條：

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

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

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

mysql的官方文檔

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

但我們建表的時候，實際上因為指定了字符集的問題，如果是在utf8的字符編碼下，實際上最大的長度只能為 21845，因為utf8一個字符占3個字節(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

但是其實65535的限制是在一行數(shù)據(jù)里面限制的總數(shù)為65535，即：
如果我們有門店表：貨號指定為21845長度，品名指定為21845長度，還是不能建表成功，因為65535的限制是一行的所有字段值加起來的限制。
（此處不僅限制varchar，是除了BLOBs的類型都會被限制）
會出現(xiàn)如下報錯：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ù)類型

可以存儲1-4GB字節(jié)長度，MySQL并不把TEXT數(shù)據(jù)存儲在內(nèi)存中，而是存儲與磁盤中，所以MySQL每次必須從磁盤讀取，導致它比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差異點

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

varchar 允許有默認值，對索引長度沒限制。

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