MySQL index types and syntax

MySQL index types and syntax

To view all the indexes on a single table: Show index from table name;

To build an index: Alter Table name Add index Normal index/unique unique index/fulltext full-text index/primary key primary key index + column name

Delete index:ALTER TABLE name DROP INDEX name;

　　syntax:ALTER TABLE member DROP INDEX email;

Delete Note: To be deleted according to the index name Key_name;

1. Normal index: Index just to speed up the query

Create syntax: ALTER TABLE member ADD index Tel index name (tel);

Delete syntax: ALTER TABLE member DROP INDEX tel;

2. Unique index: Unique can not only speed up the query speed, while the values on the row can not be duplicated;

Create syntax: ALTER TABLE member add unique (email);

3. Primary KEY index: Primary key primary key cannot be duplicated;

Create syntax: ALTER TABLE member add primary key (ID); Do not add index name, directly add (column name), you can;

Delete syntax: ALTER TABLE member drop PRIMARY key;

　　　　Primary key index differs from unique index: The primary key must be unique, the unique index is not necessarily a primary key, a table can have only one primary key, but may have more than one unique index;

4. Full-text index: fulltext

Create syntax: ALTER TABLE member add fulltext ftext(Intro);

Delete syntax: ALTER TABLE member DROP INDEX tel;

The full-text index is suitable for indexing fields with data type text;

Formatted Output index: Show index from member \g;

Index_type:btree BTREE Two fork Tree index mode

MySQL index types and syntax