Auto Generated Primary Key in MySQL

2023.04.17 16:17 Database

When a primary key is not explicitly specified, MySQL uses the first not null, unique, and integer field as the primary key. If none are found, it generates an implicit primary key ROW_ID.

Creating the following four tables, only the last one will use id as the primary key. Since secondary indexes include the primary key, let’s create a secondary index to test and observe the indexing situation on these tables.

create table nothing(id int, val int, index idx_val(val));
create table not_null(id int not null, val int, index idx_val(val));
create table `unique`(id int unique, val int, index idx_val(val));
create table not_null_unique(id int not null unique, val int, index idx_val(val));

First Table

mysql> desc nothing;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
| val   | int  | YES  | MUL | NULL    |       |
+-------+------+------+-----+---------+-------+
mysql> select index_name, stat_name, stat_description from mysql.innodb_index_stats where database_name='test' and table_name = 'nothing';
+-----------------+--------------+-----------------------------------+
| index_name      | stat_name    | stat_description                  |
+-----------------+--------------+-----------------------------------+
| GEN_CLUST_INDEX | n_diff_pfx01 | DB_ROW_ID                         |
| GEN_CLUST_INDEX | n_leaf_pages | Number of leaf pages in the index |
| GEN_CLUST_INDEX | size         | Number of pages in the index      |
| idx_val         | n_diff_pfx01 | val                               |
| idx_val         | n_diff_pfx02 | val,DB_ROW_ID                     |
| idx_val         | n_leaf_pages | Number of leaf pages in the index |
| idx_val         | size         | Number of pages in the index      |
+-----------------+--------------+-----------------------------------+

You can see that there are two indexes in the table:

The clustered index GEN_CLUST_INDEX created on the automatically generated ROW_ID.
The idx_val index we added.

Additionally, since the compound index idx_val has two prefixes: (val) and (val, primary key), we can see that MySQL uses the generated primary key DB_ROW_ID.

Second Table

mysql> desc not_null;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   |     | NULL    |       |
| val   | int  | YES  | MUL | NULL    |       |
+-------+------+------+-----+---------+-------+
mysql> select index_name, stat_name, stat_description from mysql.innodb_index_stats where database_name='test' and table_name = 'not_null';
+-----------------+--------------+-----------------------------------+
| index_name      | stat_name    | stat_description                  |
+-----------------+--------------+-----------------------------------+
| GEN_CLUST_INDEX | n_diff_pfx01 | DB_ROW_ID                         |
| GEN_CLUST_INDEX | n_leaf_pages | Number of leaf pages in the index |
| GEN_CLUST_INDEX | size         | Number of pages in the index      |
| idx_val         | n_diff_pfx01 | val                               |
| idx_val         | n_diff_pfx02 | val,DB_ROW_ID                     |
| idx_val         | n_leaf_pages | Number of leaf pages in the index |
| idx_val         | size         | Number of pages in the index      |
+-----------------+--------------+-----------------------------------+

The situation is the same.

Third Table

mysql> desc `unique`;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  | UNI | NULL    |       |
| val   | int  | YES  | MUL | NULL    |       |
+-------+------+------+-----+---------+-------+

mysql> select index_name, stat_name, stat_description from mysql.innodb_index_stats where database_name='test' and table_name = 'unique';
+-----------------+--------------+-----------------------------------+
| index_name      | stat_name    | stat_description                  |
+-----------------+--------------+-----------------------------------+
| GEN_CLUST_INDEX | n_diff_pfx01 | DB_ROW_ID                         |
| GEN_CLUST_INDEX | n_leaf_pages | Number of leaf pages in the index |
| GEN_CLUST_INDEX | size         | Number of pages in the index      |
| id              | n_diff_pfx01 | id                                |
| id              | n_leaf_pages | Number of leaf pages in the index |
| id              | size         | Number of pages in the index      |
| idx_val         | n_diff_pfx01 | val                               |
| idx_val         | n_diff_pfx02 | val,DB_ROW_ID                     |
| idx_val         | n_leaf_pages | Number of leaf pages in the index |
| idx_val         | size         | Number of pages in the index      |
+-----------------+--------------+-----------------------------------+

In addition to the previous two indexes, MySQL automatically creates a unique index id due to the unique constraint.

Fourth Table

mysql> desc not_null_unique;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   | PRI | NULL    |       |
| val   | int  | YES  | MUL | NULL    |       |
+-------+------+------+-----+---------+-------+
mysql> select index_name, stat_name, stat_description from mysql.innodb_index_stats where database_name='test' and table_name = 'not_null_unique';
+------------+--------------+-----------------------------------+
| index_name | stat_name    | stat_description                  |
+------------+--------------+-----------------------------------+
| id         | n_diff_pfx01 | id                                |
| id         | n_leaf_pages | Number of leaf pages in the index |
| id         | size         | Number of pages in the index      |
| idx_val    | n_diff_pfx01 | val                               |
| idx_val    | n_diff_pfx02 | val,id                            |
| idx_val    | n_leaf_pages | Number of leaf pages in the index |
| idx_val    | size         | Number of pages in the index      |
+------------+--------------+-----------------------------------+

You can see that GEN_CLUST_INDEX and DB_ROW_ID have disappeared, replaced by the qualified id field, indicating that MySQL chose this field as the primary key.