The default row format for InnoDB tables
- in MySQL 5.6 is
COMPACT - in MySQL 5.7 is
DYNAMIC
Default InnoDB Row Format has been changed in MySQL 5.7. let me explain why, and provide you some more background information and good-to-know things.
What is row format?
The row format of a table determines behaviour of physically stored. This can affect the performane of queries and DML operations. As more rows fit into a single disk page:
- queries and index lookups can work faster.
- less cache memory is required in the buffer pool.
- less I/O is required to write out updated values.
Supported row formats
The InnoDB storage engine supports four row formats:
REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED.
Here's a quick overview:
| Row Format | REDUNDANT | COMPACT | DYNAMIC | COMPRESSED |
| Compact Storage Characteristics | No | Yes | Yes | Yes |
| Enhanced Variable-Length Column Storage | No | No | Yes | Yes |
| Large Index Key Prefix Support | No | No | Yes | Yes |
| Compression Support | No | No | No | Yes |
| Supported Tablespace Types | system, file-per table, general* | system, file-per-table, general* | file-per-table, general* | file-per-table, general* |
| Required File Format | Antelope or Barracuda | Antelope or Barracuda | Barracuda | Barracuda |
*only available in MySQL 5.7
Default value
The default row format for InnoDB table sis defined by
innodb_default_row_formatvariable. The default value is different in MySQL 5.6 and 5.7. If you are now aware of this difference, and you use the default value in MySQL 5.6, you can encounter problems like the following: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.The different default value between MySQL 5.6 and 5.7
The default row format for InnoDB tables in MySQL 5.6 is
COMPACT and in MySQL 5.7 is DYNAMIC.Determining the Row Format of a Table
You can determine the row format of a table, by using
SHOW TABLE STATUS:mysql> SHOW TABLE STATUS IN test1\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2016-09-14 16:29:38
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Alternatively, you can query the
INFORMATION_SCHEME.INNODB_TABLES tablemysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test1/t1';
+----------+------------+
| NAME | ROW_FORMAT |
+----------+------------+
| test1/t1 | Dynamic |
+----------+------------+
Define the row format of a table explicitly
You can explicitly define the row format of a table, by using the
ROW_FORMAT table option in a CREATE TABLE or ALTER TABLE statement. For example:CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
