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

2022-12-17 14:01:22

起因

引發原因：門店需求新增自提門店，自提門店需要加自提點圖片，在渠道店上引入了圖片地址img_url 的欄位，欄位值定義為text not null 但是因為text欄位不能設定預設值，所以未設定預設值；

上線時候先上線了資料庫欄位，程式碼還未上線，線上新增渠道店的時候導致該欄位為null所以不能新增渠道店報錯。

解決方案：後緊急工單更改了img_url欄位設定欄位型別為varchar(2000) 預設值為'' 線上可以新增渠道店；

問題分析：text和varchar

根據阿里巴巴開發手冊嵩山版 MySQL 資料庫篇

建表規約第八條：

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

大於此值，定義欄位型別為 text ，獨立出來一張表，用主鍵來對應，避免影響其它欄位索引效

率

第十三條：

【推薦】欄位允許適當冗餘，以提高查詢效能，但必須考慮資料一致。冗餘欄位應遵循：

1）不是頻繁修改的欄位。

2）不是唯一索引的欄位。

3）不是 varchar 超長欄位，更不能是 text 欄位。

正例：各業務線經常冗餘儲存商品名稱，避免查詢時需要呼叫 IC 服務獲取。

ORM 對映

第一條：

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

說明：1）增加查詢分析器解析成本。2）增減欄位容易與 resultMap 設定不一致。3）無用欄位增加網路

消耗，尤其是 text 型別的欄位。

mysql的官方檔案

mysql的官方檔案上寫了varchar型別最長可以指定為65535位元組，

但我們建表的時候，實際上因為指定了字元集的問題，如果是在utf8的字元編碼下，實際上最大的長度只能為 21845，因為utf8一個字元佔3個位元組

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的限制是在一行資料裡面限制的總數為65535，即：
如果我們有門店表：貨號指定為21845長度，品名指定為21845長度，還是不能建表成功，因為65535的限制是一行的所有欄位值加起來的限制。
（此處不僅限制varchar，是除了BLOBs的型別都會被限制）
會出現如下報錯：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資料型別

可以儲存1-4GB位元組長度，MySQL並不把TEXT資料儲存在記憶體中，而是儲存與磁碟中，所以MySQL每次必須從磁碟讀取，導致它比varchar要慢

TINYTEXT 255位元組 (2KB)

TEXT 65,535位元組 (64KB)

MEDIUMTEXT 16,777,215位元組(16M)

LONGTEXT 4,294,967,295位元組(4GB)

附：MYSQL中varchar和TEXT差異點

text 欄位，MySQL不允許有預設值。建立索引必須給出字首索引長度。

varchar 允許有預設值，對索引長度沒限制。

注：InnoDB 引擎單一欄位索引的預設長度最大為 767 位元組，MyISAM 為 1000 位元組。例如字元編碼是 utf8，那麼 varchar 的索引最大長度是 256 個字元。超出限制會導致索引建立不成功，轉而需要建立字首索引。設定InnoDB_large_prefix = 1 可以增大限制，允許索引使用動態壓縮，但是表的 row_format 必須是 compressed 或者 dynamic。可以使索引列長度大於767bytes，但是總長度不能大於 3072 bytes。