Primary Key of the InnoDB Storage engine table

Primary Key of the InnoDB Storage engine table

In the InnoDB Storage engine, tables are organized and stored in the order of primary keys. In an InnoDB Storage engine table, each table has a primary key. If the primary key is not explicitly defined during table creation, the InnoDB Storage engine selects or creates an index as follows:

• First, check whether the table has a unique index (unique not null) that is not empty. If so, the column is the primary key;
• If condition 1 is not met, the InnoDB Storage engine automatically creates a six-byte pointer (rowid column ).

When multiple non-empty and unique indexes exist in the table, InnoDB Storage engine selects the first non-empty index defined during table creation as the primary key.

Create table t_sample (
A int null,
B int not null,
C int not null,
Unique key (),
Unique key (c ),
Unique key (B)
);

Insert into t_sample select 1, 2, 3;
Insert into t_sample select 1, 4, 5;
Insert into t_sample select null, 9, 7;

Query the primary key:

Select t. *, t. _ rowid from t_sample t;

_ Rowid can display the table's primary key. It can be seen that although B and c are unique indexes, c is defined first, so InnoDB Storage engine regards it as the primary key.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151174.htm