Mysql普通索引创建实例 |
您所在的位置:网站首页 › 创建索引的sql语句实例 › Mysql普通索引创建实例 |
1、前面讲到了Mysql主键索引的创建,下面将介绍Mysql普通索引创建的实例 2、Mysql普通索引创建的实例 2.1 在创建表的时候,指定普通索引 create table table_index ( id int primary key auto_increment , name varchar(20) , index index_name (name) ); 利用 下面的sql语句,来查看表的创建语句 show create table table_index; +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table_index | CREATE TABLE `table_index` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------------+--------------------------------------------利用下面的sql语句,来查看表的索引信息 show index from table_index;+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | table_index | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | table_index | 1 | index_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2.2 先创建表的时候没有普通索引,然后利用alter table 或者 create index 添加索引 create table table_index ( id int primary key auto_increment , name varchar(20) ); alter table table_index add index index_name (name);或者 create table table_index ( id int primary key auto_increment , name varchar(20) ); create index index_name On table_index( name );利用sql语句,来查看表的索引信息 show index from table_index; +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | table_index | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | table_index | 1 | index_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+---
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |