MySQL ALTER command
MySQL ALTER command
When we need to modify the data table name or modify the data table fields, we need to use MySQL ALTER Order.
Before starting this tutorial, let us first create a table named: testalter_tbl.
root@host# mysql -u root -p password; Enter password:******* mysql> use php; 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 delete The i field of the table created above:
mysql> ALTER TABLE testalter_tbl DROP i;If there is only one field left in the data table, DROP cannot be used to delete the field.
The ADD clause is used in MySQL to add columns to the data table. The following example adds the i field to the table testalter_tbl and defines the data type:
mysql> ALTER TABLE testalter_tbl ADD i INT;Execute the above After the command, the i field is automatically added to the end of the data 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 location of the new field, you can use the keyword FIRST (set in the first column) provided by MySQL, AFTER field name (set in a certain field after).
Try the following ALTER TABLE statement. After successful execution, 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;FIRST and AFTER keywords are only used in the ADD clause. So if you want to reset the position of a data table field, you need to first use DROP to delete the field and 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 MODIFY or CHANGE in the ALTER command clause.
For example, to 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);uses the CHANGE clause, the syntax is very different different. After the CHANGE keyword, follow the field name you want to modify, and 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's impact on Null values and default values
When you modify a field, you can specify whether to include only or whether to set a default value.
In the following example, the specified field j is NOT NULL and the default value is 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 by default.
Modify the default value of the field
You can use ALTER to modify the default value of the 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 and DROP clause to delete 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 data table type, you can use the ALTER command and TYPE clause to complete. Try the following example, we change the type of table testalter_tbl to MYISAM:
Note: You can use the SHOW TABLE STATUS statement to view the data 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)Modify the table name
If you need to modify the name of the data table, you can use RENAME in the ALTER TABLE statement clause to achieve.
Try the following example to rename the data table testalter_tbl to alter_tbl:
The ALTER command can also be used To create and delete indexes of MySQL data tables, we will introduce this function in the next chapter.
Recommended related video tutorials:
