The row format of anInnoDBtable determines how its rows are physically stored on disk.InnoDBsupports four row formats, each with different storage characteristics. Supported row formats includeREDUNDANT,COMPACT,DYNAMIC, andCOMPRESSED. TheDYNAMICrow format is the default. For information about row format characteristics, seeSection 15.10, “InnoDB Row Formats”.

Theinnodb_default_row_formatvariable defines the default row format. The row format of a table can also be defined explicitly using theROW_FORMATtable option in aCREATE TABLEorALTER TABLEstatement. SeeDefining the Row Format of a Table.

It is recommended that you define a primary key for each table that you create. When selecting primary key columns, choose columns with the following characteristics:

For example, in a table containing information about people, you would not create a primary key on(firstname, lastname)because more than one person can have the same name, a name column may be left blank, and sometimes people change their names. With so many constraints, often there is not an obvious set of columns to use as a primary key, so you create a new column with a numeric ID to serve as all or part of the primary key. You can declare anauto-incrementcolumn so that ascending values are filled in automatically as rows are inserted:

# The value of ID can act like a pointer between related items in different tables. CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id)); # The primary key can consist of more than one column. Any autoinc column must come first. CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

For more information about auto-increment columns, seeSection 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

Although a table works correctly without defining a primary key, the primary key is involved with many aspects of performance and is a crucial design aspect for any large or frequently used table. It is recommended that you always specify a primary key in theCREATE TABLEstatement. If you create the table, load data, and then runALTER TABLEto add a primary key later, that operation is much slower than defining the primary key when creating the table. For more information about primary keys, seeSection 15.6.2.1, “Clustered and Secondary Indexes”.

To view the properties of anInnoDBtable, issue aSHOW TABLE STATUS声明:

mysql> SHOW TABLE STATUS FROM test LIKE 't%' \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: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-02-18 12:18:28 Update_time: NULL Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment:

For information aboutSHOW TABLE STATUSoutput, seeSection 13.7.7.38, “SHOW TABLE STATUS Statement”.

You can also accessInnoDBtable properties by querying theInnoDBInformation Schema system tables:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1' \G *************************** 1. row *************************** TABLE_ID: 1144 NAME: test/t1 FLAG: 33 N_COLS: 5 SPACE: 30 ROW_FORMAT: Dynamic ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single INSTANT_COLS: 0

For more information, seeSection 15.15.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.