Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.4Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 33.8Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 198.4Kb
Man Pages (Zip) - 302.4Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Specifying the Row Format for a Table

15.9.2 Specifying the Row Format for a Table

In MySQL 5.7.8 and earlier, rows are stored in COMPACT format by default. As of MySQL 5.7.9, the default row format is defined by innodb_default_row_format, which has a default value of DYNAMIC. The default row format is used when the ROW_FORMAT table option is not defined explicitly or when ROW_FORMAT=DEFAULT is specified.

The row format of a table can be defined explicitly using the ROW_FORMAT table option in a CREATE TABLE or ALTER TABLE statement. For example:

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

An explicitly defined ROW_FORMAT setting overrides the implicit default. Specifying ROW_FORMAT=DEFAULT is equivalent to using the implicit default.

The innodb_default_row_format option, introduced in MySQL 5.7.9, can be set dynamically:

mysql> SET GLOBAL innodb_default_row_format=DYNAMIC;

Valid innodb_default_row_format options include DYNAMIC, COMPACT, and REDUNDANT. The COMPRESSED row format, which is not supported for use in the system tablespace, cannot be defined as the default. It can only be specified explicitly in a CREATE TABLE or ALTER TABLE statement. Attempting to set innodb_default_row_format to COMPRESSED returns an error:

mysql> SET GLOBAL innodb_default_row_format=COMPRESSED;
ERROR 1231 (42000): Variable 'innodb_default_row_format' 
can't be set to the value of 'COMPRESSED'

Newly created tables use the row format defined by innodb_default_row_format when a ROW_FORMAT option is not specified explicitly or when ROW_FORMAT=DEFAULT is used. For example, the following CREATE TABLE statements use the row format defined by innodb_default_row_format.

CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;

When a ROW_FORMAT option is not specified explicitly or when ROW_FORMAT=DEFAULT is used, any operation that rebuilds a table also silently changes the row format of the table to the format defined by innodb_default_row_format.

Table-rebuilding operations include ALTER TABLE operations that use ALGORITHM=COPY or ALTER TABLE operations that use ALGORITM=INPLACE where table rebuilding is required. See Table 15.9, “Summary of Online Status for DDL Operations” for an overview of the online status of DDL operations. OPTIMIZE TABLE is also a table-rebuilding operation.

The following example demonstrates a table-rebuilding operation that silently changes the row format of a table created without an explicitly defined row format.

mysql> SELECT @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic                     |
+-----------------------------+

mysql> CREATE TABLE t1 (c1 INT);

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 54
         NAME: test/t1
         FLAG: 33
       N_COLS: 4
        SPACE: 35
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

mysql> SET GLOBAL innodb_default_row_format=COMPACT;

mysql> ALTER TABLE t1 ADD COLUMN (c2 INT);

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 55
         NAME: test/t1
         FLAG: 1
       N_COLS: 5
        SPACE: 36
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

Consider the following potential issues before changing the row format of existing tables from REDUNDANT or COMPACT to DYNAMIC.

  • The REDUNDANT and COMPACT row format supports a maximum index key prefix length of 767 bytes whereas DYNAMIC and COMPRESSED row formats support an index key prefix length of 3072 bytes if the innodb_large_prefix configuration option is enabled. In a replication environment, if innodb_default_row_format is set to DYNAMIC on the master and set to COMPACT on the slave, the following DDL statement, which does not explicitly define a row format, succeeds on the master but fails on the slave:

    CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(5000), KEY i1(c2(3070)));

    For related information, see Section 15.6.7, “Limits on InnoDB Tables”.

  • Importing a table that does not explicitly define a row format results in a schema mismatch error if the innodb_default_row_format setting on the source server differs from the setting on the destination server. For more information, refer to the limitations outlined in Section 15.5.6, “Copying File-Per-Table Tablespaces to Another Server”.

To view the row format of a table, issue a SHOW TABLE STATUS statement or query INFORMATION_SCHEMA.TABLES.

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1' \G

The row format of an InnoDB table determines its physical row structure. See Section 15.2.6.7, “Physical Row Structure” for more information.


User Comments
Sign Up Login You must be logged in to post a comment.