Detailed description of the use of the ALTER command in MySQL, mysqlalter

Detailed description of the use of the ALTER command in MySQL, mysqlalter

MySQL's ALTER command is very useful when you want to change the table name, table fields, or if you want to add or delete columns in an existing table.

Let's start to create a table named testalter_tbl:

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

Discard, add or reposition fields:

If you want to delete an existing column I from the MySQL table above, use the DROP clause together with the ALTER command as follows:

mysql> ALTER TABLE testalter_tbl DROP i;

If there is only one field left in the table, the DROP command does not work.

To ADD a column, use the column definition specified by "ADD. Is the following statement restored? Testalter_tbl of the column

mysql> ALTER TABLE testalter_tbl ADD i INT;

Testalter will contain the same two columns. When the table is created for the first time, it will not have the same structure. This is because the table ends when a new column is added to the table by default. Even if ioriginally is the first column of MYTBL, it is now the last one:

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)

To specify the position of a column in the table, you can use the first column of the first column, or ALTER COL_NAME to indicate that the new column should be placed in the rear COL_NAME. In the following alter table statement, after show columns is used, everyone has different effects:

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 operators can only be used with the ADD clause. This means that to reposition an existing column in a table, you must first delete it and add it to the new position.
Change the column definition or name:

To change the column definition, modify or change the terms, together with the ALTER command. For example, to change column c from CHAR (1) To CHAR (10), do this:

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

The CHANGE syntax is a bit different. After the keyword is changed, the name of the column to be changed, and then specify a new definition, including the new name. Try the following example:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

If you use BIGINT to convert the j field to int without changing the column name, the statement should be:

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Alter table, NULL and default attributes:

When you modify or modify a column, you can also specify whether the column can contain NULL values and what is its default value. In fact, if this is not done, MySQL will automatically assign the values to these attributes.

In the following example, the value of the not null column is 100 by default.

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

If the preceding command is not used, MySQL fills in the NULL values in all columns.
Change the column default value:

You can use the ALTER command to change the default value of any column. 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 delete the default constraint from any column by using the ALTER command together with the DROP clause.

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)

Change table Type:

You can use the ALTER command and the TYPE clause to modify the table TYPE. In the following example, change testalter_tbl to the InnoDB type.

To locate the current type of a TABLE, use the show table status statement.

mysql> ALTER TABLE testalter_tbl TYPE = InnoDB;mysql> SHOW TABLE STATUS LIKE 'testalter_tbl'\G*************************** 1. row ****************      Name: testalter_tbl      Type: InnoDB   Row_format: Fixed      Rows: 0 Avg_row_length: 0  Data_length: 0Max_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)

Rename a table:

To RENAME a TABLE, use the RENAME option in the alter table statement. Try the following example and rename testalter_tbl to alter_tbl.

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

You can use the ALTER command to create and delete indexes in MySQL files. In the next chapter, we will see this feature.