MySQL ALTER command

MySQL ALTER command

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

Before we begin this chapter tutorial, let's create a table with the title: testalter_tbl.

root@host# mysql -u root -p password;
Enter password:
mysql> use W3CSCHOOL;
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 drop clause to remove the i field of the table created above:

mysql> ALTER TABLE testalter_tbl  DROP i;

If only one field remains in the data table, DROP cannot be used to delete the field.

MySQL uses the ADD clause to add columns to the data table, the following example adds an i field to Table testalter_tbl, and defines the data type:

mysql> ALTER TABLE testalter_tbl ADD i INT;

After you execute 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 set (0.00 sec)

If you need to specify the location of the new field, you can use the keyword FIRST provided by MySQL (set first column), AFTER field name (set after a field).

Try the following ALTER TABLE statement, which, after successful execution, uses SHOW COLUMNS to see 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 are used only for add clauses, so if you want to reset the location of a data table field, you need to delete the field with DROP and then use ADD to add the field and set the location.

Modify the 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, by changing the type of field c from CHAR(1) to CHAR (10), you can perform the following command:

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

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

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE's effect on Null values 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, the specified field j is NOT NULL and the default is 100.

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

If you don't set the default, MySQL automatically sets the field to NULL by default.

Modify the field default

You can use ALTER to modify the default value of a field and 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 and drop clause to remove the default value of the field, as 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:

Modifying the data table type can be done using alter commands and TYPE clauses. Try the following example, and we testalter_tbl type of table file to MYISAM:

Note: You can use the SHOW TABLE STATUS statement to view data table types.

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: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Modify the table name

If you need to modify the name of the data table, you can implement it using the RENAME clause in the ALTER TABLE statement.

Try the following instance to rename the testalter_tbl table to alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

The ALTER command can also be used to create and delete indexes for MySQL data tables, a feature we'll cover in the next section.