Wednesday, May 6, 2009

MySql: Add a new column to an existing MySQL table


Share at Facebook

You might have faced such problem. Suppose you are working with a table, and which was working good. But after few days, you came to know that you need another column to add with your existing table. How to do that?

Mysql have such option. Using ALTER keyword, you can easily add a new column to your existing mysql table. For this article, we are considering the below table for example.

mysql> create table info( name varchar(255), id int(11));

mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)


Now lets add a new column at our old table using ALTER key.
mysql> ALTER TABLE info ADD COLUMN dept varchar(255);

This will add a new column dept having varchar data type at info table. You can change varchar into any other data-type. Also you can define the primary/unique key at the same time.

After adding the new column with info table, it will look like as below.
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| dept | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+




No comments: