How to Change the Column Type in MySQL?

MySQL is an open-source relational database management system that uses tables to store data with specific data types assigned to each column. However, sometimes the data type of a column needs to be changed, such as when increasing the size of a VARCHAR column, converting a numeric column to a string column, etc. In such cases, the “ALTER TABLE” statement can be utilized to modify the structure of an existing table, including changing the data type of a column.

This guide will explain how to change the column type in MySQL using:

CMD
Workbench

How to Change the Column Type in MySQL Using CMD?

To change the column type in MySQL using CMD, the “ALTER TABLE” statement can be utilized with the “MODIFY” clause. The following syntax can be utilized to change the column type:

ALTER TABLE [table_name] MODIFY [column_name] [new_data_type];

In the above syntax:

The “ALTER TABLE” is utilized to modify the existing table.
Replace the “[table_name]” with the table name that you want to modify.
Replace the “[column_name]” with the particular column name of a table.
Replace the “[new_data_type]” with the data type that you want to set for the “[column_name]”.

Let’s move toward the examples to understand how to change the column type in MySQL. But before that log in to MySQL, use the particular database, and show the table details that you want to modify by typing the given below command:

SHOW COLUMNS FROM lh_table;

In the above command, “lh_table” is the table name.

Output

The output showed the table’s column names, data types, and other details regarding the table’s column.

Now explore the following examples of how to change the column type in MySQL.

Example 1: Change the Column Data Type

Let’s change the data type of the “name” column from VARCHAR to TEXT using the “ALTER TABLE” statement, as shown below:

ALTER TABLE lh_table MODIFY name TEXT;

Here in the above command, the “lh_table” represents the name of the selected table, and “name” represents the table’s column to be altered.

Output

The output showed that the data type of the “name” column had changed from “varchar(50)” to “TEXT”.

How to Change the Column Data Type in MySQL Using Workbench?

Follow the given instructions to change the column type in MySQL using the Workbench:

Log in to the MySQL database in the Workbench.
Explore the database and its tables by clicking on the down-arrow.
Select the table by right-clicking on the table name.
Click on the “ALTER Table…” to modify the table:

The table’s details will appear, here you can do any kind of changes to the table. To change the data type of an “is_active” column, you can either select the desired data type from the dropdown list or manually enter the data type in the particular field:

Change the “is_active” column type from “TINYINT(1)” to “LONGTEXT” and click on the “Apply” button:

Confirm the query and again click on the “Apply” button:

The message “SQL script was successfully applied to the database.” showed that the change has been applied, after that click on the “Finish” button:

Confirm if the changes have been made or not:

From the screenshot, it can be seen that the changes have been made.

Conclusion

Changing the column type in MySQL can be done by utilizing “CMD” or “Workbench”. In CMD, the “ALTER” command can be used with the “MODIFY” clause to change the data type of a particular column in a table. To change the column data type in Workbench, the user can select the desired table, click on the “ALTER Table…” option, and modify the column data type in the table editor. This guide has explained how users can utilize different ways to change the column data type in MySQL.

How to Change the Column Type in MySQL?