Shulou(Shulou.com)06/01 Report--

This article is about how to add an index to mysql. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

First, use CREATE INDEX statements

You can create an index on an existing table using a CREATE INDEX statement specifically designed to create an index, but this statement cannot create a primary key.

CREATE INDEX [] ON ([] [ASC | DESC])

The syntax is as follows:

●: specifies the index name. Multiple indexes can be created in a table, but each index has a unique name in the table.

●: specifies the name of the table to be indexed.

●: specifies the column name to create the index. You can usually consider the columns that often appear in the JOIN clause and WHERE clause in the query statement as index columns.

●: optional. Specifies that the index is created using the length characters before the column. Creating an index using part of a column helps to reduce the size of the index file and save the space occupied by the index column. In some cases, only column prefixes can be indexed. The length of an index column has a maximum limit of 255bytes (the maximum limit for MyISAM and InnoDB tables is 1000 bytes). If the length of an index column exceeds this limit, it can only be indexed with the prefix of the column. In addition, columns of type BLOB or TEXT must also be indexed with a prefix.

● ASC | DESC: optional. ASC specifies that the indexes are sorted in ascending order, and DESC specifies that the indexes are arranged in descending order. The default is ASC.

Second, use CREATE TABLE statements

The index can be created at the same time as the table (CREATE TABLE) is created, in grammatical format:

1. Create a primary key index

CONSTRAINT PRIMARY KEY [index type] (,...)

When you use a CREATE TABLE statement to define column options, you can create a primary key by adding PRIMARY KEY directly after a column definition. When the primary key is a multi-column index composed of multiple columns, this method cannot be used, but can only be used to add a PRIMARY KRY (,...) at the end of the statement. Clause in the way it is implemented.

2. Create a general index

KEY | INDEX [] [] (, …)

3. Create a uniqueness index

UNIQUE [INDEX | KEY] [] [] (,...)

4. Create a foreign key index

FOREIGN KEY

Example 1: create a table tb_stu_info and create a general index in the table's height field.

Mysql > CREATE TABLE tb_stu_info-> (- > id INT NOT NULL,-> name CHAR (45) DEFAULT NULL,-> dept_id INT DEFAULT NULL,-> age INT DEFAULT NULL,-> height INT DEFAULT NULL,-> INDEX (height)->)

Example 2: create a table tb_stu_info2 and use the UNIQUE keyword to create a unique index on the id field of the table.

Mysql > CREATE TABLE tb_stu_info2-> (- > id INT NOT NULL,-> name CHAR (45) DEFAULT NULL,-> dept_id INT DEFAULT NULL,-> age INT DEFAULT NULL,-> height INT DEFAULT NULL,-> UNIQUE INDEX (id)->)

Third, use ALTER TABLE statements

You can add an index to an existing table while using the ALTER TABLE statement to modify the table. To do this, add one or more of the following grammatical elements to the ALTER TABLE statement.

1. Create a primary key index

ADD PRIMARY KEY [] (,...)

2. Create a general index

ADD INDEX [] [] (,...)

3. Create a uniqueness index

ADD UNIQUE [INDEX | KEY] [] [] (,...)

4. Create a foreign key index

ADD FOREIGN KEY [] (,...)

Example 1: after creating a table tb_stu_info3, use the UNIQUE keyword to create a unique index on the id field of the table.

Mysql > CREATE TABLE tb_stu_info3-> (- > id INT NOT NULL,-> name CHAR (45) DEFAULT NULL,-> dept_id INT DEFAULT NULL,-> age INT DEFAULT NULL,-> height INT DEFAULT NULL,->); Query OK,0 rows affected (0.40 sec) mysql > ALTER TABLE tb_stu_info3 ADD UNIQUE (id)

Fourth, display index information

To use the SHOW INDEX command to list the relevant index information in the table. You can format the output information by adding\ G.

Example:

Mysql > SHOW CREATE TABLE tb_stu_info\ gateway * 1. Row * * Table: tb_stu_infoCreate Table: CREATE TABLE `tb_stu_ info` (`id`int (11) NOT NULL, `name` char (45) DEFAULT NULL, `dept_ id` int (11) DEFAULT NULL, `age` int (11) DEFAULT NULL `height` int (11) DEFAULT NULL, KEY `height` (`height`) ENGINE=InnoDB DEFAULT CHARSET=gb23121 row in set (0.01sec) Thank you for reading! On the mysql to add the index method to share here, I hope that the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.