Renaming a Column in MySQL

You can rename a column as shown below. You need to include the column’s definition, even if you are not changing it, otherwise MySQL displays an error:

 

mysql> create table andrews_table

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.09 sec)

 

mysql> desc andrews_table;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| col1  | varchar(5) | YES  |     | NULL    |       |

+-------+------------+------+-----+---------+-------+

1 row in set (0.01 sec)

 

mysql> alter table andrews_table

    -> change col1 col2 varchar(5);

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> desc andrews_table;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| col2  | varchar(5) | YES  |     | NULL    |       |

+-------+------------+------+-----+---------+-------+

1 row in set (0.06 sec)

 

mysql>

 

And you can change a column’s definition at the same time, if you wish:

 

mysql> alter table andrews_table

    -> change col2 col3 varchar(10);

Query OK, 0 rows affected (0.18 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> desc andrews_table;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| col3  | varchar(10) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.00 sec)

 

mysql>