mysql8学习笔记11--create index
• Create index语句用来在表中创建索引
![](https://img2020.cnblogs.com/blog/947742/202007/947742-20200726084148707-576833634.png)
• Index_col_name可以包含一个字段,也可以包含多个字段(逗号隔开),如果包含多个字段,则表明此索引是复合索引
• Unique index代表索引中的值不能有重复
• Fulltext index只能创建在innodb和myisam存储引擎的char,varchar和text字段上
• Index可以创建在包含NULL值的字段上
• Key_block_size=value是在myisam存储引擎的表上指定索引键的block大小
• create index idx_st_sname on students(sname);##创建普通索引
• create index idx_st_union on students(sname,sex);##创建复合索引
• create unique index idx_st_sid on students(sid);##创建唯一索引
• mysql> insert into students values(1,‘eee’,0);##插入重复数据失败
• ERROR 1062 (23000): Duplicate entry '1' for key 'idx_st_sid'
• Index_type代表创建索引的类型
![](https://img2020.cnblogs.com/blog/947742/202007/947742-20200726084419049-128792123.png)
mysql> select * from orders_temp; +-----------+---------------------+---------+----+------------+ | order_num | order_date | cust_id | id | order_num2 | +-----------+---------------------+---------+----+------------+ | 20007 | 2005-09-30 00:00:00 | 10004 | 1 | NULL | | 20008 | 2005-10-03 00:00:00 | 10005 | 2 | NULL | | 20009 | 2005-10-08 00:00:00 | 10001 | 3 | NULL | +-----------+---------------------+---------+----+------------+ 3 rows in set (0.00 sec) mysql> show create table orders_temp; +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | orders_temp | CREATE TABLE `orders_temp` ( `order_num` int(11) NOT NULL DEFAULT '0', `order_date` datetime NOT NULL, `cust_id` int(11) NOT NULL, `id` int(10) NOT NULL AUTO_INCREMENT, `order_num2` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> create index order_num_index on orders_temp(order_num); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table orders_temp; +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | orders_temp | CREATE TABLE `orders_temp` ( `order_num` int(11) NOT NULL DEFAULT '0', `order_date` datetime NOT NULL, `cust_id` int(11) NOT NULL, `id` int(10) NOT NULL AUTO_INCREMENT, `order_num2` int(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `order_num_index` (`order_num`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> create index order_date_index on orders_temp(order_date); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table orders_temp; +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | orders_temp | CREATE TABLE `orders_temp` ( `order_num` int(11) NOT NULL DEFAULT '0', `order_date` datetime NOT NULL, `cust_id` int(11) NOT NULL, `id` int(10) NOT NULL AUTO_INCREMENT, `order_num2` int(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `order_num_index` (`order_num`), KEY `order_date_index` (`order_date`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> create index ordere_index_union on orders_temp(order_num,order_date); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table orders_temp; +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | orders_temp | CREATE TABLE `orders_temp` ( `order_num` int(11) NOT NULL DEFAULT '0', `order_date` datetime NOT NULL, `cust_id` int(11) NOT NULL, `id` int(10) NOT NULL AUTO_INCREMENT, `order_num2` int(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `order_num_index` (`order_num`), KEY `order_date_index` (`order_date`), KEY `ordere_index_union` (`order_num`,`order_date`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> create unique index cust_id_index on orders_temp(cust_id); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table orders_temp; +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | orders_temp | CREATE TABLE `orders_temp` ( `order_num` int(11) NOT NULL DEFAULT '0', `order_date` datetime NOT NULL, `cust_id` int(11) NOT NULL, `id` int(10) NOT NULL AUTO_INCREMENT, `order_num2` int(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cust_id_index` (`cust_id`), KEY `order_num_index` (`order_num`), KEY `order_date_index` (`order_date`), KEY `ordere_index_union` (`order_num`,`order_date`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>