Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL indexing knowledge points detailed explanation", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL indexing knowledge points detailed explanation"!

General index

Create an index

This is the most basic index, and it has no restrictions. It can be created in the following ways:

CREATE INDEX indexName ON mytable (username (length))

If it is of type CHAR,VARCHAR, the length can be less than the actual length of the field; for types of BLOB and TEXT, length must be specified.

Modify table structure (add index)

ALTER mytable ADD INDEX [indexName] ON (username (length))

Specify directly when you create a table

CREATE TABLE mytable (ID INT NOT NULL,username VARCHAR (16) NOT NULL, INDEX [indexName] (username (length)

Syntax for deleting an index

DROP INDEX [indexName] ON mytable

Unique index

It is similar to the previous normal index, except that the value of the index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique. It can be created in the following ways:

Create an index

CREATE UNIQUE INDEX indexName ON mytable (username (length))

Modify table structure

ALTER table mytable ADD UNIQUE [indexName] (username (length))

Specify directly when you create a table

CREATE TABLE mytable (ID INT NOT NULL,username VARCHAR (16) NOT NULL, UNIQUE [indexName] (username (length)

Use the ALTER command to add and remove indexes

There are four ways to add an index to a data table:

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): this statement adds a primary key, which means that the index value must be unique and cannot be NULL.

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): the value that this statement creates an index must be unique (NULL may appear multiple times except for NULL).

ALTER TABLE tbl_name ADD INDEX index_name (column_list): add a normal index, where the index value can appear multiple times.

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): this statement specifies that the index is FULLTEXT for full-text indexing.

The following example adds an index to a table.

Mysql > ALTER TABLE testalter_tbl ADD INDEX (c)

You can also use the DROP clause in the ALTER command to delete the index. Try the following example to delete the index:

Mysql > ALTER TABLE testalter_tbl DROP INDEX c

Use the ALTER command to add and remove primary keys

A primary key can only work on one column. When adding a primary key index, you need to make sure that the primary key is not NOT NULL by default. Examples are as follows:

Mysql > ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;mysql > ALTER TABLE testalter_tbl ADD PRIMARY KEY (I)

You can also use the ALTER command to delete the primary key:

Mysql > ALTER TABLE testalter_tbl DROP PRIMARY KEY

You only need to specify PRIMARY KEY when deleting the specified index, but you must know the index name when deleting the index.

Display index information

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

Try the following example:

Mysql > SHOW INDEX FROM table_name;\ G. Thank you for your reading, the above is the content of "detailed explanation of MySQL index knowledge points". After the study of this article, I believe you have a deeper understanding of the problem of detailed interpretation of MySQL index knowledge points, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.