MySQL ALTER command in detail

ALTER allows you to modify the structure of an existing table. For example, you can add or remove columns, create or remove indexes, change the type of an existing column, or rename the column or the table itself. You can also change table comments and table types.
You can rename a column using the CHANGE old_col_name create_definition clause. To do this, specify the old and new column names and the type that the column currently has. For example, to rename 1 INTEGER column, from a to b, you could do this:
mysql > ALTER TABLE t1 CHANGE a b INTEGER;
If you want to change the type of a column instead of its name, CHANGE syntax still requires two column names, even if they are 1-like. Such as:
mysql > ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
However, in MySQL3.22.16a, you can also use MODIFY to change the type of a column instead of renaming it:
mysql > ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
If you use CHANGE or MODIFY to shorten a column, and one index exists in the part of that column (for example, if you have an index of the first 10 characters of an VARCHAR column), you cannot make the column shorter than the number of characters being indexed.
ALTER COLUMN specifies new default values for columns or removes old defaults. If the old default is removed and the column can be NULL, the new default is NULL. If the column cannot be NULL, MySQL gives a default value of 1. The default value assignment is described in the 7.7 CREATE TABLE syntax.
DROP INDEX delete 1 index This is an extension of MySQL to ANSI SQL92.
If a column is dropped from a table, the column is also dropped from any index of which they are a part. If all the columns that make up an index are discarded, the index is also discarded.
DROP PRIMARY KEY Discards the primary index. If such an index does not exist, it discards the first UNIQUE index in the table. If PRIMARY KEY is not explicitly specified, MySQL marks the first UNIQUE key as PRIMARY KEY.
Using the C API function mysql_info(), you can find out how many records were copied and (when using IGNORE) how many records were deleted due to duplication of the only one key.
Here is an example that shows some ALTER TABLE usage. Let's take 1 as follows

/* Create a table t1 Start: 
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
 Rename the table from t1 to t2:
mysql> ALTER TABLE t1 RENAME t2;
 In order to change the columns a , from INTEGER Instead of TINYINT NOT NULL( The name 1 sample ) , and change the column b . 

 from CHAR(10) Instead of CHAR(20) , while renaming it from b Instead of c : 
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
 increase 1 A new TIMESTAMP Columns, called d : 
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
 In the column d To add 1 Index, and make columns a Primary key: 
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
 Delete the column c : 
mysql> ALTER TABLE t2 DROP COLUMN c;
 increase 1 A new one AUTO_INCREMENT Integer column, named c : 
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
      ADD INDEX (c);*/

Note that c is indexed because the AUTO_INCREMENT column must be indexed, and in addition we declare c to be NOT because the indexed column cannot be NULL. When you add an AUTO_INCREMENT column, the column values are automatically filled in with sequential numbers.

Specific case analysis:

When we need to change the name of the table or change the field of the table, we need to use the MySQL.

To start this tutorial, 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;

If there is only one field left in the table, you cannot use DROP to drop the field.

MySQL uses the ADD clause to add columns to a data table. For example, add an i field to table testalter_tbl and define 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 clause 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. Following the CHANGE keyword is 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 Effects 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 this field to NULL by default.

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 delete the default value of a field as follows:

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 table type, you can use the ALTER command and the TYPE clause. Try the following example, where we change the type of table testalter_tbl to MYISAM:

Note: The SHOW TABLE STATUS statement can be used 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 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 RENAME TO alter_tbl;

The above is all the content of this article, I hope to help you learn.