您的位置:首页 > 数据库 > > 正文

mysql主键自增策略(MySQL的主键命名策略相关)

更多 时间:2021-10-29 14:11:13 类别:数据库 浏览量:2168

mysql主键自增策略

MySQL的主键命名策略相关

最近在梳理数据生命周期管理的细节时,发现了一个小问题,那就是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";

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

    mysql主键自增策略(MySQL的主键命名策略相关)

    小结:

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

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

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

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

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

    以上就是MySQL的主键命名策略相关的详细内容,更多关于MySQL 主键命名策略的资料请关注开心学习网其它相关文章!

    原文链接:https://cloud.tencent.com/developer/article/1545560

    您可能感兴趣