How can I modify an existing column's data type?

To modify an existing column’s data type, you can use MODIFY. Let us first create a table −

mysql> create table DemoTable
   (
   ClientId varchar(100),
   ClientName varchar(100),
   ClientAge int,
   ClientProjectDeadline timestamp,
   ClientCountryName varchar(100),
   isMarried boolean,
   ClientNumber bigint
   );
Query OK, 0 rows affected (0.70 sec)

Check the description of table −

mysql> desc DemoTable;

This will produce the following output −

+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| ClientId              | varchar(100) | YES  |     | NULL    |       |
| ClientName            | varchar(100) | YES  |     | NULL    |       |
| ClientAge             | int(11)      | YES  |     | NULL    |       |
| ClientProjectDeadline | timestamp    | YES  |     | NULL    |       |
| ClientCountryName     | varchar(100) | YES  |     | NULL    |       |
| isMarried             | tinyint(1)   | YES  |     | NULL    |       |
| ClientNumber          | bigint(20)   | YES  |     | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+
7 rows in set (0.34 sec)

Now, change the column(ClientNumber) data type bigint to varchar(20) −

mysql> alter table DemoTable modify ClientNumber varchar(20);
Query OK, 0 rows affected (1.82 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check the description of table once again −

mysql> desc DemoTable;

This will produce the following output −

+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| ClientId              | varchar(100) | YES  |     | NULL    |       |
| ClientName            | varchar(100) | YES  |     | NULL    |       |
| ClientAge             | int(11)      | YES  |     | NULL    |       |
| ClientProjectDeadline | timestamp    | YES  |     | NULL    |       |
| ClientCountryName     | varchar(100) | YES  |     | NULL    |       |
| isMarried             | tinyint(1)   | YES  |     | NULL    |       |
| ClientNumber          | varchar(20)  | YES  |     | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Look at the above sample output, the data type has been changed from bigint to varchar(20).

George John

Published on 03-May-2019 11:08:02

Previous Page Print Page