Mysql command to modify the alter table syntax

Alter table syntax

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

Alter_specification:
ADD [COLUMN] create_definition [FIRST | 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
[FIRST | AFTER column_name]
Or MODIFY [COLUMN] create_definition [FIRST | 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 revoke indexes, change the existing column type, or rename the column or table itself. You can also change the table comment and table type. View section 6.5.3 create table syntax.

If you use alter table to change a column definition, but DESCRIBE tbl_name shows that your column has not been modified, this may be because of the description of the implicit column definition change in section 6.5.3.1, make MySQL ignore your modifications. For example, if you try to change a VARCHAR column to CHAR, and if the table contains other variable-length columns, MySQL will still use VARCHAR.

Alter table works by creating a temporary copy of the original TABLE. The change is executed on the copy, the original table is deleted, and the temporary table is renamed. In this way, all modifications are automatically redirected to a new table without any update failures. When alter table is executed, the original TABLE can be read by other clients. The update and write operations are delayed until the new table is ready.

Note: If you use alter table with other options except RENAME, MySQL will always create a temporary TABLE, even if the data does not really need to be copied (just like when you change a column name ). We plan to fix it soon, but people usually do not execute alter table frequently, so on our TODO, this correction is not eager to deal. For the MyISAM table, you can set the variable myisam_sort_buffer_size to a higher value to accelerate the rebuilding of the index (this is the slowest part in the rebuilding process ).

To use alter table, you must have the ALTER, INSERT, and CREATE permissions on the TABLE.

IGNORE is an extension of ANSI SQL92 by MySQL. It controls how alter table works when duplicate values appear on the unique key of the new TABLE. If IGNORE is not specified, the copy will be abandoned and rolled back. If IGNORE is specified, only the first record row with repeated records on the unique key is used, and all others are deleted.

You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single alter table statement. This is an extension of ANSI SQL92 by MySQL. ANSI SQL92 only allows one clause in each alter table statement.

CHANGE col_name, DROP col_name, and drop index are MySQL extensions to ANSI SQL92.

MODIFY is an Oracle extension to alter table.
The optional word COLUMN is just a useless phrase and can be ignored.

If you use alter table tbl_name rename to new_name without any other options, MySQL will simply RENAME the file with TABLE tbl_name. You do not need to create a temporary table. View section 6.5.5 rename table syntax.

The create_definition clause uses the same ADD and CHANGE syntaxes as the create table clause. Note that these syntaxes not only contain the column type, but also the column name. View section 6.5.3 create table syntax.

You can use a CHANGE old_col_name create_definition clause to rename a column. To do this, you must specify the old and new column names and the current column type. For example, to rename an INTEGER column a to B, you must do the following:
Mysql> alter table t1 CHANGE a B INTEGER;
If you want to CHANGE the type of a column rather than the column name, the CHANGE syntax still requires two column names, even if they are the same. For example:
Mysql> alter table t1 CHANGE B BIGINT NOT NULL;
Then, when MySQL 3.22.16a is used, you can use MODIFY to change the type of a column without renaming it:
Mysql> alter table t1 MODIFY B BIGINT NOT NULL;
If you use CHANGE or MODIFY to shorten a column, and the column has an index that takes the column part value (for example, if you have an index on the first 10 characters of a VARCHAR column), you cannot make the column shorter than the number of characters in the index.

When you use CHANGE or MODIFY to CHANGE the column type, MySQL tries to convert the data to the new type as much as possible.

In MySQL 3.22 or later versions, you can use FIRST or ADD... AFTER col_name to ADD a column to a specific position in a table. By default, it is added to the last column. From MySQL 4.0.1, you can also use the FIRST and AFTER keywords in CHANGE or MODIFY.

Alter column can specify a new default value for a COLUMN or delete the old default value. If the old default value is removed and the column can be set to NULL, the new default value is NULL. If this column does not allow NULL values, MySQL assigns this column a default value as described in section 6.5.3 create table syntax.

Drop index removes an INDEX. This is an extension of ANSI SQL92 by MySQL. See section 6.5.8 drop index syntax.

If a column is removed from a table, the column will also be removed from any index with its components. If all 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.

Drop primary key to remove 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 specified, MySQL marks the first unique key as the primary key.) If you add a unique index or primary key to a table, it will be stored before any non-UNIQUE index, so MySQL can detect duplicate keys as much as possible.

Order by allows you to create a new table in the ORDER of specified record rows. Note that this order is not retained after the table is inserted or deleted. In some cases, if the columns in the table you want to sort in the future are ordered, this makes MySQL sorting easier. This is useful when you know that the rows you mainly query are in a definite order. When you make too many changes to a table, you may get higher performance by using this option.

If you use alter table on a MyISAM TABLE, all non-unique indexes will be created in batches (just like REPAIR ). When you have many indexes, this may make alter table faster.

From MySQL 4.0, the above features can be explicitly activated. Alter table... disable keys: Stop MySQL from updating non-unique indexes of the MyISAM TABLE. Then alter table... enable keys can be used to reconstruct the lost index. Because MySQL executes it with a special algorithm, it is much faster than inserting an index one by one. disabling a key can accelerate a large number of inserts in a program.

Using the c api function mysql_info (), you can find out how many records are copied and how many records are deleted due to repeated unique key values (when IGNORE is used.

The foreign key, CHECK, and REFERENCES clauses do not actually do anything, except for InnoDB tables, it supports add constraint foreign key (...) REFERENCES ...(...). Note that InnoDB does not allow an index_name to be specified. View section 7.5 InnoDB table. For other types of tables, this syntax is provided only for compatibility, making it easier to transplant code from other SQL servers and to run applications that reference tables. See section 1.8.4 difference between MySQL and ANSI SQL92.
Here is an example showing some usage of alter table. Create a table t1 as follows:

Mysql> create table t1 (a INTEGER, B CHAR (10 ));
To rename table t1 to t2:

Mysql> alter table t1 RENAME t2;
To change column a from INTEGER to tinyint not null (the column name remains unchanged), change column B from CHAR (10) To CHAR (20), and rename Column B to c:

Mysql> alter table t2 MODIFY a tinyint not null, CHANGE B c CHAR (20 );
Add a TIMESTAMP c column named d:

Mysql> alter table t2 ADD d TIMESTAMP;
Add an index on column d and set column a as the primary key:

Mysql> alter table t2 add index (d), add primary key ();
Remove column c:

Mysql> alter table t2 drop column c;
Add an AUTO_INCREMENT integer column named c:

Mysql> alter table t2 ADD c int unsigned not null AUTO_INCREMENT,
Add index (c );
Note that we have indexed c because the AUTO_INCREMENT column must be indexed. Similarly, we declare that column c is not null because the indexed Column cannot have NULL.

When you add an AUTO_INCREMENT column, the column values are automatically filled with sequence values. You can SET the first sequence number by executing SET INSERT_ID = # Before alter table or 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 value will not be affected. If you remove an AUTO_INCREMENT column and add another AUTO_INCREMENT column, the value starts from 1 again.