MySQL alter add to increase multiple field positions? Detailed explanation of the usage of modify column statement

MySQL alter add to increase multiple field positions? Detailed explanation of the usage of modify column statement

MySQL ALTER command

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

Before starting this tutorial, let's create a table named: testalter_tbl.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use chenweiliang;
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 with the DROP clause to drop the i column of the table created above:

mysql> ALTER TABLE testalter_tbl  DROP i;

DROP cannot be used to delete a field if there is only one field left in the data table.

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;

After executing the above 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 provided by MySQL (setPositioningfirst column), AFTER field name (set after a field).

Try the following ALTER TABLE statement, and after successful execution, use SHOW COLUMNS to see 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;

The 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 the MODIFY or CHANGE clause in the ALTER command.

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

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

With the CHANGE clause, the syntax is very different.Immediately after the CHANGE keyword is the name of the field 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;

Effect of ALTER TABLE on Null and Default Values

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

The following example specifies that 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 field default value

You can use ALTER to change the default value of a field, try the following examples:

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 with 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:

Modifying the data table type can be done using the ALTER command and the TYPE clause.Try the following example, where we change the type of the table testalter_tbl to MYISAM:

note:To view the data table type, you can use the SHOW TABLE STATUS statement.

mysql> ALTER TABLE testalter_tbl ENGINE = 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 table name

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

Try the following example 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 on MySQL tables, which we will cover in the next chapters.

alter other uses

Modify the storage engine: modify it to myisam

alter table tableName engine=myisam;

Remove foreign key constraint: keyName is a foreign key alias

alter table tableName drop foreign key keyName;

The relative position of the modified field: here name1 is the field you want to modify, type1 is the original type of the field, and you can choose one of first and after, which should be obvious, first is placed first, and after is placed after the name2 field

alter table tableName modify name1 type1 first|after name2;