How to Modify Columns in a Table in MySQL

08/03/2021

Contents

In this article, you will learn how to modify columns in a table in MySQL.

Modifying columns in a table in MySQL

In MySQL, you can modify columns in an existing table to change their data type, length, default values, and more.

Syntax for modifying columns in MySQL

The syntax for modifying columns in a table in MySQL is as follows:

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type [NULL|NOT NULL] [DEFAULT default_value] [AFTER column_name];
 
  • “table_name” is the name of the table containing the column you want to modify.
  • “column_name” is the name of the column you want to modify.
  • “new_data_type” is the new data type you want to assign to the column.
  • “NULL” or “NOT NULL” specifies whether the column can contain NULL values or not.
  • “DEFAULT” specifies the default value for the column.
  • “AFTER column_name” specifies the position of the modified column in the table.

Example of modifying columns in MySQL

Let’s look at an example of how to modify the data type of the “salary” column in the “employees” table from “INT” to “DECIMAL” with a precision of 10 and scale of 2:

ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10,2);

This command modifies the data type of the “salary” column to “DECIMAL(10,2)”.

Modifying multiple columns in MySQL

If you need to modify multiple columns in a table in MySQL, you can use the “MODIFY COLUMN” clause multiple times in the same ALTER TABLE statement. Here’s an example of how to modify the data type and default value of the “email” and “phone_number” columns in the “employees” table:

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100) DEFAULT 'N/A',
MODIFY COLUMN phone_number VARCHAR(20);

This command modifies the data type of the “email” column to “VARCHAR(100)” and sets its default value to “N/A”. It also modifies the data type of the “phone_number” column to “VARCHAR(20)”.

Changing the position of a column in MySQL

You can also change the position of a column in a table by using the “AFTER column_name” clause. Here’s an example of how to move the “email” column after the “last_name” column in the “employees” table:

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100) AFTER last_name;

This command moves the “email” column after the “last_name” column in the “employees” table.

#MySQL