ROW_FORMAT - COMPRESSED vs COMPACT vs DYNAMIC

Question

In MySQL InnoDB, what is the difference between COMPRESSED, COMPACT and DYNAMIC for ROW_FORMAT?

What are the benefits between each other?

Answer 1

COMPACT is format supported by Antilope. It stores first 768 bytes of BLOB in case its value doesn't fit in page.

DYNAMIC is almost the same as COMPACT except only 20 bytes for each BLOB field is used. Benefits - more BLOB fields are possible in a record.

COMPRESSED is used for compressed tables. Hence its benefits.

Answer 2

From MySQL Documentation:

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

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