MySQL建立表失敗的問題

今天有一個朋友問我一個MySQL的建表問題，問題的現象是建立表失敗，根據他的反饋，問題比較奇怪，

CREATE TABLE XXX

..此處省略260多個欄位

`xxxxIsAllowIn` varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT 'xx是否准入(是，否)',

`xxxxIsAllowIn` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '理財-准入',

PRIMARY KEY (`SERIALNO`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='???????”3èˉ·?????ˉ';

是的，你沒有看錯，還有亂碼，根據朋友反饋的現象是在生產環境可以建立成功，但是測試環境建立失敗。

報錯資訊為：

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

我把文字複製到本地，想復現，結果因為亂碼直接執行失敗，對於這種情況，還是同事幫我做了下問題過濾，採用如下的方式即可把註釋刪除。

cat a.sql |sed 's/COMMENT'.*'/,/g'

所以省事了不少，我就來繼續分析這個問題。一般來說這個錯誤看起來是單行的資料超出限制了，因為MySQL裡面每行的資料有一個65535的限制，想必是這個原因吧。

但是朋友反饋是沒有超出這個限制的，根據裡面的字元型別做計算，發現確實沒有達到65535.

所以這個問題就微妙起來，我們來說說幾種解決方式。

解決方式1：

修改儲存引擎，設定為myisam

...

KEY `idx_customerName` (`CUSTOMERNAME`)

) ENGINE=myisam DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

"c.sql" 276L, 16070C written

mysql> source c.sql

Query OK, 0 rows affected (0.07 sec)

MyISAM有3種行儲存格式：fixed/dynamic/compressed,InnoDB在這個基礎上增加了Barracuda的格式。

5.7中的預設引數設定如下：

mysql> show variables like '%format';

+---------------------------+-------------------+

| Variable_name | Value |

+---------------------------+-------------------+

| binlog_format | ROW |

| date_format | %Y-%m-%d |

| datetime_format | %Y-%m-%d %H:%i:%s |

| default_week_format | 0 |

| innodb_default_row_format | dynamic |

| innodb_file_format | Barracuda |

| time_format | %H:%i:%s |

+---------------------------+-------------------+

7 rows in set (0.00 sec)

所以現在的問題差異就在於MyISAM和InnoDB。

共享表空間的格式為Antelope,在5.5中預設就是這個格式。

解決方式2;

這個問題我做了一些測試。對比了字符集，row_format的設定。

) ENGINE=innodb row_format=dynamic DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

"c.sql" 276L, 16090C written

mysql> source c.sql

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB p

) ENGINE=innodb row_format=compact DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

"c.sql" 276L, 16090C written

mysql> source c.sql

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

) ENGINE=innodb DEFAULT CHARSET=latin1;

"c.sql" 276L, 16056C written

mysql> source c.sql

ERROR 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.

得到的一個初步結論就是先設定innodb_strict_mode為off,預設5.7是開啟的,當然從MySQL5.5版本開始，可以開啟InnoDB嚴格檢查模式，如果採用了頁資料壓縮功能後，建議是開啟該功能。在建立表，更改表和建立索引時，如果寫法有錯誤，不會有警告資訊，而是直接丟擲錯誤，這樣就可直接將問題扼殺在搖籃裡。

當然這個裡的這個問題現象確實比較糾結。

解決方法3：

從表結構設計入手，儘可能拆分這個表的邏輯，把它拆分為多個表。一個表的欄位數儘可能不要太多。資料庫、表數量儘可能少；資料庫一般不超過50個，每個資料庫下，資料表數量一般不超過500個(包括分割槽表)；可以很明顯看出這個表的設計就是根據業務的需求開始垂直擴充套件，其實可以拆分出一個邏輯表，邏輯資料很容易持續擴充套件，而不是在欄位層面來不斷擴充套件。