How to create an index in mysql?

Mysql method of creating an index: 1. Use the CREATE TABLE statement with the INDEX or UNIQUE keyword to directly create the index when creating the table; 2. Use the ALTER TABLE statement to directly create the index; 3. Use the CREATE INDEX statement Add a normal index or UNIQUE index to the table.

How to create an index in mysql?

1. ALTER TABLE

ALTER TABLE is used to create a normal index, UNIQUE index or PRIMARY KEY index.

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Table_name is the name of the table to be indexed, column_list indicates which columns to index, and when there are multiple columns, separate them with commas. The index name index_name is optional. By default, MySQL will assign a name based on the first index column. Additionally, ALTER TABLE allows multiple tables to be altered in a single statement, so multiple indexes can be created at the same time.

2. CREATE INDEX

CREATE INDEX can add ordinary indexes or UNIQUE indexes to the table.

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name, index_name and column_list have the same meaning as in the ALTER TABLE statement, and the index name is not optional. In addition, you cannot use the CREATE INDEX statement to create a PRIMARY KEY index.

3. CREATE TABLE

Specify

//普通索引
CREATE TABLE mytable(  
 ID INT NOT NULL,   
 username VARCHAR(16) NOT NULL,  
 INDEX [indexName] (username(length))  
 );  
 
//唯一索引
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);

directly when creating the table

The above is the detailed content of How to create an index in mysql?. For more information, please follow other related articles on the PHP Chinese website!