MySQL-隐式主键

青

sky2025年01月08日约 6 分钟

点击上方蓝字，关注我们

想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。

加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。

同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。

如果你有想了解的知识点希望我们发文可以后台私信。

最近联合几个 ACE 开通了一个付费微信群，加群后会有一些会员福利（分享各类技术文档，干货资源，问题解答等等），更有特邀嘉宾会定期在群内直播，解读AWR，快问快答等！有兴趣联系微：ywu0613

本期投稿人

sky，数据库女司机，青学会会员，擅长躺平和摆烂。

正文开始

探索一下MySQL数据库在何时会生成隐式主键。

1、创造数据

mysql> create table test.a(id int,a varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> select t.name,i.* from information_Schema.innodb_tables t,information_schema.innodb_indexes i where t.table_id=i.table_id and t.name='test/a';
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| name   | INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| test/a |     2454 | GEN_CLUST_INDEX |     1940 |    1 |        5 |       4 |   855 |              50 |
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
1 row in set (0.02 sec)

--如果没有显式指定主键，会自动生成一个隐式主键

2、添加UK

mysql> alter table test.a add unique key uk_a(a);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select t.name,i.* from information_Schema.innodb_tables t,information_schema.innodb_indexes i where t.table_id=i.table_id and t.name='test/a';
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| name   | INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| test/a |     2454 | GEN_CLUST_INDEX |     1940 |    1 |        5 |       4 |   855 |              50 |
| test/a |     2455 | uk_a            |     1940 |    2 |        2 |       5 |   855 |              50 |
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
2 rows in set (0.02 sec)

--仍然使用原来的隐式主键

3、重新创建只有uk的表

mysql> create table test.a(id int,a varchar(10),unique key (a));
Query OK, 0 rows affected (0.01 sec)

mysql> select t.name,i.* from information_Schema.innodb_tables t,information_schema.innodb_indexes i where t.table_id=i.table_id and t.name='test/a';
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| name   | INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| test/a |     2456 | GEN_CLUST_INDEX |     1941 |    1 |        5 |       4 |   856 |              50 |
| test/a |     2457 | a               |     1941 |    2 |        2 |       5 |   856 |              50 |
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
2 rows in set (0.03 sec)

--仍使用隐式主键？why？--因为uk未设置为非空

4、创建非空的uk表

mysql> drop table test.a;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test.a(id int,a varchar(10) not null,unique key (a));
Query OK, 0 rows affected (0.02 sec)

mysql> select t.name,i.* from information_Schema.innodb_tables t,information_schema.innodb_indexes i where t.table_id=i.table_id and t.name='test/a';
+--------+----------+------+----------+------+----------+---------+-------+-----------------+
| name   | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+--------+----------+------+----------+------+----------+---------+-------+-----------------+
| test/a |     2458 | a    |     1942 |    3 |        4 |       4 |   857 |              50 |
+--------+----------+------+----------+------+----------+---------+-------+-----------------+
1 row in set (0.02 sec)

--可以看到使用非空uk作为隐式主键

mysql> drop table test.a;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test.a(id int,a varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> select t.name,i.* from information_Schema.innodb_tables t,information_schema.innodb_indexes i where t.table_id=i.table_id and t.name='test/a';
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| name   | INDEX_ID | NAME            | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
| test/a |     2463 | GEN_CLUST_INDEX |     1946 |    1 |        5 |       4 |   861 |              50 |
+--------+----------+-----------------+----------+------+----------+---------+-------+-----------------+
1 row in set (0.02 sec)

mysql> alter table test.a add unique key uk_a(a);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select t.name,i.* from information_Schema.innodb_tables t,information_schema.innodb_indexes i where t.table_id=i.table_id and t.name='test/a';
+--------+----------+------+----------+------+----------+---------+-------+-----------------+
| name   | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+--------+----------+------+----------+------+----------+---------+-------+-----------------+
| test/a |     2464 | uk_a |     1947 |    3 |        4 |       4 |   862 |              50 |
+--------+----------+------+----------+------+----------+---------+-------+-----------------+
1 row in set (0.02 sec)

--可以看到原来是隐式生成的主键，添加非空uk后，表进行了重建，并使用uk作为隐式主键

5、有主键的情况下创建非空uk

mysql> drop table test.a;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test.a(id int,a varchar(10) not null,primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> select t.name,i.* from information_Schema.innodb_tables t,information_schema.innodb_indexes i where t.table_id=i.table_id and t.name='test/a';
+--------+----------+---------+----------+------+----------+---------+-------+-----------------+
| name   | INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+--------+----------+---------+----------+------+----------+---------+-------+-----------------+
| test/a |     2459 | PRIMARY |     1943 |    3 |        4 |       4 |   858 |              50 |
+--------+----------+---------+----------+------+----------+---------+-------+-----------------+
1 row in set (0.01 sec)


mysql> alter table test.a add unique key uk_a(a);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select t.name,i.* from information_Schema.innodb_tables t,information_schema.innodb_indexes i where t.table_id=i.table_id and t.name='test/a';
+--------+----------+---------+----------+------+----------+---------+-------+-----------------+
| name   | INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+--------+----------+---------+----------+------+----------+---------+-------+-----------------+
| test/a |     2459 | PRIMARY |     1943 |    3 |        4 |       4 |   858 |              50 |
| test/a |     2460 | uk_a    |     1943 |    2 |        2 |       5 |   858 |              50 |
+--------+----------+---------+----------+------+----------+---------+-------+-----------------+
2 rows in set (0.01 sec)