MySQL in a Nutshell [51]
Add an index (see the ALTER TABLE: ADD clause for standard indexes,” ALTER TABLE: ADD clause for FULLTEXT indexes,” and ALTER TABLE: ADD clause for SPATIAL indexes” subsections)
Add a foreign key constraint (see the ALTER TABLE: ADD clauses for foreign keys” subsection)
Change an existing column (see the ALTER TABLE: CHANGE clauses” subsection)
Delete a column or index (see the ALTER TABLE: DROP column clause” and ALTER TABLE: DROP index clauses” subsections)
Set other column and index factors (see the ALTER TABLE: Miscellaneous clauses” subsection)
Add and change table partitions (see the ALTER TABLE: Partition altering clauses” and ALTER TABLE: Partition administration clauses” subsections)
Set table-wide options (see the ALTER TABLE: Table options” subsection)
The IGNORE flag applies to all clauses and instructs MySQL to ignore any error messages regarding duplicate rows that may occur as a result of a column change. It will keep the first unique row found and drop any duplicate rows. Otherwise, the statement will be terminated and changes will be rolled back.
This statement requires the ALTER, CREATE, and INSERT privileges for the table being altered, at a minimum. While an ALTER TABLE statement is being executed, users will be able to read the table, but usually they won’t be able to modify data or add data to a table being altered. Any INSERT statements using the DELAYED parameter that are not completed when a table is altered will be canceled and the data lost. Increasing the size of the myisam_sort_buffer_size system variable will sometimes make MyISAM table alterations go faster.
The syntax and explanation of each clause follows, with examples, grouped by type of clause. Multiple alterations may be combined in a single ALTER TABLE statement. They must be separated by commas and each clause must include the minimally required elements.
ALTER TABLE: ADD clauses for columns
ALTER [IGNORE] TABLE table
ADD [COLUMN] column definition [FIRST|AFTER column] |
ADD [COLUMN] (column definition,...)
These clauses add columns to a table. The same column definitions found in a CREATE TABLE statement are used in this statement. Basically, the statements list the name of the column followed by the column data type and the default value or other relevant components. The COLUMN keyword is optional and has no effect.
By default, an added column is appended to the end of the table. To insert a new column at the beginning of a table, use the FIRST keyword at the end of the ADD COLUMN clause. To insert it after a particular existing column, use the AFTER keyword followed by the name of the column after which the new column is to be inserted:
ALTER TABLE workreq
ADD COLUMN req_type CHAR(4) AFTER req_date,
ADD COLUMN priority CHAR(4) AFTER req_date;
In this example, two columns are added after the existing req_date column. The clauses are executed in the order that they are given. Therefore, req_type is placed after req_date. Then priority is added after req_date and before req_type. Notice that you can give more than one clause in one ALTER TABLE statement; just separate them with commas.
ALTER TABLE: ADD clause for standard indexes
ALTER [IGNORE] TABLE table
ADD {INDEX|KEY} [index] [USING index_type] (column,...)
Use the ADD INDEX clause to add an index to a table. If you omit the name of the index, MySQL will set it to the name of the first column on which the index is based. The type of index may be stated, but usually it’s not necessary. The names of one or more columns for indexing must be given within parentheses, separated by commas.
Here is an example of how you can add an index using the ALTER TABLE statement, followed by the SHOW INDEXES statement with the results:
ALTER TABLE clients
ADD INDEX client_index
(client_name(10), city(5)) USING BTREE;
SHOW INDEXES FROM clients \G
*************************** 1. row ***************************
Table: clients
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: