How can we change the data type of the column in MySQL table?

It can be done with the help of ALTER TABLE command of MySQL. Consider the table ‘Student’ in which the data type of ‘RollNo’ column is declared as Integer, can be seen from the following query −

mysql> DESCRIBE Student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Name   | varchar(20) | YES  |     | NULL    |       |
| RollNo | int(11)     | YES  |     | NULL    |       |
| Grade  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

Now suppose we want to change the data type of RollNo from Int(11) to Varchar(10) the following query will do it −

mysql> Alter Table student Modify column RollNo Varchar(10);
Query OK, 3 rows affected (0.25 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> Desc Student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Name   | varchar(20) | YES  |     | NULL    |       |
| RollNo | varchar(10) | YES  |     | NULL    |       |
| Grade  | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

From the query above it can be observed that the data type of RollNo has been changed from integer to varchar.

Varun

Published on 21-Feb-2018 13:17:36

Previous Page Print Page