I know how to use INDEX as in the following code. And I know how to use foreign key and primary key.
CREATE TABLE tasks (
task_id int unsigned NOT NULL AUTO_INCREMENT,
parent_id int unsigned NOT NULL DEFAULT 0,
task varchar(100) NOT NULL,
date_added timestamp NOT NULL,
date_completed timestamp NULL,
PRIMARY KEY ( task_id ),
INDEX parent ( parent_id )
)
However I found a code using KEY
instead of INDEX
as following.
CREATE TABLE orders (
order_id int unsigned NOT NULL AUTO_INCREMENT,
-- etc
KEY order_date ( order_date )
)
I could not find any explanation on the official MySQL page. Could anyone tell me what is the differences between KEY
and INDEX
?
The only difference I see is that when I use KEY ...
, I need to repeat the word, e.g. KEY order_date ( order_date )
.
5条答案
按热度按时间q3qa4bjr1#
There's no difference. They are synonyms, though
INDEX
should be preferred (asINDEX
is ISO SQL compliant, whileKEY
is a MySQL-specific, non-portable, extension).From the
CREATE TABLE
manual entry:KEY
is normally a synonym forINDEX
. The key attributePRIMARY KEY
can also be specified as justKEY
when given in a column definition. This was implemented for compatibility with other database systems.By "The key attribute
PRIMARY KEY
can also be specified as justKEY
when given in a column definition.", it means that these threeCREATE TABLE
statements below are equivalent and generate identicalTABLE
objects in the database:...while these 2 statements below (for
orders4
,orders5
) are equivalent with each other, but not with the 3 statements above, as hereKEY
andINDEX
are synonyms forINDEX
, not aPRIMARY KEY
:...as the
KEY ( order_id )
andINDEX ( order_id )
members do not define aPRIMARY KEY
, they only define a genericINDEX
object, which is nothing like aKEY
at all (as it does not uniquely identify a row).As can be seen by running
SHOW CREATE TABLE orders1...5
:| Table |
SHOW CREATE TABLE...
|| ------------ | ------------ |
|
orders1
|CREATE TABLE orders1 (
order_id int NOT NULL,
PRIMARY KEY ( order_id )
)
||
orders2
|CREATE TABLE orders2 (
order_id int NOT NULL,
PRIMARY KEY ( order_id )
)
||
orders3
|CREATE TABLE orders3 (
order_id int NOT NULL,
PRIMARY KEY ( order_id )
)
||
orders4
|CREATE TABLE orders4 (
order_id int NOT NULL,
KEY ( order_id )
)
||
orders5
|CREATE TABLE orders5 (
order_id int NOT NULL,
KEY ( order_id )
)
|fv2wmkja2#
下面是nice description的有关“区别”:
“MySQL还要求对每个键进行索引,这是MySQL特有的实现细节,以提高性能。”
olmpazwi3#
Keys are special fields that play very specific roles within a table, and the type of key determines its purpose within the table.
An index is a structure that RDBMS(database management system) provides to improve data processing. An index has nothing to do with a logical database structure.
SO...
Keys are logical structures you use to identify records within a table and indexes are physical structures you use to optimize data processing.
Source: Database Design for Mere Mortals
Author: Michael Hernandez
gudnpqoy4#
在“创建表”文档中,它被作为
INDEX
的同义词提及:MySQL 5.5 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.17 CREATE TABLE Syntax@Nos已经引用了该部分并链接了5.1的帮助。
与
PRIMARY KEY
创建主键和索引一样,KEY
只创建索引。waxmsbnn5#
A key is a set of columns or expressions on which we build an index.
Check on https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721