How to change MySQL column definition?

MySQLMySQLi Database

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.

Updated on 30-Jul-2019 22:30:23