Is the primary key in mysql an index?

The primary key in mysql is not an index. The full name of primary key is "primary key constraint", which is a constraint on the data in the table. It is a special field of the table, which can uniquely identify each piece of information in the table; and the index is a special database structure, which is composed of data. A combination of one or more columns in a table, which can be used to quickly query records with a specific value in the data table.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL Primary Key

In MySQL, the full name of the primary key (PRIMARY KEY) is "primary key constraint", which is the most frequently used constraint in MySQL. Under normal circumstances, in order to facilitate the DBMS to find records in the table faster, a primary key will be set in the table.

Constraints refer to a constraint on the data in the table, which can help database administrators better manage the database and ensure the correctness and validity of the data in the database.

The primary key is a special field in the table that uniquely identifies each piece of information in the table. For example, the student number in the student information table is unique.

Primary keys are divided into single-field primary keys and multi-field joint primary keys. This section will explain the creation, modification and deletion of these two primary key constraints respectively.

The following points should be noted when using primary keys:

• Each table can only define one primary key.

• The primary key value must uniquely identify each row in the table and cannot be NULL, that is, there cannot be two rows of data with the same primary key value in the table. This is the principle of uniqueness.

• A field name can only appear once in the joint primary key field table.

• The joint primary key cannot contain unnecessary redundant fields. When a field in the joint primary key is deleted, if the primary key composed of the remaining fields still satisfies the uniqueness principle, then the joint primary key is incorrect. This is the principle of minimization.

Set the primary key constraint when creating the table

Set the primary key constraint when creating the data table, either for the table Set a primary key for a field in the table, or set a joint primary key for multiple fields in the table. But no matter which method is used, there can only be one primary key in a table. The following explains how to set a single-field primary key and a multi-field joint primary key.

1) Set a single-field primary key

In the CREATE TABLE statement, specify the primary key through the PRIMARY KEY keyword.

Specify the primary key while defining the field. The syntax format is as follows:

<字段名> <数据类型> PRIMARY KEY [默认值]

Example 1

Create the tb_emp3 data table in the test_db database, whose primary key is id, SQL statement and The running results are as follows.

mysql> CREATE TABLE tb_emp3
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> DESC tb_emp3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

Or specify the primary key after defining all fields. The syntax format is as follows:

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

Example 2

Create the tb_emp4 data table in the test_db database, and its primary key is id , the SQL statements and running results are as follows.

mysql> CREATE TABLE tb_emp4
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

2) Set the joint primary key when creating the table

The so-called joint primary key means that the primary key is composed of multiple fields in a table.

For example, when setting up a student course selection data table, should the student number be used as the primary key or the course number as the primary key? If the student number is used as the primary key, then a student can only choose one course. If the course number is used as the primary key, then only one student can choose a course. Obviously, both of these situations are unrealistic.

In fact, when designing a student course selection schedule, the limitation is that a student can only choose the same course once. Therefore, the student number and course number can be put together as the primary key, which is a joint primary key.

The primary key is composed of multiple fields. The syntax format is as follows:

PRIMARY KEY [字段1，字段2，…,字段n]

Note: When the primary key is composed of multiple fields, the primary key constraint cannot be declared directly after the field name.

Example 3

Create the data table tb_emp5. Assume that there is no primary key id in the table. In order to uniquely identify an employee, you can combine name and deptId as the primary key. The SQL statement and running results are as follows.

mysql> CREATE TABLE tb_emp5
    -> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp5;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(25) | NO   | PRI | NULL    |       |
| deptId | int(11)     | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.14 sec)

Add primary key constraints when modifying the table

Primary key constraints can not only be created while creating the table, but can also be added when modifying the table . However, it should be noted that null values ​​are not allowed in fields set as primary key constraints.

The syntax format for adding primary key constraints when modifying the data table is as follows:

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

View the table structure of the tb_emp2 data table. The SQL statement and running results are as follows.

mysql> DESC tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

Example 4

Modify the data table tb_emp2 and set the field id as the primary key. The SQL statement and running results are as follows.

mysql> ALTER TABLE tb_emp2
    -> ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.12 sec)

Normally, when you want to set the primary key constraint of a field in the table when modifying the table, you must ensure that the values ​​in the field set as the primary key constraint cannot have duplicates and must be non-empty. . Otherwise, the primary key constraint cannot be set.

MySQL Index (Index)

The index is a special database structure, which is composed of one or more columns in the data table. It can be used to quickly query a certain column in the data table. Records of specific values.

Through the index, when querying data, you do not need to read all the information in the record, but only query the index column. Otherwise, the database system will read all information of each record for matching.

The index can be compared to the phonetic sequence of the Xinhua Dictionary. For example, if you want to look up the word "ku", if you don't use phonetic sequence, you need to find it page by page in the 400 pages of the dictionary. However, if you extract the pinyin to form a phonetic sequence, you only need to look it up directly from the phonetic table of more than 10 pages. This can save a lot of time.

Advantages and Disadvantages of Index

Index has its obvious advantages and its inevitable disadvantages.

Advantages

The advantages of indexes are as follows:

• By creating a unique index, the uniqueness of each row of data in the database table can be guaranteed.

• You can set indexes for all MySQL column types.

• can greatly speed up data query, which is the main reason for using indexes.

• It can speed up the connection between tables in terms of achieving referential integrity of data.

• When using grouping and sorting clauses for data query, the time of grouping and sorting in the query can also be significantly reduced

Disadvantages

Increasing indexes also has many disadvantages, mainly as follows:

• It takes time to create and maintain index groups, and the time spent will increase as the amount of data increases.

• Indexes need to occupy disk space. In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If you have a large number of indexes, the index files may reach their maximum file size faster than the data files.

• When the data in the table is added, deleted, and modified, the index must also be dynamically maintained, which reduces the data maintenance speed.

When using indexes, you need to consider the advantages and disadvantages of indexes.

Indexes can increase query speed, but will affect the speed of inserting records. Because when inserting records into an indexed table, the database system will sort according to the index, which reduces the speed of inserting records. The speed impact will be more obvious when inserting a large number of records. In this case, the best way is to delete the index in the table first, then insert the data, and then create the index after the insertion is completed.

[Related recommendations: mysql video tutorial]

The above is the detailed content of Is the primary key in mysql an index?. For more information, please follow other related articles on the PHP Chinese website!