mysql 8.0 表的 ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

迈不过友情╰ 2022-12-06 01:13 213阅读 0赞

os: centos 7.4  
db: mysql 8.0

# version #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    
    # mysql
    
    root@localhost 08:03:03 [test]> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.20    |
    +-----------+
    1 row in set (0.00 sec)

# row\_format #

root@localhost 07:55:38 [test]> show table status like 'tmp_t100'\G
    *************************** 1. row ***************************
               Name: tmp_t100
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 994137
     Avg_row_length: 100
        Data_length: 100286464
    Max_data_length: 0
       Index_length: 54165504
          Data_free: 4194304
     Auto_increment: NULL
        Create_time: 2020-09-10 07:45:39
        Update_time: 2020-09-10 07:27:07
         Check_time: NULL
          Collation: utf8mb4_0900_ai_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.00 sec)

root@localhost 08:01:24 [test]> select * from information_schema.innodb_tables where name='test/tmp_t100'\G
    *************************** 1. row ***************************
         TABLE_ID: 1083
             NAME: test/tmp_t100
             FLAG: 33
           N_COLS: 6
            SPACE: 26
       ROW_FORMAT: Dynamic
    ZIP_PAGE_SIZE: 0
       SPACE_TYPE: Single
     INSTANT_COLS: 0
    1 row in set (0.00 sec)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70_pic_center]

# ROW\_FORMAT \[=\] DEFAULT #

The default row format is defined by innodb\_default\_row\_format, which has a default setting of DYNAMIC. The default row format is used when the ROW\_FORMAT option is not defined or when ROW\_FORMAT=DEFAULT is used.

If the ROW\_FORMAT option is not defined, or if ROW\_FORMAT=DEFAULT is used, operations that rebuild a table also silently change the row format of the table to the default defined by innodb\_default\_row\_format.

root@localhost 00:48:07 [test]> show global variables like '%innodb_default_row_format%';
    +---------------------------+---------+
    | Variable_name             | Value   |
    +---------------------------+---------+
    | innodb_default_row_format | dynamic |
    +---------------------------+---------+
    1 row in set (0.01 sec)

# ROW\_FORMAT \[=\] DYNAMIC #

# ROW\_FORMAT \[=\] FIXED #

ROW\_FORMAT=FIXED is not supported.  
If ROW\_FORMAT=FIXED is specified while innodb\_strict\_mode is disabled, InnoDB issues a warning and assumes ROW\_FORMAT=DYNAMIC.  
If ROW\_FORMAT=FIXED is specified while innodb\_strict\_mode is enabled, which is the default, InnoDB returns an error.

# ROW\_FORMAT \[=\] COMPRESSED #

The COMPRESSED row format is a variation of the COMPACT row format.

# ROW\_FORMAT \[=\] REDUNDANT #

The REDUNDANT format provides compatibility with older versions of MySQL.

# ROW\_FORMAT \[=\] COMPACT #

The COMPACT row format reduces row storage space by about 20% compared to the REDUNDANT row format, at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed, COMPACT format is likely to be faster. If the workload is limited by CPU speed, compact format might be slower.

参考:  
https://dev.mysql.com/doc/refman/8.0/en/create-table.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html\#innodb-row-format-defining  
https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html\#innodb-row-format-dynamic  
https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html\#innodb-row-format-compressed  
https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html\#innodb-row-format-redundant  
https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html\#innodb-row-format-compact

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N0eXB5YjIwMDI_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/c87716216d704458ae36b3f151325921.png