MySQL command modify table structure ALTER TABLE syntax

ALTER TABLE Syntax

ALTER [IGNORE] TABLE tbl_name alter_spec [, Alter_spec ...]

Alter_specification:
ADD [COLUMN] create_definition [i | After column_name]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (Index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (Index_col_name,...)
or ADD fulltext [index_name] (Index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name]
(Index_col_name,...) [Reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or change [COLUMN] Old_col_name create_definition
[I | After column_name]
or MODIFY [COLUMN] create_definition [A | After column_name]
or DROP [COLUMN] Col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [to] New_tbl_name
or ORDER by Col
or table_options
ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create or undo an index, change the type of an existing column, or rename a column or table itself. You can also change the annotation of the table and the type of the table. View Chapter 6.5.3 CREATE TABLE syntax.

If you use ALTER TABLE to change a column specification, but DESCRIBE Tbl_name shows that your column has not been modified, it is possible that the chapter 6.5.3.1 the implicit definition of the change description of the column, so that MySQL ignores your modifications. For example, if you try to change a VARCHAR column to CHAR, and if you include other variable-length columns in this table, MySQL will still use VARCHAR.

ALTER table works by creating a temporary deputy to the original table. Changes are performed on the replica, and the original table is deleted, and the temporary table is replaced. Doing so automatically shifts all changes to a new table that does not have any update failures. When ALTER table executes, the original table can be read by other clients. Updates and writes are delayed until the new table is ready.

Note that if you use ALTER table in addition to RENAME options, MySQL will always create a temporary table, even if the data does not really need to be replicated (as if you were changing a column name). We plan to fix it soon, but usually people don't execute ALTER table very often, so in our TODO, this amendment is not a rush. For MyISAM tables, you can set the variable myisam_sort_buffer_size and a little higher to speed up the rebuilding of the index (which is the slowest part of the rebuild process).

In order to use ALTER TABLE, you need alter, INSERT, and CREATE permissions on this table.

IGNORE is the extension of MySQL to ANSI SQL92. It controls how ALTER table works when duplicate values appear on unique keys in a new table. If the IGNORE is not specified, the copy is discarded and rolled back. If IGNORE is specified, only the first record row is used for the record row that repeats on the unique key, and the rest is deleted.

You can emit multiple ADD, alter, DROP, and change clauses in a single ALTER TABLE statement. This is the extension of MySQL to ANSI SQL92, which ANSI SQL92 allows only one clause in each ALTER TABLE statement.

Change col_name, Drop col_name, and drop INDEX are MySQL extensions to ANSI SQL92.

MODIFY is a Oracle extension to ALTER TABLE.
The optional word COLUMN is just a useless phrase that can be ignored.

If you use ALTER table Tbl_name RENAME to new_name and do not have any other options, MySQL will simply rename the file with the table Tbl_name. This does not require the creation of temporary tables. View Chapter 6.5.5 RENAME TABLE syntax.

The create_definition clause uses the same ADD and change syntax as the CREATE TABLE. Note that these syntax not only contain column types, but also include column names. View Chapter 6.5.3 CREATE TABLE syntax.

You can rename a column by using a change old_col_name create_definition clause. In order to do so, you must specify the old and new column names, and the current type of columns. For example, to rename an INTEGER column A to B, you must do this:
mysql> ALTER TABLE T1 change a b INTEGER;
If you want to change the type of a column rather than the column name, the changing syntax still needs to have two column names, even if they are the same. For example:
mysql> ALTER TABLE T1 change b b BIGINT not NULL;
Then, when you go to MySQL 3.22.16a, you can also use MODIFY to change the type of a column without having to rename it:
mysql> ALTER TABLE T1 MODIFY b BIGINT not NULL;
If you shorten a column with change or MODIFY, and there is an index on the column that takes a partial value (for example, if you have an index on the first 10 characters of a VARCHAR column), then you will not be able to make the column shorter than the number of characters indexed.

When you change a column type using changes or MODIFY, MySQL tries to convert the data to the new type as much as possible.

In MySQL 3.22 or newer versions, you can use the one or ADD ... After Col_name adds a column to a specific location in a table. The default is to add to the last column. Starting with MySQL 4.0.1, you can also use the keyword first and after in change or MODIFY.

ALTER column can specify a new default value for a column or delete old default values. If the old default is removed and the column can be set to NULL, the new default value will be null. If the column does not allow null values, MySQL assigns the column a default value as described in the chapter 6.5.3 CREATE TABLE syntax.

DROP index removes an index. This is an extension of MySQL to ANSI SQL92. View Chapter 6.5.8 DROP INDEX syntax.

If the column is removed from a table, the column is also removed from any index that has it as part of it. If all the columns that make up an index are removed, the index is also removed.

If a table contains only one column, the column cannot be removed. If you intend to remove the table, use DROP table instead.

The DROP PRIMARY key removes the primary index. If such an index does not exist, it will remove the first UNIQUE index in the table. (If no PRIMARY key is explicitly specified, MySQL marks the first unique key as PRIMARY key) If you add a unique index or PRIMARY key to a table, it will be stored in any non-UNIQUE index Before, thus, MySQL can detect duplicate keys as much as possible.

The order by allows you to create a new table in the specified line of records. Note that the table will not retain this order after insertions and deletions. In some cases, if the table is ordered on the column you want to sort later, it will make MySQL sort more easy. This is useful when you know that the rows of your main query are in a certain order. By using this option, you may get a higher performance after a large change to the table.

If you use ALTER table on a MyISAM table, all non unique indexes will be created in one batch (just like REPAIR). This may make ALTER TABLE a little quicker when you have many indexes.

Starting with MySQL 4.0, the above features can be explicitly activated. ALTER TABLE ... The DISABLE KEYS enable MySQL to stop updating the MyISAM table's non unique index. Then ALTER TABLE ... The ENABLE KEYS can be used to reconstruct lost indexes. Because MySQL executes it with a special algorithm, this will be much faster than inserting the index one after another, disabling the key can be a large number of programs to speed up a large batch of inserts.

Using the C API function Mysql_info (), you can find out how many records are copied and how many records (when IGNORE is used) are deleted because of the unique key value duplicates.

FOREIGN key, CHECK, and REFERENCES clauses do nothing, except for tables of InnoDB type, it supports ADD CONSTRAINT FOREIGN key (...) REFERENCES ... (...)。 Note that InnoDB does not allow a index_name to be specified. View Chapter 7.5 InnoDB tables. For other types of tables, this syntax is provided only for compatibility, making it easier to migrate code from other SQL servers and run more easily to refer to the application that created the table. View Chapter 1.8.4 MySQL differs from ANSI SQL92.
Here is an example that shows some of the uses of ALTER TABLE. We begin by creating a table T1 as follows:

mysql> CREATE TABLE T1 (a integer,b CHAR (10));
To rename the table T1 to T2:

mysql> ALTER TABLE t1 RENAME T2;
To change column A from INTEGER to TINYINT not NULL (the column name does not change), change column B from char (10) to char (20), and also rename B to C:

mysql> ALTER TABLE T2 MODIFY a TINYINT not NULL and change B C CHAR (20);
Add a TIMESTAMP C column named D:

mysql> ALTER TABLE T2 ADD D TIMESTAMP;
Add an index to column D and set column A as the primary key:

mysql> ALTER TABLE T2 add INDEX (d), add PRIMARY KEY (a);
Remove column C:

mysql> ALTER TABLE T2 DROP COLUMN C;
Add a auto_increment integer column named C:

mysql> ALTER TABLE T2 ADD C INT UNSIGNED not NULL auto_increment,
ADD INDEX (c);
Note that we index C because the auto_increment column must be indexed, and we declare that column C is not NULL because the indexed column cannot have null.

When you add a auto_increment column, the column values are automatically populated with the sequence values. You can set the first sequence number by executing the Set insert_id=# before ALTER table or by using the Auto_increment = # table option. View Chapter 5.5.6 SET syntax.

For MyISAM tables, if you do not change the auto_increment column, the sequence values will not be affected. If you remove a auto_increment column and add another auto_increment column, the value will start again from 1.