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

• Related Questions & Answers
• How can we change the name of a MySQL table?
• How to change the column position of MySQL table without losing column data?
• How can we amend the declared size of a column’s data type in MySQL?
• How to alter the data type of a MySQL table’s column?
• How can we modify column/s of MySQL table?
• How can we insert current year automatically in a YEAR type column of MySQL table?
• How can we apply BIT_LENGTH() function on the column/s of MySQL table?
• How can I change the name of an existing column from a MySQL table?
• How can we remove a column from MySQL table?
• How can we update MySQL table after padding a string with the values of the column?
• How can we extract a substring from the value of a column in MySQL table?
• How can I change the storage engine of a MySQL table?
• How to change the type of a column in PostgreSQL?
• How can we insert data into a MySQL table?
• How can we put comments in a column of existing MySQL table?