MySQL的主键命名策略相关

吾爱主题阅读：143 2024-04-02 07:59:26 评论：0

最近在梳理数据生命周期管理的细节时，发现了一个小问题，那就是MySQL的主键命名策略，似乎会忽略任何形式的自定义命名。

也就意味着你给主键命名为idx_pk_id这种形式，在MySQL里面会统一按照PRIMARY来处理。

当然我们可以在这个基础之上做一些拓展和补充。

首先来复现下问题，我们连接到数据库test,然后创建表test_data2.

1 2 3 4 5 mysql> use test mysql> create table test_data2 (id int , name varchar (30)); Query OK, 0 rows affected (0.05 sec)

接着创建一个主键，命名为idx_pk_id，从执行情况来看，MySQL是正常处理了。

1 2 3 mysql> alter table test_data2 add primary key idx_pk_id(id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

为了进一步对比，我们添加一个唯一性索引（辅助索引），来看看它们的差异。

1 2 3 mysql> alter table test_data2 add unique key idx_uniq_name( name ); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0

查看主键命名方法1：使用show indexes命令

要查看MySQL索引的信息，使用show indexes from test_data2就可以。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 mysql> show indexes from test_data2\G *************************** 1. row *************************** Table : test_data2 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null : Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table : test_data2 Non_unique: 0 Key_name: idx_uniq_name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null : YES Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)

查看主键命名方法2：使用数据字典information_schema.statistics

使用命令的方式不够通用，我们可以使用数据字典information_schema.statistics来进行数据提取。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 mysql> select * from information_schema. statistics where table_schema= 'test' and table_name= 'test_data2' limit 20 \G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: test_data2 NON_UNIQUE: 0 INDEX_SCHEMA: test INDEX_NAME: PRIMARY SEQ_IN_INDEX: 1 COLUMN_NAME: id COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: test_data2 NON_UNIQUE: 0 INDEX_SCHEMA: test INDEX_NAME: idx_uniq_name SEQ_IN_INDEX: 1 COLUMN_NAME: name COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: 2 rows in set (0.00 sec)

查看主键命名方法3：使用show create table 命令

如果查看建表语句，会发现主键名已经被过滤掉了。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> show create table test_data2\G *************************** 1. row *************************** Table : test_data2 Create Table : CREATE TABLE `test_data2` ( `id` int (11) NOT NULL , ` name ` varchar (30) DEFAULT NULL , PRIMARY KEY (`id`), UNIQUE KEY `idx_uniq_name` (` name `) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 有的同学可能想，是不是分别执行了 create , alter 语句导致处理方式有差异，我们可以一步到位，在 create 语句里面声明主键名。 CREATE TABLE `test_data3` ( `id` int (11) NOT NULL , ` name ` varchar (30) DEFAULT NULL , PRIMARY KEY idx_pk_id(`id`), UNIQUE KEY `idx_uniq_name` (` name `) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个时候查看建表语句，会发现结果和上面一样，主键名都是PRIMARY.

1 2 3 4 5 6 7 8 9 10 mysql> show create table test_data3\G *************************** 1. row *************************** Table : test_data3 Create Table : CREATE TABLE `test_data3` ( `id` int (11) NOT NULL , ` name ` varchar (30) DEFAULT NULL , PRIMARY KEY (`id`), UNIQUE KEY `idx_uniq_name` (` name `) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

查看主键命名方法4：查看约束命名

当然还有多种验证方式，比如我们使用约束的方式来命名，得到的主键名都是PRIMARY.

1 2 3 4 5 CREATE TABLE IF NOT EXISTS `default_test` ( `default_test`.`id` SMALLINT NOT NULL AUTO_INCREMENT, `default_test`.` name ` LONGTEXT NOT NULL , CONSTRAINT `pk_id` PRIMARY KEY (`id`) )；

查看主键命名方法5：使用DML报错信息

当然还有其他多种形式可以验证，比如我们使用DML语句。

1 2 3 4 mysql> insert into test_data2 values (1, 'aa' ); Query OK, 1 row affected (0.02 sec) mysql> insert into test_data2 values (1, 'aa' ); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

以上的方法都可以让我们对这个细节有更深入的理解，当然我们可以再深入一些。

查看主键命名方法6：官方文档

官方文档里面其实包含了这个信息，但是不是很明显。

关于主键的描述，大体内容如下，有一条是专门做了声明，主键名为PRIMARY.

一个表只能有一个PRIMARY KEY。
PRIMARY KEY的名称始终为PRIMARY，因此不能用作任何其他类型的索引的名称。
如果您没有PRIMARY KEY，而应用程序要求您在表中提供PRIMARY KEY，则MySQL将返回没有NULL列的第一个UNIQUE索引作为PRIMARY KEY。
在InnoDB表中，将PRIMARY KEY保持较短，以最小化辅助索引的存储开销。每个辅助索引条目都包含对应行的主键列的副本。
在创建的表中，首先放置一个PRIMARY KEY，然后放置所有UNIQUE索引，然后放置非唯一索引，这有助于MySQL优化器确定使用哪个索引的优先级，还可以更快地检测重复的UNIQUE键。

查看主键命名方法7：源代码

在sql_table.cc 里面对主键名称做了定义声明。

const char *primary_key_name="PRIMARY";

顺着这条路，可以看到在不同层的实现中的一些逻辑情况。