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>

Modifying Column Definitions in MySQL

If you change a column definition, MySQL converts the data in that column for you. If it finds data which it cannot convert, it displays a warning and you can see details by using the show warnings command. In the example, a varchar column is converted to int. MySQL is unable to convert the value ABC so it sets it to zero instead:

 

mysql> create table andrews_table

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into andrews_table

    -> values ('ABC');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table

    -> values ('123');

Query OK, 1 row affected (0.00 sec)

 

mysql> alter table andrews_table

    -> change col1 col1 int;

Query OK, 2 rows affected, 1 warning (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

(N.B. I have reformatted the output from the show warnings command to make it fit on the page.)

mysql> show warnings;

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

| Level   | Code | Message                           |

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

| Warning | 1366 | Incorrect integer value:          |
|         |      | 'ABC' for column 'col1' at row 1  |

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

1 row in set (0.00 sec)

 

mysql> select * from andrews_table;

+------+

| col1 |

+------+

|    0 |

|  123 |

+------+

2 rows in set (0.00 sec)

 

mysql>