mysql8学习笔记11--create index

• Create index语句用来在表中创建索引

• 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代表创建索引的类型

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>