MySQL的主鍵命名挺任性，就這麼定了

  • 2019 年 11 月 28 日
  • 筆記

最近在梳理數據生命周期管理的細節時，發現了一個小問題，那就是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_data2G  *************************** 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_data2G  *************************** 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_data3G  *************************** 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`)  )；

查看主鍵命名方法4：使用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'

以上的方法都可以讓我們對這個細節有更深入的理解，當然我們可以再深入一些。

查看主鍵命名方法5：官方文檔

官方文檔裡面其實包含了這個資訊，但是不是很明顯。

關於主鍵的描述，大體內容如下，有一條是專門做了聲明，主鍵名為PRIMARY.

  • 一個表只能有一個PRIMARY KEY。
  • PRIMARY KEY的名稱始終為PRIMARY，因此不能用作任何其他類型的索引的名稱。
  • 如果您沒有PRIMARY KEY，而應用程式要求您在表中提供PRIMARY KEY，則MySQL將返回沒有NULL列的第一個UNIQUE索引作為PRIMARY KEY。
  • 在InnoDB表中，將PRIMARY KEY保持較短，以最小化輔助索引的存儲開銷。每個輔助索引條目都包含對應行的主鍵列的副本。
  • 在創建的表中，首先放置一個PRIMARY KEY，然後放置所有UNIQUE索引，然後放置非唯一索引，這有助於MySQL優化器確定使用哪個索引的優先順序，還可以更快地檢測重複的UNIQUE鍵。

查看主鍵命名方法6：源程式碼

在sql_table.cc 裡面對主鍵名稱做了定義聲明。

const char *primary_key_name="PRIMARY";

順著這條路，可以看到在不同層的實現中的一些邏輯情況。

小結：

通過這樣的一些方式，我們對主鍵的命名情況有了一個整體的認識，為什麼會採用PRIMARY這樣一個命名呢，我總結了幾點：

1）統一命名可以理解是一種規範

2）和唯一性索引能夠區別開來，比如一個唯一性索引非空，從屬性上來看很相似的，通過主鍵命名就可以區分出來，在一些特性和索引使用場景中也容易區分。

3）主鍵是一個表索引的第一個位置，統一命名可以在邏輯判斷中更加清晰，包括欄位升級為主鍵的場景等等。

4）在優化器處理中也會更加方便，提高MySQL優化器確定使用哪個索引的優先順序。

VirMach 便宜 VPS

QNews

QNews