Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the difference between mysql primary key index and ordinary index. It is very detailed and has certain reference value. Interested friends must finish reading it.

Indexes are used to quickly find records with specific values, and all MySQL indexes are saved in the form of a B-tree. MySQL provides a variety of index types to choose from: general index, unique index, primary key index, full-text index, and so on.

General index

A normal index is the most basic type of index, and it has no restrictions such as uniqueness. A normal index can be created in the following ways:

Create an index, such as

CREATE INDEX ON tablename (list of columns)

Modify the table, for example

ALTER TABLE tablename ADD INDEX [name of index] (list of columns)

Specify an index when creating a table, for example

CREATE TABLE tablename ([...], INDEX [name of index] (list of columns))

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 the table is created, for example

CREATE TABLE tablename ([...], PRIMARY KEY (list of columns))

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 have only one primary key.

Difference

Ordinary index is the most basic index type, there are no restrictions, the value can be empty, only accelerate the query. Normal indexes can be repeated, and there can be multiple normal indexes in a table.

A primary key index is a special unique index, in which a table can have only one primary key and no null values are allowed; all values of index columns can only appear once, that is, they must be unique. To put it simply: the primary key index is an accelerated query + column values unique (no null) + only one in the table.

The above is what is the difference between mysql primary key index and ordinary index, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.