How to alter the data type of a MySQL table’s column?

You can use modify command for this. Let us first us create a table.

mysql> create table DemoTable (    StudentId varchar(200) not null,    StudentName varchar(20),    StudentAge int,    StudentAddress varchar(20),    StudentCountryName varchar(20) ); Query OK, 0 rows affected (0.73 sec)

Now check the description of table.

mysql> desc DemoTable;

This will produce the following output −

+--------------------+--------------+------+-----+---------+-------+ | Field              | Type         | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | StudentId          | varchar(200) | NO   |     | NULL    |       | | StudentName        | varchar(20)  | YES  |     | NULL    |       | | StudentAge         | int(11)      | YES  |     | NULL    |     | | StudentAddress     | varchar(20)  | YES  |     | NULL    | | | StudentCountryName | varchar(20)  | YES  |     | NULL    | | +--------------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)

Here is the query to alter the data type from varchar to BIGINT −

mysql> alter table DemoTable modify StudentId BIGINT(10) UNSIGNED NOT NULL DEFAULT 0; Query OK, 0 rows affected (1.51 sec) Records: 0 Duplicates : 0 Warnings : 0

Above, we have changed the column ‘StudentId’ datatype from varchar(200) to BIGINT(10). Let us check the description of table once again.

mysql> desc DemoTable;

This will produce the following output −

+--------------------+---------------------+------+-----+---------+-------+ | Field              | Type                | Null | Key | Default | Extra | +--------------------+---------------------+------+-----+---------+-------+ | StudentId          | bigint(10) unsigned | NO   |     | 0       |       | | StudentName        | varchar(20)         | YES  |     | NULL    |       | | StudentAge         | int(11)             | YES  |   | NULL | | | StudentAddress     | varchar(20)         | YES  | | NULL | | | StudentCountryName | varchar(20)         | YES  | | NULL | | +--------------------+---------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)