Of Stack

MySQL database ALTER command explanation

  • 2021-01-19 22:30:19
  • OfStack

MySQL is a relational database (Relational Database Management System). This so-called "relational" can be understood as the concept of "table". A relational database consists of one or several tables.

The MySQL command is used when you need to change a table name or change a table field.

To start this chapter, let's create a table called testalter_tbl. 


root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
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 the DROP clause to delete the i field from the table created above: 


mysql> ALTER TABLE testalter_tbl DROP i;

You cannot use DROP to drop a field if there is only 1 field left in the table.

MySQL uses the ADD clause to add a column to a 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;

When 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 FIRST keyword (set bit, column 1) provided by MySQL and the AFTER field name (set after a field).

Try the following ALTER TABLE statement and, after successful execution, use SHOW COLUMNS to see the change 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 only used in the ADD clause, so if you want to reset the position of a table field, you need to use DROP to delete the field and then use ADD to add the field and set the position.

Modify the field type and name

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

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


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

Using the CHANGE clause, the syntax is quite different. The CHANGE keyword is followed by the name of the field 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 The effect of TABLE on Null values and default values

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

The following example specifies that the 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 the default value, MySQL will automatically set the default value for this field to NULL.

Modify the field defaults

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

You can also use the ALTER command and the DROP clause to remove the default value of a 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:

The ALTER command and the TYPE clause can be used to modify the table type. Try the following example, where we change the type of table testalter_tbl to MYISAM:

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


mysql> ALTER TABLE testalter_tbl DROP i;
0

Modify the name of the table

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

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


mysql> ALTER TABLE testalter_tbl DROP i;
1

The ALTER command can also be used to create and drop indexes on MySQL tables, which is described in the next section.

The above is this site to introduce the MySQL database ALTER command to explain the relevant knowledge, I hope to help you, if you want to know more information, please pay attention to this site website!

Related articles: