526互联

[err] 1118 Row size too large.

发布时间 2023-08-03 19:56:56作者: zno2

 

 

一个工单表字段多+个别字段使用较长（上千） varchar，导致err 1118 

解决是将这些超长varchar 转换为 text ，注意 MBG 生成的mapper 会有变化

这种业务数据量逐年增长，表字段不断增加。可采取冷热数据分离（横向分表，业务分隔），业务字段分离（纵向分表，关联查询）

 

https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

Column Count Limits

MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors:

  • The maximum row size for a table constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. See Row Size Limits.

  • The storage requirements of individual columns constrain the number of columns that fit within a given maximum row size. Storage requirements for some data types depend on factors such as storage engine, storage format, and character set. See Section 11.7, “Data Type Storage Requirements”.

  • Storage engines may impose additional restrictions that limit table column count. For example, InnoDB has a limit of 1017 columns per table. See Section 14.23, “InnoDB Limits”. For information about other storage engines, see Chapter 15, Alternative Storage Engines.

  • 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. See Limits Imposed by .frm File Structure.

Row Size Limits

The maximum row size for a given table is determined by several factors:

  • The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.

  • The maximum row size for an InnoDB table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size. For 64KB pages, the maximum row size is slightly less than 16KB. See Section 14.23, “InnoDB Limits”.

    If a row containing variable-length columns exceeds the InnoDB maximum row size, InnoDB selects variable-length columns for external off-page storage until the row fits within the InnoDB row size limit. The amount of data stored locally for variable-length columns that are stored off-page differs by row format. For more information, see Section 14.11, “InnoDB Row Formats”.

  • Different storage formats use different amounts of page header and trailer data, which affects the amount of storage available for rows.