擅長 Oracle、MySQL、PostgresSQL 等多種數據庫領域；
擅長 Oracle、MySQL 性能優化、數據庫架構設計、數據庫故障修復、數據遷移以及恢復；
熱衷於研究 MySQL 數據庫內核源碼、分享技術文章，並擁有 Oracle OCP 認證；
就職於江蘇國泰新點軟件有限公司，中央研究院-DBA 技術團隊成員。
一、背景說明
項目中有一個數據交換的場景，由於使用了很多個 varchar(1000)、 varchar(2000)，導致在創建表的時候，MySQL 提示：
<code>ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help./<code>
該表有 242 個字段，都是 varchar 類型，只是長度上有所區別。
二、MySQL 的限制
說明：本文僅討論 MySQL 中，單條記錄最大長度的限制，其他的暫且擱置。
無論是 MySQL 還是 Oracle，或者是 SQL Server，其實都有這麼兩層存在，一個是 Server 層，另一個是存儲引擎層。
其實也很好理解，可以類比一下我們 Windows 操作系統，比如常說的把 D 盤格式化成 NTFS 或者 FAT32，這個 NTFS 或者 FAT32 就可以理解成數據庫中的存儲引擎。
那為什麼以前在用 SQL Server 或者 Oracle 的時候幾乎沒什麼接觸存儲引擎這個概念呢？因為這兩家都是閉源數據庫，底層怎麼實現的你也不知道，裝好了就用唄！但是 MySQL 不一樣，開源的東西，人人都可以看源碼。只要你實現了那些接口，你就可以接入到 MySQL 中，作為一個存儲引擎供 MySQL 的 Server 層使用。
說完這個概念，下面我們再說一下這個最大長度的限制。
官方文檔相關說明 - Limits on Table Column Count and Row Size https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html)
2.1 MySQL Server 的長度限制
The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes.
MySQL Server 層的限制比較寬，你的一條記錄不要超過 65535 個字節即可。
有的人就問了，怎麼可能啊？我明明可以往 MySQL 裡面放好幾百兆的附件啊，咳咳...這個後面會提到。
2.2 InnoDB 的長度限制
InnoDB 作為現在官方唯一還在繼續開發和支持的存儲引擎（下一個版本 MySQL 8.0 中就默認看不到原先的 MyISAM 了），其長度限制比較嚴格，其大致的算法如下
一條記錄的長度，不能超過 innodb_page_size 大小的一半（實際上還要小一點，因為要扣除一些頁中元數據信息）
即默認MySQL官方推薦的 16K 的頁大小，單條記錄的長度不能超過 8126Byte。
為什麼會有這種限制呢，其實也很好理解，InnoDB 是以 B+ 樹來組織數據的，假設允許一行數據填滿整個頁，那麼 InnoDB 中的數據結構將會從 B+樹退化為單鏈表，所以 InnoDB 規定了一個頁至少包含兩行數據。
那這就好理解了，項目中給出的建表語句的字段中，有好幾十個 varhcar(1000) 或者 varchar(2000)，累加起來已經遠遠超過了 8126 的限制。
2.3 字段個數的限制
同樣，除了長度，對每個表有多少個列的個數也是有限制的，這裡簡單說一下：
1. MySQL Server 層規定一個表的字段個數最大為 4096；
2. InnoDB 層規定一個表的字段個數最大為 1017；
[官方文檔相關說明 - Limits on InnoDB Tables] https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
至於為什麼有這個限制，其實可以不用深究，因為是代碼中寫死的。
至於原因，個人猜測和 MySQL 的定位有關係，MySQL 一直定位於 OLTP 業務，OLTP 業務的特點就是短平快，字段數過多或者長度太長，都會影響 OLTP 業務的 TPS（所以那些拿 MySQL 存附件的同時，還在抱怨 MySQL 性能差的同學，是不是該反思一下了？）
三、TEXT 類型的字段
回到我們項目中的問題，既然那麼多 varchar 超過了限制，那按照提示，我們直接把所有字段改成 TEXT是不是就可以了呢？
我們做了測試，發現依然失敗，提示和之前一樣。
3.1 TEXT 字段介紹
官方文檔說明 - innodb-row-format-dynamic https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html
關於 TEXT 字段的存儲的方式和很多因素有關，他除了和本身記錄的格式（參數 INNODB_ROW_FORMART，當前默認格式為 DYNAMIC）有關係，同時和當前記錄所在的頁的存儲長度也有關係，簡單歸納一下：
1. 在 COMPACT 格式下，TEXT 字段的前 768 個字節存儲在當前記錄中，超過的部分存儲在溢出頁(overflow page)中，同時當前頁中增加一個 20 個字節的指針（即 SPACEID + PAGEID + OFFSET）和本地長度信息（2 個字節），共計 768 + 20 + 2 = 790 個字節存儲在當前記錄。
2. 在 DYNAMIC 格式下，一開始會盡可能的存儲所有內容，當該記錄所在的頁快要被填滿時，InnoDB 會選擇該頁中一個最長的字段（所以也有可能是 BLOB 之類的類型），將該字段的所有內容存儲到溢出頁（overflow page）中，同時在原記錄中保留 20 個字節的指針`。
3. 當 TEXT 字段存儲的內容不大於 40 個字節時，這 40 個字節都會存儲在該記錄中，此時該字段的長度為 40 + 1（本地長度信息）= 41 個字節。
這裡提到一個溢出頁的概念，其實就是 MySQL 中的一種數據存儲機制，當一條記錄中的內容，無法存儲在單獨的一個頁內（比如存儲一些大的附件），MySQL 會選擇部分列的內容存儲到其他數據頁中，這種僅保存數據的頁就叫溢出頁（overflow page）。
3.2 計算 TEXT 字段的最大列數
有了上述概念，現在我們可以來算一下 TEXT 字段一共可以存儲多少列（以目前默認的 DYNAMIC 格式，且 innodb_strict_mode=on 為例），假設可以存儲 x 列。
除了我們創建的字段，每個記錄（ROW）中還存在元信息：
1. header 信息（5 個字節）；
2. 列是否為 null 的 bitmap 信息（ceil(x/8) 即向上取整）
3. 系統字段：主鍵 ID（6 個字節）、事物 ID（6個字節）、回滾指針（7 個字節）；
那麼計算公式就是：
<code>$5 + ceil(x/8) + 6 + 6 + 7 + x * 41 <= 8126，求 x 的解 $。/<code>
最終我們可以計算出符合該公式的 x 的解為 197。
那是不是就可以插入 197 個的 text 呢？我們又做了一個測試，發現還是失敗的（What's The F**K？）。
最終通過源碼我們找到了問題的答案（以當前最新的 5.7.20 為例）：
<code>/* Filename:./storage/innobase/dict/dict0dict.cc 第 2504 行 */if (rec_max_size >= page_rec_max) { /* 居然是 >= */ ib::error_or_warn(strict) << "Cannot add field " << field->name << " in table " << table->name << " because after adding it, the row size is " << rec_max_size << " which is greater than maximum allowed" " size (" << page_rec_max << ") for a record on index leaf page."; return(TRUE);}/<code> 通過代碼我們可以發現，不能剛好等於最大值，所以在當前 MySQL 版本（5.7.x）中，極端情況下，可以存儲 196 個 TEXT 字段。
同時我們也進行了測試，的確可以創建有且僅含有 196 個 TEXT 字段的表。
我們可以構造一下 create table 的測試語句，包含 196 個 TEXT 字段的 sql 文件 c_196.sql 和 197 個 TEXT 字段的 sql 文件 c_197.sql
<code>create table c_196( f1 text,f2 text,f3 text,......f196 text);-- 197 個字段的的類似，多增加 f197 text 字段mysql> source c_197.sqlERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.mysql> source c_196.sqlQuery OK, 0 rows affected (0.01 sec)mysql> select count(*) from information_schema.columns where table_name='c_196' and data_type='text';+----------+| count(*) |+----------+| 196 | -- 共 196 個字段+----------+1 row in set (0.00 sec)/<code>
備註：能這樣保存 196 個 TEXT 字段，並不代表我們推薦這樣做，如果業務上達到了這種限制，建議業務上做一定的拆分。
3.3 關於 innodb_strict_mode
細心的同學可能會想，如果所有 TEXT 字段都是以溢出頁（overflow page）的方式存儲，本地記錄都是以指針（20 個字節）進行存儲，那不是可以存儲更多的字段呢？
確實是的，但是 MySQL 現在開啟了嚴格模式（innodb_strict_mode=on），由於 MySQL 層面無法保證所有數據都是存儲在溢出頁（業務才能決定），所以在嚴格模式下，寧願犧牲字段個數的上限，也要確保所有的 insert 或者 update 能成功執行。
那我們關閉嚴格模式，看一下，究竟能存儲多少個 TEXT 字段呢？繼續套公式
<code>$5 + ceil(x/8) + 6 + 6 + 7 + x * 20 <= 8126，求 x 的解 $。/<code>
最終我們可以計算出符合該公式的 x 的解為 402。但是事實真的如此麼？
同樣我們也可以構造一下 create table 的測試語句，包含 402 個 TEXT 字段的 sql 文件 c_402.sql
<code>create table c_402( f1 text,f2 text,f3 text,......f402 text);mysql> source c_402.sqlQuery OK, 0 rows affected, 1 warning (0.02 sec)-- 雖然成功了，但是有一個警告mysql> show warnings\\G*************************** 1. row *************************** Level: Warning Code: 139Message: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.1 row in set (0.00 sec)mysql> show tables;+---------------+| Tables_in_db2 |+---------------+| c_402 |+---------------+1 row in set (0.00 sec)mysql> select count(*) from information_schema.columns where table_name='c_402' and data_type='text';+----------+| count(*) |+----------+| 402 |+----------+1 row in set (0.00 sec)/<code>
看到上面的執行結果，雖然 create table 執行成功了，通過 show table 也的確看了 c_402 這個 table，但是出現了 warnings。
warnings 的內容我們應該很熟悉了，因為沒有了嚴格模式的保護，mysql 允許你創建成功，但是給了一個 warning。
有興趣的同學其實可以繼續測試，其創建的 text 字段可以更多，可以達到 innodb 的最大限制 1017 個字段，如下所示：
<code>mysql> source c_1017.sqlQuery OK, 0 rows affected, 1 warning (0.04 sec) -- 一如既往的Warningsmysql> select count(*) from information_schema.columns where table_name='c_1017' and data_type='text';+----------+| count(*) |+----------+| 1017 |+----------+1 row in set (0.00 sec)mysql> source c_1018.sqlERROR 1117 (HY000): Too many columns/<code>
但是這樣做了以後，雖然建立了 1017 個 text 列，如果業務上進行 insert 或者 update 的時候，mysql 無法保證能執行。
所以項目上建議還是保持默認值，將 innodb_strict_mode 設置為 on（公司的 bin 包中已經默認開啟）
四、總結
很多同學看到這裡，可能會想，MySQL 弱暴啦，怎麼這麼多限制啊，你看 Oracle 多強啊......
其實，針對項目中這種超多字段，同時又只能用 MySQL 的場景下，我們可以使用 MySQL 5.7 中最新推出的 JSON 類型的字段，這樣 N 多數據只算在一個 JSON 字段哦，同時還有豐富的 JSON 函數予以支持，業務上使用起來其實還是比較方便的（5.6 等版本可以存在 blob 中，只是需要業務自己做 json_encode/json_decode 等操作）。
這裡更要強調的是，MySQL 作為一個絕大部分互聯網公司都在廣泛使用的 OLTP 型數據庫（微信支付的交易庫就運行在 MySQL 社區版之上），這些成功案例已經證明了 MySQL 是一個優秀的工業級數據庫。
當然除了他自身在不斷進步以外，同樣需要我們從業務上進行良好的表結構設計，編寫規範的 SQL 語句以及採用合適的集群的架構，才能發揮出 MySQL 自身的潛力，而不是一味的和 Oracle 進行對比，拿 Oracle 的優點和 MySQL 的缺點進行比較，這樣無法做到客觀和公正。
閱讀更多 愛可生 的文章
