MySQL ALTER Command

When we need to modify the table name or modify the table fields, we need to use the MySQL ALTER command.

Before starting this chapter tutorial, let's create a table first, named: testalter_tbl.

root@host# mysql　-u root　-p password;
Enter password:*******
mysql> use w3codebox;
Database changed
mysql> create table testalter_tbl
　　　　-> (
　　　　-> i INT,
　　　　-> c CHAR(1)
　　　　-> );
Query OK, 0 rows affected (0.05　sec)
mysql>　SHOW　COLUMNS　FROM　testalter_tbl;
+-------+---------+------+-----+---------+-------+
|　Field　|　Type　　　　|　Null　|　Key　|　Default　|　Extra　|
+-------+---------+------+-----+---------+-------+
|　i　　　　　|　int(11)　|　YES　　|　　　　　|　NULL　　　　|　　　　　　　|
|　c　　　　　|　char(1)　|　YES　　|　　　　　|　NULL　　　　|　　　　　　　|
+-------+---------+------+-----+---------+-------+
2　rows in set (0.00 sec)

Delete, add, or modify table fields

The following command uses the ALTER command and the DROP clause to delete the i field created in the table above:

mysql> ALTER TABLE testalter_tbl DROP i;

If there is only one field left in the table, you cannot use DROP to delete the field.

In MySQL, the ADD clause is used to add columns to the table, as shown in the following example, adding the i field to the table testalter_tbl and defining the data type:

mysql> ALTER TABLE testalter_tbl ADD i INT;

After executing the above command, the i field will be automatically added to the end of the table fields.

mysql>　SHOW　COLUMNS　FROM　testalter_tbl;
+-------+---------+------+-----+---------+-------+
|　Field　|　Type　　　　|　Null　|　Key　|　Default　|　Extra　|
+-------+---------+------+-----+---------+-------+
|　c　　　　　|　char(1)　|　YES　　|　　　　　|　NULL　　　　|　　　　　　　|
|　i　　　　　|　int(11)　|　YES　　|　　　　　|　NULL　　　　|　　　　　　　|
+-------+---------+------+-----+---------+-------+
2　rows in set (0.00 sec)

If you need to specify the position of the new field, you can use the MySQL keywords FIRST (positioning as the first column) and AFTER field name (positioning after a certain field).

Try the following ALTER TABLE statement, and after the execution is successful, use SHOW COLUMNS to view the changes in the table structure:

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

The FIRST and AFTER keywords can be used in the ADD and MODIFY clauses, so if you want to reset the position of the table fields, you need to use DROP to delete the field first, then use ADD to add the field and set the position.

Modify field type and name

If you need to modify the field type and name, you can use the MODIFY or CHANGE clause in the ALTER command.

For example, change the type of field c from CHAR(1) to CHAR(10), you can execute the following command:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

The syntax is very different when using the CHANGE clause. After the CHANGE keyword, the field name you want to modify follows, followed by specifying the new field name and type. Try the following example:

mysql>　ALTER　TABLE　testalter_tbl　CHANGE　i　j　BIGINT;

If you want to change the field j from BIGINT to INT now, the SQL statement is as follows:

mysql>　ALTER　TABLE　testalter_tbl　CHANGE　j　j　INT;

ALTER TABLE's Impact on Null Values and Defaults

When modifying a field, you can specify whether to include a value or set a default value.

In the following example, specify the field j as NOT NULL with a default value of100 .

mysql>　ALTER　TABLE　testalter_tbl　
　　　　->　MODIFY　j　BIGINT　NOT　NULL　DEFAULT　100;

If you do not set a default value, MySQL will automatically set the field to NULL by default.

Modify field default value

You can use ALTER to modify the default value of a field, try the following example:

mysql>　ALTER　TABLE　testalter_tbl　ALTER　i　SET　DEFAULT　1000;
mysql>　SHOW　COLUMNS　FROM　testalter_tbl;
+-------+---------+------+-----+---------+-------+
|　Field　|　Type　　　　|　Null　|　Key　|　Default　|　Extra　|
+-------+---------+------+-----+---------+-------+
|　c　　　　　|　char(1)　|　YES　　|　　　　　|　NULL　　　　|　　　　　　　|
|　i　　　　　|　int(11)　|　YES　　|　　　　　|　1000　　　　|　　　　　　　|
+-------+---------+------+-----+---------+-------+
2　rows in set (0.00 sec)

You can also use the ALTER command with the DROP clause to remove the default value of a field, as shown in the following example:

mysql>　ALTER　TABLE　testalter_tbl　ALTER　i　DROP　DEFAULT;
mysql>　SHOW　COLUMNS　FROM　testalter_tbl;
+-------+---------+------+-----+---------+-------+
|　Field　|　Type　　　　|　Null　|　Key　|　Default　|　Extra　|
+-------+---------+------+-----+---------+-------+
|　c　　　　　|　char(1)　|　YES　　|　　　　　|　NULL　　　　|　　　　　　　|
|　i　　　　　|　int(11)　|　YES　　|　　　　　|　NULL　　　　|　　　　　　　|
+-------+---------+------+-----+---------+-------+
2　rows in set (0.00 sec)
Changing a Table Type:

To modify the type of a data table, you can use the ALTER command with the TYPE clause. Try the following example, where we change the type of the table testalter_tbl to MYISAM:

Note:You can use the SHOW TABLE STATUS statement to view the type of a data table.

mysql>　ALTER　TABLE　testalter_tbl　ENGINE　=　MYISAM;
mysql>　　SHOW　TABLE　STATUS　LIKE　'testalter_tbl'\G
***************************　1. row　****************
　　　　　　　　　　　Name: testalter_tbl
　　　　　　　　　　　Type: MyISAM
　　　　　Row_format: Fixed
　　　　　　　　　　　Rows: 0
　Avg_row_length: 0
　　　　Data_length: 0
Max_data_length:　25769803775
　　　Index_length:　1024
　　　　　　Data_free: 0
　Auto_increment: NULL
　　　　Create_time:　2007-06-03　08:04:36
　　　　Update_time:　2007-06-03　08:04:36
　　　　　Check_time: NULL
　Create_options:
　　　　　　　　Comment:
1　row in set (0.00 sec)

Modify Table Name

If you need to change the name of a data table, you can use the RENAME clause in the ALTER TABLE statement to achieve this.

Try the following example to rename the data table testalter_tbl to alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

The ALTER command can also be used to create and delete indexes on MySQL data tables, which we will introduce in the following chapters.