The difference between key, primary key, unique key and index in MySQL

one, key and primary key difference
CREATE TABLE Wh_logrecord (
logrecord_id Int (one) not NULL auto_increment,
User_name varchar (+) default NULL,
Operation_time datetime default NULL,
Logrecord_operation varchar (+) default NULL,
PRIMARY KEY (logrecord_id),
KEY Wh_logrecord_user_name (USER_NAME)
)
Analytical:
KEY Wh_logrecord_user_name (USER_NAME)
The User_name field in this table establishes a foreign key with the Wh_logrecord_user_name table user_name field
Outside the parentheses is the table that establishes the foreign key, the corresponding field in parentheses
Similar to the KEY user (userid)
Of course, key may not all be foreign keys

Summarize:
Key is an index constraint that constrains the index of a field in a table by primary foreign unique. Common with foreign key, foreign key association.

Key Forum (Status,type,displayorder) # is a multi-column index (key)
Key Tid (TID) # is a single-column index (key).

When building a table: KEY forum (Status,type,displayorder)
Whether the select * from table group BY Status,type,displayorder automatically uses this index,
And when select * from table group By status is this index useful?

Key uses: Mainly to speed up the query.

second, key and index difference
Comment: I still foggy this part.
Key is usually the index synonym. If the keyword attribute primary key is given in the column definition, then primary key can also be specified as key only. The purpose of this is to be compatible with other database systems. PRIMARY key is a unique key, at which point all the keyword columns must be defined as not NULL. These columns should be implicitly defined if these columns are not explicitly defined as not null,mysql. A table has only one primary KEY.


The difference between index and key in MySQL

Key is a part of the relational model theory, such as a primary key (Primary key), a foreign key (Foreign key), etc., for data integrity checking and uniqueness constraints. Index is at the implementation level, such as the table can be indexed to any column, then when the indexed column in the SQL statement in the where condition, you can get fast data positioning, so as to quickly retrieve. As for the unique index, it is only one of the index, the creation of a unique index indicates that this column of data is not repeatable, guess MySQL index of unique index type can be further special optimization bar.

Thus, when designing a table, key is only at the model level, and when query optimization is required, the related columns are indexed.

Also, in MySQL, for a column of primary key, MySQL has automatically created a unique index on it, without having to re-index it.

A section of the search explanation:

Note that the "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).


Iii. What is the difference between a unique key and primary key in MySQL
The 1 or more columns of the 1,primary key must be not NULL, and if the column is null, the columns are automatically changed to NOT NULL when the Primary key is added. The unique KEY does not have this requirement for columns

2, a table can have only one primary key, but there may be multiple unique keys

3, primary key and unique key constraints are implemented through the reference index, if the inserted values are null, then according to the principle of the index, all null values are not recorded on the index, so when inserting a full null value, you can have duplicates, while others cannot insert duplicate values.
ALTER TABLE t add constraint uk_t_1 unique (a, b);
INSERT into t (A, B) values (null,1); # Can't repeat
INSERT into t (A, B) values (null,null); #可以重复

Iv. use of unique KEY
CREATE TABLE ' secure_vulnerability_warning ' (
' id ' int (ten) is not NULL auto_increment,
' Date ' date is not NULL,
' type ' varchar (+) not NULL,
' Sub_type ' varchar (+) not NULL,
' domain_name ' varchar (+) not NULL,
' url ' text not NULL,
' Parameters ' text is not NULL,
' Hash ' varchar (+) not NULL,
' Deal ' int (1) is not NULL,
' deal_date ' date default NULL,
' Remark ' text,
' Last_push_time ' datetime default NULL,
' Push_times ' int (one) 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 use of UNIQUE key is mainly used to prevent duplication of data when it is inserted.

1, when creating a table
CREATE TABLE Persons
(
id_p int not NULL,
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
UNIQUE (id_p)
)
If you need to name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:
CREATE TABLE Persons
(
id_p int not NULL,
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
CONSTRAINT Uc_personid UNIQUE (id_p,lastname)
)

2, when the table has been created, to create a UNIQUE constraint in the "id_p" column, use the following SQL:

ALTER TABLE Persons
ADD UNIQUE (id_p)

To name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT Uc_personid UNIQUE (id_p,lastname)


3, REVOKE UNIQUE constraint

To revoke a UNIQUE constraint, use the following SQL:
Mysql:

ALTER TABLE Persons
DROP INDEX Uc_personid

The difference between key, primary key, unique key and index in MySQL