MySQL Alter Table - Modify Column

Last Updated: 01 July, 2023

In MySQL, we can modify a column in a table using the ALTER TABLE statement along with the MODIFY COLUMN clause. The MODIFY COLUMN clause allows us to change the data type, nullability, default value, or other properties of an existing column.

Here's the basic syntax for modifying a column in MySQL:

 ALTER TABLE table_name
 MODIFY COLUMN column_name new_data_type [optional_modifications];

Let me explain the components:

• table_name: The name of the table containing the column we want to modify.
• column_name: The name of the column we want to modify.
• new_data_type: The new data type we want to assign to the column.
• optional_modifications: Any other optional modifications we want to make, such as adding a default value, changing nullability, etc.

Here's an example to demonstrate how to modify a column in MySQL:

Changing the data type of a column:

 ALTER TABLE employees
 MODIFY COLUMN age INT;

Changing the column data type and adding a default value:

 ALTER TABLE products 
 MODIFY COLUMN price DECIMAL(10, 2) DEFAULT 0.00;

Making a column not nullable:

 ALTER TABLE customers
 MODIFY COLUMN email VARCHAR(100) NOT NULL;

Remember that when modifying a column, we should be cautious as it can affect existing data. Always make sure to take a backup of the data before executing any potentially destructive queries.

Additionally, note that some changes might require additional considerations, especially if the table contains a significant amount of data. For large tables, altering a column can be time-consuming, and we might need to consider using tools like pt-online-schema-change to minimize downtime during the modification process.