The MySQL ALTER command is very useful when you want to change a name of your table, any table field or if you want to add or delete an existing column in a table.
Let us begin with the creation of a table called testalter_tbl.
root@host# mysql -u root -p password;
Enter password:
mysql> use mydb;
Database changed
mysql> CREATE TABLE IF NOT EXISTS `mydb`.`products` (
-> `supplier_ids` LONGTEXT NULL DEFAULT NULL,
-> `id` INT(11) NOT NULL AUTO_INCREMENT,
-> `product_code` VARCHAR(25) NULL DEFAULT NULL,
-> `product_name` VARCHAR(50) NULL DEFAULT NULL,
-> `description` LONGTEXT NULL DEFAULT NULL,
-> `standard_cost` DECIMAL(19,4) NULL DEFAULT '0.0000',
-> `list_price` DECIMAL(19,4) NOT NULL DEFAULT '0.0000',
-> `reorder_level` INT(11) NULL DEFAULT NULL,
-> `target_level` INT(11) NULL DEFAULT NULL,
-> `quantity_per_unit` VARCHAR(50) NULL DEFAULT NULL,
-> `discontinued` TINYINT(1) NOT NULL DEFAULT '0',
-> `minimum_reorder_quantity` INT(11) NULL DEFAULT NULL,
-> `category` VARCHAR(50) NULL DEFAULT NULL,
-> `attachments` LONGBLOB NULL DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> INDEX `product_code` (`product_code` ASC))
-> ENGINE = InnoDB
-> DEFAULT CHARACTER SET = utf8;
Query OK, 0 rows affected (0.22 sec)
mysql> show columns from products;
+--------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+----------------+
| supplier_ids | longtext | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_code | varchar(25) | YES | MUL | NULL | |
| product_name | varchar(50) | YES | | NULL | |
| description | longtext | YES | | NULL | |
| standard_cost | decimal(19,4) | YES | | 0.0000 | |
| list_price | decimal(19,4) | NO | | 0.0000 | |
| reorder_level | int(11) | YES | | NULL | |
| target_level | int(11) | YES | | NULL | |
| quantity_per_unit | varchar(50) | YES | | NULL | |
| discontinued | tinyint(1) | NO | | 0 | |
| minimum_reorder_quantity | int(11) | YES | | NULL | |
| category | varchar(50) | YES | | NULL | |
| attachments | longblob | YES | | NULL | |
+--------------------------+---------------+------+-----+---------+----------------+
14 rows in set (0.06 sec)Dropping, Adding or Repositioning a Column
If you want to drop an existing column attachments from the above MySQL table, then you will use the DROP clause along with the ALTER command as shown below −
mysql> alter table products drop attachments;
Query OK, 0 rows affected (0.71 sec)
Records: 0 Duplicates: 0 Warnings: 0A DROP clause will not work if the column is the only one left in the table.
To add a column, use ADD and specify the column definition. The following statement restores the attachments column to the testalter_tbl −
mysql> ALTER TABLE products ADD `attachments` LONGBLOB NULL DEFAULT NULL;
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0After issuing this statement, testalter will contain the same two columns that it had when you first created the table, but will not have the same structure. This is because there are new columns that are added to the end of the table by default. So even though i originally was the first column in mytbl, now it is the last one.
mysql> show columns from products;
+--------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+----------------+
| supplier_ids | longtext | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_code | varchar(25) | YES | MUL | NULL | |
| product_name | varchar(50) | YES | | NULL | |
| description | longtext | YES | | NULL | |
| standard_cost | decimal(19,4) | YES | | 0.0000 | |
| list_price | decimal(19,4) | NO | | 0.0000 | |
| reorder_level | int(11) | YES | | NULL | |
| target_level | int(11) | YES | | NULL | |
| quantity_per_unit | varchar(50) | YES | | NULL | |
| discontinued | tinyint(1) | NO | | 0 | |
| minimum_reorder_quantity | int(11) | YES | | NULL | |
| category | varchar(50) | YES | | NULL | |
| attachments | longblob | YES | | NULL | |
+--------------------------+---------------+------+-----+---------+----------------+
14 rows in set (0.07 sec)To indicate that you want a column at a specific position within the table, either use FIRST to make it the first column or AFTER col_name to indicate that the new column should be placed after the col_name.
Try the following ALTER TABLE statements, using SHOW COLUMNS after each one to see what effect each one has −
ALTER TABLE products DROP attachments;
ALTER TABLE products ADD attachments LONGBLOB NULL DEFAULT NULL FIRST;
ALTER TABLE products DROP attachments;
ALTER TABLE products ADD attachments LONGBLOB NULL DEFAULT NULL AFTER target_level;The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.
Altering (Changing) a Column Definition or a Name
To change a column's definition, use MODIFY or CHANGE clause along with the ALTER command.
For example, to change column product_code from varchar(25) to varchar(30) , you can use the following command −
mysql> Alter TABLE products MODIFY product_code VARCHAR(30);
Query OK, 0 rows affected (0.71 sec)
Records: 0 Duplicates: 0 Warnings: 0With CHANGE, the syntax is a bit different. After the CHANGE keyword, you name the column you want to change, then specify the new definition, which includes the new name.
Try out the following example −
mysql> Alter TABLE products CHANGE product_name prd_name varchar(35);
Query OK, 0 rows affected (0.82 sec)
Records: 0 Duplicates: 0 Warnings: 0If you now use CHANGE to convert prd_name from varchar(35) back to varchar(30) without changing the column name, the statement will be as shown below −
mysql> Alter TABLE products CHANGE prd_name prd_name varchar(30);
Query OK, 0 rows affected (0.78 sec)
Records: 0 Duplicates: 0 Warnings: 0The Effect of ALTER TABLE on Null and Default Value Attributes − When you MODIFY or CHANGE a column, you can also specify whether or not the column can contain NULL values and what its default value is. In fact, if you don't do this, MySQL automatically assigns values for these attributes.
The following code block is an example, where the NOT NULL column will have the value as 100 by default.
mysql> ALTER TABLE products
-> MODIFY prd_name varchar(30) NOT NULL DEFAULT 'demo';
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0If you don't use the above command, then MySQL will fill up NULL values in all the columns.
Altering (Changing) a Column's Default Value
You can change a default value for any column by using the ALTER command.
Try out the following example.
mysql> alter table products alter product_code set default 1000;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from products;
+--------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+----------------+
| supplier_ids | longtext | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_code | varchar(30) | YES | MUL | 1000 | |
| prd_name | varchar(30) | NO | | demo | |
| description | longtext | YES | | NULL | |
| standard_cost | decimal(19,4) | YES | | 0.0000 | |
| list_price | decimal(19,4) | NO | | 0.0000 | |
| reorder_level | int(11) | YES | | NULL | |
| target_level | int(11) | YES | | NULL | |
| attachments | longblob | YES | | NULL | |
| quantity_per_unit | varchar(50) | YES | | NULL | |
| discontinued | tinyint(1) | NO | | 0 | |
| minimum_reorder_quantity | int(11) | YES | | NULL | |
| category | varchar(50) | YES | | NULL | |
+--------------------------+---------------+------+-----+---------+----------------+
14 rows in set (0.01 sec)You can remove the default constraint from any column by using DROP clause along with the ALTER command.
mysql> alter table products alter product_code DROP default;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show columns from products;
+--------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+----------------+
| supplier_ids | longtext | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_code | varchar(30) | YES | MUL | NULL | |
| prd_name | varchar(30) | NO | | demo | |
| description | longtext | YES | | NULL | |
| standard_cost | decimal(19,4) | YES | | 0.0000 | |
| list_price | decimal(19,4) | NO | | 0.0000 | |
| reorder_level | int(11) | YES | | NULL | |
| target_level | int(11) | YES | | NULL | |
| attachments | longblob | YES | | NULL | |
| quantity_per_unit | varchar(50) | YES | | NULL | |
| discontinued | tinyint(1) | NO | | 0 | |
| minimum_reorder_quantity | int(11) | YES | | NULL | |
| category | varchar(50) | YES | | NULL | |
+--------------------------+---------------+------+-----+---------+----------------+
14 rows in set (0.03 sec)Altering (Changing) a Table Type
You can use a table type by using the TYPE clause along with the ALTER command. Try out the following example to change the testalter_tbl to MYISAM table type.
To find out the current type of a table, use the SHOW TABLE STATUS statement.
mysql> ALTER TABLE products TYPE = MYISAM;
mysqlshow table status LIKE 'products'G;
*************************** 1. row ***************************
Name: products
type:MYISAM
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2021-02-15 15:13:30
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)Renaming (Altering) a Table
To rename a table, use the RENAME option of the ALTER TABLE statement.
Try out the following example to rename products to product_table;
mysql> ALTER TABLE products RENAME to products_table;
Query OK, 0 rows affected (0.20 sec)You can use the ALTER command to create and drop the INDEX command on a MySQL file. We will discuss in detail about this command in the next chapter.