MySQL ALTER and mysqlalter

MySQL ALTER and mysqlalter

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

Before starting this chapter, let's 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)

  Delete, add, or modify table fields

The following command uses the ALTER command and the DROP clause to delete the I field of the table created above:

mysql> ALTER TABLE testalter_tbl  DROP i;

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

MySQL uses the ADD clause to ADD columns to the data table. The following example adds the I field to the testalter_tbl table and defines the data type:

mysql> ALTER TABLE testalter_tbl ADD i INT;

After the preceding command is executed, 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 (set the FIRST column) provided by MySQL, AFTER field name (set the location AFTER a field ).

Try the following alter table statement. After the statement is executed successfully, use show columns to view the TABLE structure changes:

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 the ADD clause. to reset the position of a data table field, you must FIRST Delete the field by using DROP and then ADD the field and set the position.

  Modify Field Type and name

To MODIFY the field type and name, you can use the MODIFY or CHANGE Clause in the ALTER command.

For example, to change the type of field c from CHAR (1) To CHAR (10), run the following command:

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

The syntax for using the CHANGE Clause is quite different. After the CHANGE keyword, it is 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 impact on Null and default values

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

In the following example, 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 automatically sets this field to NULL by default.

  Modify Field Default Value

You can use ALTER to modify the default value of a field and try the following instances:

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 delete the default values of fields, 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:

You can use the ALTER command and the TYPE clause to modify the data table TYPE. Try the following instance to change the testalter_tbl type 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: 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)

  Modify Table Name

To modify the name of a data TABLE, use the RENAME clause in the alter table statement.

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

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

The ALTER command can also be used to create and delete indexes of MySQL Data Tables. This function will be described in the following sections.

Address: http://www.manongjc.com/mysql/mysql_alter.html

Related reading:

• MySQL Index
• MySQL temporary table