MySQL primary key naming strategy related

Recently, when I was sorting out the details of data lifecycle management, I found a small problem. That is, MySQL's primary key naming strategy seems to ignore any form of custom naming.

This means that if you name the primary key as idx_pk_id, it will be treated as PRIMARY in MySQL.

Of course, we can make some expansions and additions on this basis.

First, let's reproduce the problem. We connect to the database test and create the table test_data2.

mysql> use test

mysql> create table test_data2 (id int ,name varchar(30));

Query OK, 0 rows affected (0.05 sec)

Next, create a primary key and name it idx_pk_id. From the execution situation, MySQL handles it normally.

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

For further comparison, we add a unique index (secondary index) to see the difference.

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

View primary key naming method 1: Use the show indexes command

To view MySQL index information, use 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)

View primary key naming method 2: Use data dictionary information_schema.statistics

The command method is not general enough. We can use the data dictionary information_schema.statistics to extract data.

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)

View primary key naming method 3: Use the show create table command

If you view the table creation statement, you will find that the primary key name has been filtered out.

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)
Some students may wonder whether the processing methods are different because the create and alter statements are executed separately. We can do it in one step and declare the primary key name in the create statement.
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;

At this time, if you check the table creation statement, you will find that the result is the same as above, and the primary key name is 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)

View primary key naming method 4: View constraint naming

Of course, there are many other ways to verify. For example, if we use constraints to name the primary key, the primary key name we get is 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`)
);

View primary key naming method 5: Use DML error information

Of course, there are many other ways to verify, such as using DML statements.

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'

The above methods can give us a deeper understanding of this detail, and of course we can go deeper.

View primary key naming method 6: official documentation

The official documentation actually contains this information, but it is not very obvious.

The description of the primary key is as follows. There is a special statement that the primary key is named PRIMARY.

• A table can have only one PRIMARY KEY.
• The name of a PRIMARY KEY is always PRIMARY and therefore cannot be used as the name of any other type of index.
• If you do not have a PRIMARY KEY and your application requires you to provide a PRIMARY KEY in a table, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.
• In InnoDB tables, keep the PRIMARY KEY short to minimize the storage overhead of secondary indexes. Each secondary index entry contains a copy of the primary key columns for the corresponding row.
• In the created table, place a PRIMARY KEY first, then all the UNIQUE indexes, and then the non-unique indexes. This helps the MySQL optimizer to prioritize which index to use and also detects duplicate UNIQUE keys faster.
  

View primary key naming method 7: source code

The primary key name is defined in sql_table.cc.

const char *primary_key_name="PRIMARY";

Following this path, we can see some logical situations in the implementation of different layers.

summary:

Through these methods, we have a general understanding of the naming of primary keys. Why is PRIMARY named this way? I have summarized a few points:

1) Unified naming can be understood as a standard

2) It can be distinguished from a unique index. For example, if a unique index is not empty, they are very similar in properties and can be distinguished by naming the primary key. They are also easy to distinguish in some features and index usage scenarios.

3) The primary key is the first position of a table index. Unified naming can make logical judgments clearer, including scenarios where fields are upgraded to primary keys, etc.

4) It will also be more convenient in the optimizer processing and increase the priority of the MySQL optimizer in determining which index to use.

The above are the details related to MySQL primary key naming strategy. For more information about MySQL primary key naming strategy, please pay attention to other related articles on 123WORDPRESS.COM!