tl;dr The default row format for InnoDB tables in MySQL 5.6 is COMPACT
and in MySQL 5.7 is DYNAMIC
.
TIL there is a difference in the default InnoDB Row Format value between MySQL 5.6 and 5.7. I'll 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 how its rows are 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_format
variable. 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 encouter 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;