MySQL ALTER command

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

Tutorial before the start of this chapter let us create a table, the table name: testalter_tbl.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use w3big;
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 a table field

Use the following commands ALTER and DROP clause delete command created above table i fields:

mysql> ALTER TABLE testalter_tbl  DROP i;

If the data in the table only remaining one field you can not use DROP to delete the field.

MySQL use the ADD clause to add columns want to datasheet, as examples of fields in the table add i testalter_tbl and define data types:

mysql> ALTER TABLE testalter_tbl ADD i INT;

After executing the above command, they fields are 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, (after setting located in a field) you can use MySQL provide keyword FIRST (set bit in the first column), AFTER field name.

Try the following ALTER TABLE statement, after the successful implementation, use SHOW COLUMNS view the change 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;

FIRST and AFTER keywords only for ADD clause, so if you want to reset the data table field position on the need to use DROP delete a field and then use ADD to add the field and set the position.

Modify the field name and type

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

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

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

Use CHANGE clause grammar are very different. After the CHANGE keyword, followed by the field name you want to modify, and then specify a new field name and type. Try the following examples:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

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

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)

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:

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)

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;