一、前言
根据前一篇博客,知道了mysql中index和key在理论上的区别,但是总是很迷糊。现在不管理论里,在实践中看看吧。
二、代码实践
新建一张User表,包含字段id, name。
(1)第一种情况:
mysql> create table user(id int, name varchar(50), age int, primary key(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table user;
+-------+------------------------------------
------------------------------------------+
| Table | Create Table
|
+-------+------------------------------------
------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) NOT NULL default '0',
`name` varchar(50) default NULL,
`age` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
mysql> show keys from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
(2)第二种情况:
mysql> create table user(id int, name varchar(50), age int, key(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table user;
+-------+------------------------------
------------------------------+
| Table | Create Table
|
+-------+------------------------------
------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) default NULL,
`name` varchar(50) default NULL,
`age` int(11) default NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 1 | id | 1 | id | A | 0 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
mysql> show keys from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 1 | id | 1 | id | A | 0 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
(3)第三种情况:
mysql> create table user(id int, name varchar(50), age int, index(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table user;
+-------+-----------------------------
------------------------------+
| Table | Create Table
|
+-------+-----------------------------
------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) default NULL,
`name` varchar(50) default NULL,
`age` int(11) default NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 1 | id | 1 | id | A | 0 | NULL | NULL |YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
mysql> show keys from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 1 | id | 1 | id | A | 0 | NULL | NULL |YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
三、总结
(1)当建表时,建立primary key的键,同时默认建立对应的index
(2)当建表时,指定某列为key时,那么同时为该键建立index,key和index对应的键允许null
(3)当建表时,指定某列为index时,那么同时为该键建立key,index和key对应的键允许null。从建表语句中可以看出key ‘id’ (id),等价于(2)中的情况。
根据(1)(2)(3)说明在以上的使用情况中,index和key没有什么区别。