How to change MySQL column definition?

To change MySQL column definition, we can use modify or change clause with ALTER command. Let us first create a table with a column as ID, with int data type. We will modify the same column name with varchar data type.

Creating a table.

mysql> create table ModifyColumnDemo
   -> (
   -> id int
   -> );
Query OK, 0 rows affected (0.52 sec)

Now, let us write the syntax to change the column definition. The syntax is as follows −

alter table yourTableName modify column columnName data type;

Apply the above syntax to change the MySQL definition.

mysql> alter table ModifyColumnDemo modify column id varchar(10) not null;
Query OK, 0 rows affected (1.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

Let us now check whether the column has been change with the new data type or not. For that, we will be using the DESC command.

mysql> desc ModifyColumnDemo;

The following is the output.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.15 sec)

You can see in the above output, we have changed the column definition with new datatype varchar.

Arjun Thakur

Updated on: 2019-07-30T22:30:23+05:30

238 Views

Kickstart Your Career

Get certified by completing the course

Get Started