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

Key differs from primary key

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. If you do not have primary key and an application requires primary key to be used in the table, MySQL returns the first unique index, which does not have a null column as a primary key

1. If key is empty, then the column value can be duplicated, indicating that the column has no index, or is a non-leading column of a non-unique composite index
2. If key is a PRI, then the column is part of the primary key
3. If key is uni, then the column is the first column (the leading column) of a unique value index and must not contain a null value (NULL)
4. If key is Mul, the value of the column can be repeated, which is a leading column of a non-unique index (the first column) or a component of a unique index but can contain null values

If the definition of a column satisfies many of the 4 cases mentioned above, for example, a column is both a PRI and uni
Then "desc table name", the displayed key value is displayed according to the priority level Pri->uni->mul
So at this point, the PRI is displayed

A unique index column can be displayed as a PRI, and the column cannot contain null values, and the table does not have a primary key

A unique index column can be displayed as Mul if multiple columns make up a unique composite index
Because although the multiple-column combination of indexes is unique, such as Id+name is unique, no single column can have duplicate values
As long as Id+name is the only one.

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)
)
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

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 listed as NULL, the column is automatically changed to NOT NULL when the Primary key is added. The unique key does not have this requirement on the column
2, a table can have only one primary key, but there may be multiple unique key
3, the primary key and the unique key constraint is implemented through the reference index, if the inserted value is null, then according to the principle of the index, All null values are not recorded on the index, so when you insert 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); # cannot be repeated
insert into t (A, B) values (null,null); #可以重复

Four, use unique KEY
CREATE TABLE ' secure_vulnerability_warning ' (
' id ' int (ten) not NULL auto_increment,
' Date ' d Ate not NULL,
' type ' varchar (+) NOT NULL,
' sub_type ' varchar (+) NOT NULL,
' domain_name ' varchar (+) not N ULL,
' 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 (1 1) Default ' 1 ',
' first_set_ok_time ' datetime default NULL,
' last_set_ok_time ' datetime default NULL,
PRIMARY K EY (' id '),
unique KEY ' date ' (' Date ', ' hash ')
) engine=innodb DEFAULT Charset=utf8
Unique The purpose of key is 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 primary key and unique
A field that defines a unique constraint cannot contain duplicate values, and you can define a unique constraint for one or more fields. Therefore, UNIQUE can be defined at the field level or at the table level, and can contain null values on uniqued constrained fields. Oracle automatically establishes a unique index and a NOT NULL constraint for a field with the PRIMARY key constraint, which can be indexed when defining the PRIMARY key constraint;
Uniqued can be empty and can be defined in one or more fields in a table; PRIMARY key cannot be empty and cannot be duplicated, and a joint primary key can be defined in a table;
Simply put, primary key = unique + NOT NULL
There are many similarities between Primary key and unique key. But there are still the following differences:
One, the domain/domain Group as primary key cannot be null, and the unique key can.
There can be only one primary key in a table, and multiple unique keys may exist at the same time.
The larger difference is in the logical design. Primary key is generally used as a record identifier in logical design, which is the original intention of setting Primary key, and unique key is just to guarantee the uniqueness of the domain/domain group.
There are two constraints in Oracle's constraint, both of which are unique to the column ――unique and primary key, but there are differences:
1, unique key requires the column is unique, but does not include the null field, that is, the constrained column can be empty and only require that the value in the column is not repeated except null;
2. Primary Key also requires the column to be unique, while also restricting the value of the field cannot be null, which is equivalent to primary key=unique + NOT NULL.
Creating a primary key and a unique key will create a unique index accordingly.
PRIMARY KEY syntax: ALTER TABLE table name add constraint key Name primary key (columns);
Syntax for unique key: ALTER TABLE table name add constraint key name unique (columns);
A table can have only one primary key, but there may be many unique, and unique can be null values, such as the employee's phone number is usually unique, because the phone number is definitely the only, but some employees may not have a phone.
The primary key is certainly unique, but the only one is not necessarily the primary key;
Don't confuse unique indexes with unique constraints
1. Primary KEY = unique + NOT NULL
2, the only constraint and the primary key is the same category of constraints, and can be used as a reference to foreign keys, the difference is that a table can only have a single primary key
3. The creation of primary keys and unique constraints depends on the index, and Oracle automatically creates a unique index if there are no indexed indexes available when creating a primary key or UNIQUE constraint.

The difference between key, primary key, unique key and index in MySQL
The index is used to quickly find a row with a specific value on a column. Without an index, MySQL had to start with the first record and then read through the entire table until it found the relevant line.
The larger the table, the more time it takes. If the table has an index to the column of the query, MySQL can quickly reach a location to find the middle of the data file, and there is no need to consider all the data.
If a table has 1000 rows, this is at least 100 times times faster than sequential reads. Note that you need to access almost all 1000 rows, which are read in a faster order, because at this point we avoid disk pathfinding.

All MySQL indexes (PRIMARY, unique, and index) are stored in the B-tree. Strings are automatically compressed with prefix and end space.

Indexes are used to:

Quickly find the line that matches a WHERE clause;
When a junction is executed, rows are retrieved from other tables;
Find the Max () or min () value for a specific index column;
Sort or Group A table if sorting or grouping is done on the leftmost prefix of an available key (for example, order by key_part_1,key_part_2).

If all key values are partially followed by DESC, the key is read in reverse order.
In some cases, a query can be optimized to retrieve values without consulting the data file.

If all columns used for some tables are numeric and form the leftmost prefix of some keys, the values can be retrieved from the index tree for faster.

—————————————————————————————————————————————————————————————————————————————

The following is the statement that builds the table:

[SQL] View plain copy

CREATE TABLE ' Phpcolor_ad ' (
' ID ' mediumint (8) not NULL auto_increment,
' Name ' varchar (+) is not NULL,
' Type ' mediumint (1) Not NULL,
' Code ' text,
PRIMARY KEY (' id '),
KEY ' type ' (' type ')
);

One, key and primary key difference
[SQL] View plain copy
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.
[SQL] View plain copy
CREATE TABLE ' Admin_role ' (
' adminset_id ' varchar (+) not NULL,
' roleset_id ' varchar (+) not NULL,
PRIMARY KEY (' adminset_id ', ' roleset_id '),
KEY ' fk9fc63fa6daed032 ' (' adminset_id '),
KEY ' fk9fc63fa6c7b24c48 ' (' roleset_id ')
) Engine=innodb DEFAULT Charset=utf8;

Primary key, two columns combined, unique, built-in uniqueness index, and cannot be null
In addition, the two key definitions are equivalent to indexing the two columns separately.

InnoDB
Primary key primary key clustered index
Key General Index

The difference between index and key in MySQL
key is a key value, which 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.
While index is at the implementation level, such as the ability to index any column of a table, when the indexed column is in the Where condition in the SQL statement, fast data positioning can be obtained for quick retrieval.
As for the unique index, it is only one of the index, the creation of a unique index indicates that this column data is not repeatable, guess MySQL index of the 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.
An explanation of the search:
Note that "primary" was called primary KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity ...) The
INDEX is something at the physical level, helps improve access time for table operations.
Behind Every PK there is (usually) unique index created (automatically).

Third, what is the difference between a unique key and Primary key in MySQL
1,primary key 1 or more columns must be NOT NULL, and if the column is null, when the Primary key is incremented, the columns are automatically changed to NOT NULL.
The unique key does not have this requirement for the column
2, a table can have only one primary key, but there may be multiple unique key
3, the primary key and the unique key constraint is implemented through the reference index, if the inserted value is NULL,
According to the principle of the index, all null values are not recorded on the index, so when you insert 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); # cannot be repeated
insert into t (A, B) values (null,null); #可以重复

Iv. use of unique KEY
[SQL] View plain copy
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
[SQL] View plain copy

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:
[SQL] View plain copy
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:
[SQL] View plain copy
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:
[SQL] View plain copy
ALTER TABLE Persons
ADD CONSTRAINT Uc_personid UNIQUE (id_p,lastname)

3, REVOKE UNIQUE constraint
To revoke a UNIQUE constraint, use the following SQL:
[SQL] View plain copy
ALTER TABLE Persons
DROP INDEX Uc_personid

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