1、在上面了解了Mysql常见的索引后,这里进行Mysql索引实例的创建.
2、主键索引
2.1 在创建表的时候,指定主键索引
create table table_primarykey
( id int primary key auto_increment ,
name varchar(20)
);
利用 show create table table_primarykey ; 来查看表的创建语句
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_primarykey | CREATE TABLE `table_primarykey` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
2.2先创建表,在增加主键索引
create table table_primarykey
( id int auto_increment ,
name varchar(20)
);
alter table table_primarykey
add primary key (id);
利用 show create table table_primarykey ; 来查看表的创建语句
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_primarykey | CREATE TABLE `table_primarykey` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
3、查看表上的索引
show indexes from table_primarykey;
执行的结果如下:
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table_primarykey | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+