MySQL ALTER Command

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

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

root@host# mysql -u root -p password;
Enter password:*******
mysql> use tutorialpro;
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)

Drop, Add, or Modify Table Fields

The following command uses the ALTER command with the DROP clause to remove the i field from the table created above:

mysql> ALTER TABLE testalter_tbl  DROP i;

If the table has only one field, it cannot be used to drop the field with DROP.

To add a column to the table, use the ADD clause in the ALTER command. The following example adds the i field to the testalter_tbl table and defines its 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 (to set it as the first column) or AFTER field_name (to set it after a specific field).

Try the following ALTER TABLE statements, and after successful execution, use SHOW COLUMNS to see 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 with the ADD and MODIFY clauses. So, if you want to reset the position of the table fields, you need to first drop the field using DROP and then add it back using ADD with the specified 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, to change the type of the c field from CHAR(1) to CHAR(10), execute the following command:

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

The syntax is quite different when using the CHANGE clause. After the CHANGE keyword, specify the field name you want to modify, then specify the new field name and type. Try the following example:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE Effects on Null and Default Values

When modifying fields, you can specify whether they include values or set default values.

The following example specifies that the j field is NOT NULL and has a default value of 100:

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.

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 ALTER and 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 change the table type, use the ALTER command with the TYPE clause. The following example changes the type of the testalter_tbl table to MYISAM:

Note: You can view the table type using the SHOW TABLE STATUS statement.

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 the table, you can use the RENAME clause in the ALTER TABLE statement.

The following example renames the 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 tables, which will be covered in the next chapter.