MySQL Change Column Type
Changing the column definition is a very useful feature for the database whenever we want. MySQL allows a command to alter the column definition such as name and type according to our needs. We can do this with the help of an ALTER TABLE statement in MySQL. In this article, we are going to see step by step how to change the column definition by using the ALTER TABLE command.
Syntax
The following is the syntax to change the data type of a column in MySQL:
Let us understands the parameters of the statement in detail:
- First, we will specify the table name after the ALTER TABLE keywords containing the column we are going to modify.
- Second, we will specify the column name that we want to change the data type after the MODIFY clause.
- Finally, we will specify the new data type for the column to change the column type.
If we want to change the data types of multiple columns in a single statement, we can use the statement as follows:
MySQL Change Column Type Example
Let us understand how the ALTER TABLE statement works in MySQL through the various examples to change column definition. Suppose we have a table named ‘employees‘ that contains the following columns descriptions:
Table Name: employees
In the above image, we can see that the emp_id column data type is an integer type. Now suppose we want to change the data type of emp_id from INT to VARCHAR; we can execute the below statement to do this:
After executing the statement, if no error is found, the below output should have appeared:
Let us verify the table information through the DESCRIBE statement:
In the output, we can observe that the datatype of the emp_id column has been changed from integer to varchar.
Suppose you want to change more than one column data type of the ’employees’ table using a single statement. In that case, we will specify the column name using the comma(,) operator. See the below statement:
After executing the statement, if no error is found, the below output should have appeared:
Now, verify the table information again through the DESCRIBE statement:
In the output, we can observe that the datatype of the emp_id column has been changed from varchar to integer, and the income column has been changed from integer to varchar.
How to change the column data type in MySQL workbench?
To change the column data type using MySQL workbench, we first need to launch it and then log in using the username and password we created earlier. It will return the screen as follows:
Now do the following steps for changing the column definition such as name or data type:
1. Go to the Navigation tab and click on the Schema menu that contains all the databases available in the MySQL server.
2. Select the database (for example, mystudentdb), double click on it, and show the sub-menu containing Tables, Views, Functions, and Stored Procedures.
3. Expand the Tables sub-menu and select the table in which you want to change the column definition. For example, the ‘students‘ table contains the following column definition:
4. If we want to change the class column type from INT to VARCHAR, then right-click on the selected table (students), and then click on the Alter Table option. We should get the screen as below:
5. Click into the datatype box corresponding to the column you want to change, choose the desired type, and click on the Apply button. See the below image:
6. We will get the below screen. If no error is found, click on the Apply button.
7. Finally, click on the Finish button to complete the process.
8. Now, verify the table information again through the DESCRIBE statement.
In the output, we can observe that the data type of the class column has been changed from integer to varchar.