How to create and delete indexes in MySQL?

mysql index, mysql creates an index, mysql deletes an index

1. In a relational database, an index is a database structure related to a table, which enables the execution of SQL statements corresponding to the table Got faster. Today I will briefly demonstrate the creation, query and deletion of indexes in mysql.

2. First create a table casually, the SQL statement is as follows:

CREATE TABLE IF NOT EXISTS `student` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` varchar(64) NOT NULL DEFAULT '' COMMENT '姓名',
 `sex` tinyint(1) NOT NULL COMMENT '性别',
 `age` tinyint(2) NOT NULL COMMENT '年龄',
 `class` varchar(64) NOT NULL DEFAULT '' COMMENT '班级',
 PRIMARY KEY (`id`)
 ) ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='学生表';

3. You can see that in the SQL statement to create the table, a primary key index has been established. At this time, view the index in the table: SHOW INDEX FROM `student`, the result is as shown in the figure:

##4. Of course, we can also add other indexes based on , such as a unique index. Assuming that each student's name cannot be repeated, then you can add a unique index on the name field:

ALTER TABLE `student` ADD UNIQUE `stu_name` (`name`);

At this time, check the index in the table again, SHOW INDEX FROM `student`, The result is as shown in the figure:

##5. Then give Add a common index to the class:

ALTER TABLE `student` ADD INDEX `stu_class` (`class`);

View the index in the table, SHOW INDEX FROM `student`, the result is as shown in the figure:

6. Next is to delete the index, delete the unique index and ordinary index:

ALTER TABLE `student` DROP INDEX `stu_name`;
ALTER TABLE `student` DROP INDEX `stu_class`;

Then check the index in the table, SHOW INDEX FROM `student`, the result is as shown in the figure:

7. At this time, there is only one primary key index left If you delete it directly, an error will be reported:

ALTER TABLE `student` DROP PRIMARY KEY;

Reason: Because the id key associated with the primary key index is automatically growing;

8. You need to cancel the automatic growth of the id key first:

ALTER TABLE `student`  MODIFY `id`  int(10) NOT NULL COMMENT '学号'

Execute again:

 ALTER TABLE `student` DROP PRIMARY KEY;

View the index in the table, SHOW INDEX FROM `student`, in the table There is no index anymore

The above is the detailed content of How to create and delete indexes in MySQL?. For more information, please follow other related articles on the PHP Chinese website!