Mysql key, primary key, unique key and index difference _mysql

MySQL in the index is very important knowledge points, compared to other knowledge points, the index is more difficult to grasp, and MySQL in the index of a lot of types, such as primary key, unique key and index, etc., this article to introduce you to the MySQL key, primary Key, unique key differs from index.

One, key and primary key difference

CREATE TABLE Wh_logrecord ( 
logrecord_id Int (one) not NULL auto_increment, 
user_name varchar (MB) Default null,< C4/>operation_time datetime default NULL, 
logrecord_operation varchar (MB) 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 corresponding table that establishes the foreign key, and the corresponding field in parentheses
Similarly, KEY User (UserID)
Of course, key is not always foreign key

Summarize:

Key is an index constraint, which is indexed by the fields in the table, created by primary foreign unique, and so on. Common have foreign key, foreign Key association uses.

Key Forum (Status,type,displayorder) # is a multiple-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?

Purpose of key: it is mainly used to speed up the query.

Two, key and index difference

Annotation: I still have a foggy part of this.
Key is usually the index synonym. If the keyword attribute primary key is given in the column definition, primary key can also be specified as a key. The purpose of this is to be compatible with other database systems. PRIMARY key is a unique key, at which point all the key 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 the value of the relationship model is a part of the theory, such as a primary key (Primary key), foreign key (Foreign key), etc., for data integrity detection and uniqueness constraints. Index is at the implementation level, for example, you can index any column of a table, so when the indexed column is in the Where condition in the SQL statement, you can get fast data positioning for fast retrieval. As for the unique index, it is only one of the index, set up a unique index to indicate that this column of data is not repeatable, guess that MySQL for the unique index type indexes can be further special optimization.

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

In addition, in MySQL, for a primary key column, MySQL has automatically set up a unique index, no need to repeat the above indexing.

A section of the search is explained:

Note This "PRIMARY" is called primary KEY does not INDEX.
The logical level of the KEY is something to describes your table and database design (i.e. enforces referential integrity ...)
The INDEX is something at the physical level and helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically).

What is the difference between the unique key and the primary key in MySQL?

1 or more columns of the 1,primary key must be NOT null and, if column is null, automatically changes to NOT NULL when the Primary key is added. And the unique KEY does not have this requirement for columns

2, a table can have only one primary key, but there are multiple unique key

3, primary key and unique key constraints are implemented by reference index, and if the value of the insertion is NULL, the null value is not recorded on the index by the principle of the index, so when you insert a full null value, you can have duplicates while the others cannot insert duplicate values.

ALTER TABLE t add constraint uk_t_1 unique (a,b); 
INSERT into t (A, B) values (null,1); # cannot repeat 
insert into t (A, B) values (null,null); #可以重复 

Iv. use of unique KEY

CREATE TABLE ' secure_vulnerability_warning ' ( 
 ' id ' int ') NOT NULL auto_increment, 
 ' date ' date is not null, 
 ' Type ' varchar NOT NULL, 
 ' sub_type ' varchar (m) not null, 
 ' domain_name ' varchar (128) NOT null, 
 ' URL ' Text NOT NULL, 
 ' parameters ' text is not null, 
 ' hash ' varchar is not NULL, 
 ' deal ' int (1) is not NULL, 
 ' deal_d Ate ' 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 a UNIQUE key is primarily 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) NOT NULL, 
FirstName varchar (255), c25/>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) NOT NULL, 
FirstName varchar (255), 
Address varchar (255), City 
varchar (255), 
CONSTRAINT Uc_personid UNIQUE (id_p,lastname) 
) 

2, when a table has been created, if you want 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 the UNIQUE constraint

If you want to undo the UNIQUE constraint, use the following SQL:
Mysql:

ALTER TABLE Persons
DROP INDEX Uc_personid

Thank you for reading, I hope to help you, thank you for your support for this site!