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).
Published on 03-May-2019 11:08:02
