MySQL中的（ROW_FORMAT ）-编程学习网

说明

InnoDB是MySQL中的一种存储引擎，在InnoDB中ROW_FORMAT也被用来定义表的行格式，可以通过以下方式设置：

CREATE TABLE table_name (    ...) ENGINE = InnoDB ROW_FORMAT = format_name;

其中，format_name可以取以下选项：

DEFAULT：默认情况下，使用一个存储引擎的默认行格式。DYNAMIC：该选项可以支持动态列宽和行格式，可以帮助节省磁盘空间，提高存取效率。COMPACT：该选项只存储必要的数据类型和长度信息，可以更好地利用磁盘空间，但在查询时可能需要进行类型转换操作，因此查询速度较慢。REDUNDANT：该选项存储数据类型和长度的信息，可以在查询时避免类型转换操作，但会占用更多的磁盘空间。

需要注意的是，在InnoDB中不支持FIXED和COMPRESSED选项。另外，需要注意的是，对于一个既有的表，可以使用以下语句来修改行格式：

ALTER TABLE table_name ENGINE = InnoDB ROW_FORMAT = format_name;

需要注意的是，在修改行格式的同时，也需要修改存储引擎。因为不同的存储引擎支持的行格式选项可能有所不同，并且不支持所有的选项。

Simply put

InnoDB is a storage engine in MySQL that supports the ROW_FORMAT option for defining the storage format of tables. It determines how the rows of data are stored and organized within the table.

In InnoDB, the ROW_FORMAT option has several available options, including:

DEFAULT : This is the default storage format, where InnoDB automatically selects the appropriate format based on the row size.
COMPACT : This format stores data in a compact manner, with variable-length column prefixes and off-page storage for large variable-length columns.
REDUNDANT : This format is similar to COMPACT but includes additional storage for redundant information, which can aid in crash recovery.
DYNAMIC : This format allows for dynamic-length rows, where each row’s size can be adjusted based on the actual data stored.
COMPRESSED : This format compresses the row data to reduce storage space. It uses the same storage format as DYNAMIC, but with compression applied.

To specify the ROW_FORMAT option in InnoDB, you can use the ROW_FORMAT clause when creating or altering a table. For example:

CREATE TABLE your_table (    column1 INT,    column2 VARCHAR(50),    ...) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

It’s important to note that the availability of ROW_FORMAT options may vary depending on the version of MySQL and the specific configuration of the InnoDB engine. Please refer to the MySQL documentation for more detailed information and options applicable to your specific environment.

执行原理

InnoDB引擎的ROW_FORMAT对于InnoDB表中的数据存储方式有重要影响，不同的ROW_FORMAT选项会影响到表的存储空间、查询性能和数据的可靠性等方面。在InnoDB中，ROW_FORMAT的执行原理如下：

COMPACT：使用最小的存储空间来存储数据，每个列只使用必要的字节数。对于可变长度的数据类型，InnoDB使用了类似于VARCHAR的方式来存储数据，以减少存储空间。对于固定长度的数据类型，则直接存储为固定长度，以减少存储和查询操作的消耗。

DYNAMIC：与COMPACT类似，但是允许变更行数据的长度，可以在需要时增加或减少列的长度或数目，以适应不同的数据类型和数据长度。DYNAMIC格式还可以支持TEXT和BLOB等大型数据类型，可以将大对象存储在独立的表空间中，并在需要时进行读取。

REDUNDANT：与COMPACT类似，但会存储更多的数据类型和长度信息，以减少查询时的数据转换和计算。与DYNAMIC相比，REDUNDANT的空间利用率较低，但适用于那些包含许多可变长度列、查询操作较多或数据的读取速度更为重要的应用程序。

在InnoDB中，ROW_FORMAT也会影响到表的查询性能。因为不同的ROW_FORMAT选项会影响到InnoDB表的索引大小和查询效率，因此在选择ROW_FORMAT选项时需要进行权衡。同时，ROW_FORMAT也会影响到InnoDB表的数据可靠性，因为一些错误或故障可能会导致数据损坏或丢失。因此，在选择ROW_FORMAT选项时，需要根据具体情况来进行权衡和决策。

Exec

The ROW_FORMAT option in InnoDB determines the internal storage format for rows in a table. It affects how the data is organized and stored on disk. The execution principle of ROW_FORMAT in InnoDB involves the following aspects:

Fixed-length columns: InnoDB stores fixed-length columns, such as INT or DATE, directly in the data page. This allows for efficient retrieval and scanning of the data.
Variable-length columns: InnoDB uses an off-page storage technique for variable-length columns, such as VARCHAR. The actual data is stored in separate overflow pages, and the main data page contains a pointer to the overflow page.
NULL values: InnoDB optimizes storage for NULL values. It only requires one bit per column to indicate whether a column is NULL or not.
Variable-length column prefixes: InnoDB supports prefix compression for variable-length columns. It stores only the first few characters of a column value directly in the main data page, which helps to save storage space.
Row format selection: InnoDB automatically selects the most appropriate row format based on the table’s schema and the size of the data being stored. It aims to minimize storage space and improve performance.
Compression: InnoDB also provides the option to compress the row data using the COMPRESSED row format. This can significantly reduce storage requirements, but it comes with some overhead for compression and decompression during data access.

It’s important to note that the specific implementation details of the ROW_FORMAT option in InnoDB may vary based on the version of MySQL and the configuration settings. The InnoDB storage engine documentation provides more in-depth information on the internal workings of ROW_FORMAT.

来源地址：https://blog.csdn.net/weixin_38233104/article/details/131499619