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

• Related Questions & Answers
• How can I modify an existing MySQL column’s data type?
• How can we modify an existing MySQL event?
• How can we modify column/s of MySQL table?
• How can we modify an existing module in Java 9?
• How do I change matplotlib's subplot projection of an existing axis?
• How can I drop an existing column from a MySQL table?
• How can I change the name of an existing column from a MySQL table?
• How can I use SPACE() function along with MySQL’s column data?
• Does SQL Server have an equivalent to MySQL's ENUM data type?
• What can another keyword be used instead of MODIFY to modify the column/s of MySQL table?
• How can I set an ImageView's width and height programmatically in Android?
• How to set an element's display type with JavaScript?
• How can I get a file's size in C++?
• How can I vary a shape's alpha with Tkinter?
• How can we amend the declared size of a column’s data type in MySQL?