Detailed explanation of the differences between key, primary key, unique key and index in mysql

1. The difference between key and primary key

CREATE TABLE wh_logrecord (
logrecord_id int(11) NOT NULL auto_increment,
user_name varchar(100) default NULL,
operation_time datetime default NULL,
logrecord_operation varchar(100) default NULL,
PRIMARY KEY (logrecord_id),
KEY wh_logrecord_user_name (user_name)
)

Recommendation: "mysql video tutorial"

Analysis:

KEY wh_logrecord_user_name (user_name)

The user_name field of this table establishes a foreign key with the user_name field of the wh_logrecord_user_name table.

Outside the brackets is the corresponding table to establish the foreign key, and inside the brackets is the corresponding field

Similar to KEY user(userid)

Of course, keys may not all be foreign keys

Summary:

Key is an index constraint. Constraint indexing of fields in the table is done through Created by primary foreign unique and so on. Commonly used are foreign keys, which are used for foreign key associations.

KEY forum (status,type,displayorder) # 是多列索引（键）
KEY tid (tid) # 是单列索引（键）。

When creating a table: KEY forum (status,type,displayorder)

select * from table group by status,type,displayorder Is this index automatically used?

Is this index useful when selecting * from table group by status?

Purpose of key: Mainly used to speed up query.

2. The difference between KEY and INDEX

Note: I am still confused about this part.

KEY is usually a synonym for INDEX. PRIMARY KEY can also be specified as KEY only if the keyword attribute PRIMARY KEY is given in the column definition. This is done for compatibility with other database systems. PRIMARY KEY is a unique KEY. At this time, all key columns must be defined as NOT NULL. If these columns are not explicitly defined as NOT NULL, MySQL should define these columns implicitly. A table has only one PRIMARY KEY.

The difference between Index and Key in MySQL

Key is the key value, which is part of the relational model theory Parts, such as primary key, foreign key, etc., are used for data integrity checking and uniqueness constraints, etc. Index is at the implementation level. For example, you can index any column of a table. Then when the indexed column is in the Where condition in the SQL statement, you can get fast data location and thus fast retrieval. As for Unique Index, it is just one type of Index. The establishment of Unique Index means that the data in this column cannot be repeated. I guess MySQL can further optimize the Unique Index type index.

So, when designing the table, the Key only needs to be at the model level, and when query optimization is required, indexes can be created on the relevant columns.

In addition, in MySQL, for a Primary Key column, MySQL has automatically established a Unique Index for it, so there is no need to repeatedly create an index on it.

An explanation found:

Note that “primary” is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity…)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically).

3. There are UNIQUE KEY and PRIMARY KEY in mysql What is the difference?

1. One or more columns of the Primary key must be NOT NULL. If the column is NULL, when adding PRIMARY KEY, the column will automatically change to NOT NULL. UNIQUE KEY does not have this requirement for columns

2. A table can only have one PRIMARY KEY, but it can have multiple UNIQUE KEY

3. Primary key and unique key constraints are implemented through the reference index. If the inserted values ​​​​are all NULL, then according to the principle of the index, all NULL values ​​will not be recorded on the index, so when inserting all NULL values, there can be duplicates. Others cannot insert duplicate values.

alter table t add constraint uk_t_1 unique (a,b);
insert into t (a ,b ) values (null,1);    # 不能重复
insert into t (a ,b ) values (null,null);#可以重复

4. Use UNIQUE KEY

CREATE TABLE `secure_vulnerability_warning` (
  `id` int(10) NOT NULL auto_increment,
  `date` date NOT NULL,
  `type` varchar(100) NOT NULL,
  `sub_type` varchar(100) NOT NULL,
  `domain_name` varchar(128) NOT NULL,
  `url` text NOT NULL,
  `parameters` text NOT NULL,
  `hash` varchar(100) NOT NULL,
  `deal` int(1) NOT NULL,
  `deal_date` date default NULL,
  `remark` text,
  `last_push_time` datetime default NULL,
  `push_times` int(11) default '1',
  `first_set_ok_time` datetime default NULL,
  `last_set_ok_time` datetime default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `date` (`date`,`hash`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

The purpose of UNIQUE KEY: It is mainly used to prevent duplication when data is inserted.

1, when creating a table

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)

If you need to name UNIQUE constraints and define UNIQUE constraints for multiple columns, please use the following SQL syntax:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)

2, when When the table has been created, if you need to create a UNIQUE constraint on the "Id_P" column, please use the following SQL:

ALTER TABLE Persons
ADD UNIQUE (Id_P)

If you need to name the UNIQUE constraint and define UNIQUE constraints for multiple columns, please use the following SQL syntax ：

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

3, cancel the UNIQUE constraint

If you need to cancel the UNIQUE constraint, please use the following SQL:

ALTER TABLE Persons
DROP INDEX uc_PersonID

The above is the detailed content of Detailed explanation of the differences between key, primary key, unique key and index in mysql. For more information, please follow other related articles on the PHP Chinese website!