There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors.
•Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.
The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. For example, utf8 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255× 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.
Storage for variable-length columns includes length bytes, which are assessed against the row size.
For example, a VARCHAR(255) CHARACTER SET utf8 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.
BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.
Declaring columns NULL can reduce the maximum number of columns permitted. For MyISAM tables, NULL columns require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra, rounded up to the nearest byte. The maximum row length in
bytes can be calculated as follows:
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + delete_flag + 7)/8
+ (number of variable-length columns)
delete_flag is 1 for tables with static row format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted. delete_flag is 0 for dynamic tables because the flag is stored in the dynamic row header.
Individual storage engines might impose additional restrictions that limit table column count.
Examples:
•InnoDB permits up to 1000 columns.
•InnoDB restricts row size to something less than half a database page (approximately 8000 bytes), not including VARBINARY, VARCHAR, BLOB, or TEXT columns.
•Different InnoDB storage formats (COMPRESSED, REDUNDANT) use different amounts of page header and trailer data, which affects the amount of storage available for rows.
•Each table has an .frm file that contains the table definition. The definition affects the content of this
file in ways that may affect the number of columns permitted in the table.
InnoDB ROW_FORMAT options include COMPACT, REDUNDANT, DYNAMIC, and COMPRESSED.
For InnoDB tables, rows are stored in COMPACT format (ROW_FORMAT=COMPACT) by default. Refer to
the CREATE TABLE documentation for additional information about the ROW_FORMAT table option.
每个表格的列的数量有一个硬性的限制，就是4096，然而实际上，由于各种因素的影响，表格的有效最大数量会比4096要少。
所有的表格，不管它属于任何存储引擎，一行记录的最大长度为65535个字节。不同的存储引擎可能会有一些严格的限制，从而减少行的最大长度。
最大行记录的大小约束了表格中列的数量，因为所有列的长度不能超过行记录的最大限制。举个例子，一个utf8字符，需要三个字节组成。因此，一个CHAR(255) CHARACTER SET utf8 列，服务器需要分配255 × 3 =765个字节。因此，表格最多包含 65,535 / 765 = 85 列。
变长列的存储包含长度字节，这包含在列的最大长度里。举个例子，一个VARCHAR(255) CHARACTER SET utf8列使用两个字节来保存数据的长度，因此，这个列的长度就为767字节。Blob和text列额外使用一到四加八个字节，因为他们的内容和该行数据分开存储（原文BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.）。如果一列声明为NULL，也影响了列的最大数量，因为NULL列需要额外的空间表示该列是否为NULL。
不同的存储引擎可能对列和行的大小有不同的限制。
例如InnoDB允许列的最大数量为1000，行的最大数量为数据存储页的一半即8k，一个数据页大小为16k。
InnoDB存储引擎的文件格式包括Antelope 和Barracuda。默认情况下是Antelope。要设置文件格式，需要设置innodb_file_format。这个选项只对具有独立表格空间的表格有效。因此，要设置这个选项，需要同时设置innodb_file_per_table为true，即每个表格使用一个单独的文件。
InnoDB存储引擎的行存储格式（ROW FORMAT）包括COMPACT, REDUNDANT, DYNAMIC, and COMPRESSED四种，默认为COMPACT格式。
DYNAMIC和 COMPRESSED要使用这两种格式，需要将innodb_file_format设置为Barracuda。Barracuda文件格式也允许使用COMPACT, REDUNDANT格式。
当使用DYNAMIC和 COMPRESSED格式时，长的数据列会存储在溢出区数据页（overflow page）里，本页只存储20字节指向溢出区数据页的指针。对于小于或等于40字节的text和blob列，数据保存在本数据页里。
当使用COMPACT, REDUNDANT,格式时，长的数据列的前768个字节保存在本数据页里，额外的数据保存在溢出区数据页里。
CREATE TABLE `Player` (
`roleid` BIGINT(20) NOT NULL,
`account` VARCHAR(64) CHARACTER SET utf8 NOT NULL DEFAULT 'NONE' COMMENT '账户名',
`name` VARCHAR(64) CHARACTER SET utf8 NOT NULL DEFAULT 'NONE' COMMENT '角色名称',
`common_info` BLOB,
`hero_list` BLOB,
`equip_list` BLOB,
`skill_list` BLOB,
`itemlist` BLOB,
`counter_list` BLOB,
`pet_list` BLOB,
`task_list` BLOB,
`skin_list` BLOB,
`pass_list` BLOB,
`reply_list` BLOB,
PRIMARY KEY (`roleid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
例如上面的表格，包含有11个blob类型的列，因此当所有列的长度超过768之后，就会出现行长度过大，无法插入数据库。
Error Code : 1118
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.
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
解决方式：
SET GLOBAL innodb_file_per_table = 1;
SET GLOBAL innodb_file_format = 'Barracuda';
ALTER TABLE Player ROW_FORMAT=COMPRESSED ;
通过我们对mysql数据列和行大小的学习，我们在平时的项目开发中，最好能限制blob的字段数量在10个以内。一旦超出十个，为了避免出现问题，我们需要将mysql的innodb_file_format设置为 'Barracuda'，而Barracuda数据格式在mysql5.6.6之后才有。
