MySQL: How to add column to existing table

To add column in existing table, you may refer to the 2 examples below:-

Example I: Add new varchar column to the end of the table

ALTER TABLE `tablename_here` ADD `new_column_name` VARCHAR( 255 ) NOT NULL ;

Example II: Add new integer column after an existing column in table

ALTER TABLE `tablename_here` ADD `new_column_name` INT NOT NULL AFTER `existing_column` ;

MySQL tables are easy to extend with additional columns.

The following SQL command will create a table called contacts as described above:

CREATE TABLE contacts ( contact_id INT(10),
name VARCHAR(40),
birthdate DATE
);

To add a column called email to the contacts table created with a datatype of ARCHAR(80), use the following SQL statement:

ALTER TABLE contacts ADD email VARCHAR(60);

This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

[sankar@new-host ~]$ mysql -u root -p

Enter password:

mysql>

mysql> show databases;

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

| Database           |

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

| information_schema |

| dciiphone          |

| mysql              |

| test               |

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

4 rows in set (0.37 sec)

mysql> use test

Database changed

mysql> CREATE TABLE contacts ( contact_id INT(10),

    -> name VARCHAR(40),

    -> birthdate DATE

    -> );

Query OK, 0 rows affected (0.07 sec)

mysql> show tables

    -> ;

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

| Tables_in_test |

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

| contacts       |

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

1 row in set (0.00 sec)

mysql>

mysql> ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

Query OK, 0 rows affected (0.13 sec)

Records: 0  Duplicates: 0  Warnings: 0