The MySQL ALTER command is used when we need to change the name of a table or change the field of a table.

To start this tutorial, let’s create a table called testalter_tbl.

root@host# mysql -u root -p password; Enter password:******* mysql> use cnblogs; 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 the set (0.00 SEC)Copy the code

Delete, add, or modify table fields

The following command uses the ALTER command and DROP clause to DROP the I field of the created table above:

mysql> ALTER TABLE testalter_tbl  DROP i;
Copy the code

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

Alter table testalter_tbl alter table testalter_tbl alter table testalter_tbl alter table testalter_tbl alter table testalter_tbl

mysql> ALTER TABLE testalter_tbl ADD i INT;
Copy the code

After executing the above command, the I field is automatically added to the end of the data table field.

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 the set (0.00 SEC)Copy the code

If you need to specify the position of a new field, you can use the MySQL keyword FIRST, AFTER field name (AFTER field name).

Try the following ALTER TABLE statement and, after successful execution, use SHOW COLUMNS to see how the TABLE structure has changed:

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;
Copy the code

The FIRST and AFTER keywords are only used in the ADD clause, so if you want to reset the position of a table field you need to DROP the field FIRST and then ADD the field and set the position using ADD.

Modify the field type and name

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

For example, to change the type of field C from CHAR(1) to CHAR(10), run the following command:

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

With the CHANGE clause, the syntax is quite different. The CHANGE keyword is followed by the name of the field you want to CHANGE, and then specify the type and name of the new field. Try the following example:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
Copy the code

If you now want to change field j=”” from =”” bigint=”” to =”” int, SQL statement as follows:

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
Copy the code

ALTER TABLE effect on Null and default values

When you modify a field, you can specify whether to include only or whether to set the default value.

In the following example, specify that field j is NOT NULL and the default value is 100.

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

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

Change the default value of a field

You can use ALTER to change 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 the set (0.00 SEC)Copy the code

You can also use the ALTER command and DROP clause to DROP 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 the set (0.00 SEC) Changing a Table Type:Copy the code

Changing the data table TYPE can be done using the ALTER command and the TYPE clause. Try the following example, where we change the type of the table testalter_tbl to MYISAM:

Note: To view the data TABLE type, use the SHOW TABLE STATUS statement.

mysql> ALTER TABLE testalter_tbl TYPE = 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: Check_time: NULL Create_options: Comment: 1 row in set (0.00 SEC)Copy the code

Modify the name of the table

If you need to change the name of a data TABLE, you can do so using the RENAME clause in the ALTER TABLE statement.

Try the following example to rename table testalter_tbl to ALTER_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
Copy the code

The ALTER command can also be used to create and drop indexes in MySQL tables, which we will cover next.