MySQL alter command is used to change the schema of the tables mean changing the definition of tables. It allows:
- Adding a new column in existing table.
- Removing a column from existing table.
- Modifying the data type, name, default values of a column in the existing table
- Creating new index or constraint in existing table.
- Dropping the index from the table.
- Renaming the existing table.
- Changing the Engine of the Table.
- Changing the collation or charset of table or some columns.
ALTER command is AUTO COMMIT command. Basic syntax of ALTER command is shown below, actions here contain any operations listed above:
ALTER TABLE TBALE_NAME [ACTIONS]
Adding a New Column in Existing Table
For example we have a data of the company names along with their address and price of some server.
ID | Company | Address | Amount |
1 | HCL | NY | $1000 |
2 | Apple | Chicago | $2000 |
3 | IBM | INDIA | $3000 |
4 | Singapore | $15000 |
After Alteration of the table, structure will be:
ID | Company | Address | Amount | YOM |
1 | HCL | NY | $1000 | 1999 |
2 | Apple | Chicago | $2000 | 1999 |
3 | IBM | INDIA | $3000 | 1999 |
4 | Singapore | $15000 | 1999 |
Removing a column from the existing table.
Suppose we don’t want the Amount information from the table then the alter command to drop the AMOUNT column is written as:
ALTER TABLE Company DROP Column Amount;
ID | Company | Address | YOM |
1 | HCL | NY | 1999 |
2 | Apple | Chicago | 1999 |
3 | IBM | INDIA | 1999 |
4 | Singapore | 1999 |
mysql> mysql> ALTER TABLE cities ADD COLUMN YOM YEAR ; Query OK, 4 rows affected (1.81 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql>
Modifying the data type, name, default values of a column in the existing table
Now suppose we want to change the YOM column to some date/month/year format and the default value of which is 0000-00-00 and then column name is date-of-manufacturing DOM. Then the ALTER command will be:
ALTER table Company MODIFY COLUMN YOM DOM DATE Default ‘0000-00-00’;
After altering the table the table data will be:
ID | Company | Address | Amount | DOM |
1 | HCL | NY | $1000 | 0000-00-00 |
2 | Apple | Chicago | $2000 | 0000-00-00 |
3 | IBM | INDIA | $3000 | 0000-00-00 |
4 | Singapore | $15000 | 0000-00-00 |
Create new index or constraint in existing table.
For Adding the index in the table on a particular column say Company the ALTER query is:
Alter Table Company ADD INDEX `idx_1`(`Company`);
Here the index name idx_1 is created over the column Company. There is no change in the name, values, data type and default values of the column.
Drop the index from the table.
For drop the index from the table we can use the ALTER command as:
Alter Table Company DROP INDEX `idx_1`;
Here we don’t need to define the column name as the index name is uniquely defined in the table definition. No effect on name, values, data type and default values of the column.
Rename the existing table.
For changing the table name we can use the ALTER command as:
Alter Table Company RENAME TO `Company_data`;
Here “RENAME TO“ is the keyword for changing the table name via ALTER command. No effect on name, values, data type and default values of the column.
Change the Engine of the Table.
For change the Storage engine of the table we can use the following ALTER command:
Alter Table Company_dataENGINE=’InnoDB’;
If the engine is already InnoDB then there is no effect on the table, otherwise the engine changed to InnoDB as defined in the ALTER command.
Change the collation or charset of table or some columns.
A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.
For example: To change the collation of the table.
Alter table Company convertto character set utf8 collate utf8_unicode_ci;
Here Company is the table name and utf8 is the new character set and utf8_unicode_ci is new Collation.