What is the difference between mysql primary key index and ordinary index

Indexes are used to quickly find records with specific values. All MySQL indexes are saved in the form of B-trees. MySQL provides a variety of index types to choose from: ordinary index, unique index, primary key index, full-text index, etc. The following article will introduce to you the difference between primary key index and ordinary index. I hope it will be helpful to you.

Ordinary index

Ordinary index is the most basic index type, and it has no uniqueness Class restrictions. Ordinary indexes can be created in the following ways:

Create an index, for example

CREATE INDEX <索引的名字> ON tablename (列的列表);

Modify the table, for example

ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);

Specify the index when creating the table, for example

CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

Primary key index

The primary key is a unique index, but it must be specified as "PRIMARY KEY".

The primary key is usually specified when creating the table, such as

CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) );

However, we can also add the primary key by modifying the table, such as "ALTER TABLE tablename ADD PRIMARY KEY (list of columns); ". Each table can only have one primary key.

Difference

1. Ordinary index is the most basic index type without any restrictions. The value can be empty and only speeds up queries. Ordinary indexes can be repeated, and there can be multiple ordinary indexes in a table.

2. The primary key index is a special unique index. A table can only have one primary key, and no null values ​​are allowed. All values ​​in the index column can only appear once, that is, they must be unique. To put it simply: the primary key index speeds up queries. The column value is unique (cannot have null) and there is only one in the table.

The above is the detailed content of What is the difference between mysql primary key index and ordinary index. For more information, please follow other related articles on the PHP Chinese website!