MySQL Modify table/field Add/Remove Table index

MySQL Tutorial modify table/Field Add/Remove Table index

CREATE TABLE Test (Blob_col blob, index (Blob_col (10)); in MySQL 5.1, for MyISAM and InnoDB tables, the prefix can be up to 1000 bytes long. Note that the prefix limit should be measured in bytes, and the prefix length in the CREATE TABLE statement is interpreted as the number of characters. This must be considered when specifying the prefix length for columns that use multibyte character sets.

You can also create Fulltext indexes. The index can be used for Full-text search. Only the MyISAM storage engine supports the Fulltext index, and only the char, varchar, and text columns. The index is always on the entire column, and the local (prefix) index is not supported

Add index
Mysql> ALTER TABLE name add index index name (field name 1[, field Name 2 ...]);

Example: mysql> ALTER TABLE employee ADD index emp_name (name);
Index of the Primary keyword
Mysql> ALTER TABLE name add primary key (field name);
Example: mysql> ALTER TABLE employee ADD primary key (ID);
Index with unique restriction criteria
Mysql> ALTER TABLE name add unique index name (field name);
Example: mysql> ALTER TABLE employee add unique emp_name2 (cardnumber);
MySQL alter syntax usage: View index of a table
Mysql> Show index from table name;

Example: Mysql> show index from employee;

Delete an index
mysql> ALTER TABLE name DROP INDEX index name;

Example: Mysql>alter table employee DROP index emp_name;

Modify table: Add Field:mysql> ALTER TABLE table_name add Field_name field_type;
View Table:mysql> SELECT * FROM table_name;

Modify the original field name and type:mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;

Delete field: MySQL ALTER TABLE table_name drop FIELD_NAME;

Finally add a point

Multiple-column index
MySQL can create indexes for multiple columns. An index can include 15 columns. For some column types, you can index the prefix of the column (see Section 7.4.3, "column index").

A multiple-column index can be treated as an array of sorts that contain values created by connecting the values of indexed columns.

MySQL uses a multiple-column index in this way: When you specify a known number for the 1th column of the index in the WHERE clause, the query is quick, even if you do not specify a value for the other column.

Assume that the table has the following structure:

CREATE TABLE test (    ID int not null,    last_name char (a) not NULL,&NB sp;   first_name char () not null,    primary key (ID),    index name (last_ Name,first_name)); The name index is an index to last_name and first_name. Indexes can be used to query for last_name, or for last_name and first_name to specify values within a known range. Therefore, the name index is used for the following query:

Select * from test where last_name= ' Widenius '; select * from test    where last_ Name= ' Widenius ' and first_name= ' Michael '; SELECT * from test    where last_name= ' Widenius '     and (first_name= ' Michael ' or First_ Name= ' Monty '); SELECT * from test    where last_name= ' Widenius '     and first_name >= ' m ' and first_name < ' n '; however, the name index is not used for the following query:

Select * from Test where first_name= ' Michael '; Select * test    w Here last_name= ' Widenius ' or first_name= ' Michael ';