MySQL的主键命名策略相关

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

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

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

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

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是正常处理了。

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

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

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就可以。

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来进行数据提取。

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 命令

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

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.

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.

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语句。

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”;

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

小结:

通过这样的一些方式,我们对主键的命名情况有了一个整体的认识,为什么会采用primary这样一个命名呢,我总结了几点:

1)统一命名可以理解是一种规范

2)和唯一性索引能够区别开来,比如一个唯一性索引非空,从属性上来看很相似的,通过主键命名就可以区分出来,在一些特性和索引使用场景中也容易区分。

3)主键是一个表索引的第一个位置,统一命名可以在逻辑判断中更加清晰,包括字段升级为主键的场景等等。

4)在优化器处理中也会更加方便,提高mysql优化器确定使用哪个索引的优先级。

以上就是mysql的主键命名策略相关的详细内容,更多关于mysql 主键命名策略的资料请关注www.887551.com其它相关文章!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐