InnoDB's default Row Format in MySQL 5.6 and 5.7

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 table

mysql> 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;