MySQL Add New Column to a table

In this tutorial, we will learn steps to add new column to a MySQL table. To add a new column, we can use an ALTER TABLE statement with the appropriate clause that specifies the new column’s definition.

Syntax

The general syntax for the statement is a follows:

mysql> ALTER TABLE <table_name>
             ADD COLUMN <column_name> <datatype> <column_constraints> ;

That ALTER TABLE statement changes the table structure.

Column names within a table must be unique, so we cannot add a column with the same name as one that already exists in the table.

Example

Let’s add a new column to a sample table called demo. Column names are not case sensitive. So, if the table already contains a column with certain name, we cannot add a new column with any combination of the name with uppercase or lowercase.

For example, if the table contains a column named id, we cannot add a new column using the names: id, ID, iD, Id.

mysql> DESC demo;
+———-+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————–+——+—–+———+—————-+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| joindate | datetime | YES | MUL | NULL | |
+———-+————–+——+—–+———+—————-+
3 rows in set (0.00 sec)

 

Add New Column

Let’s add a new column email to the table. The data type for the column is VARCHAR.

Issue the ALTER TABLE statement.

mysql> ALTER TABLE demo
          -> ADD COLUMN email VARCHAR(60) NOT NULL ;

Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC demo;
+———-+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————–+——+—–+———+—————-+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| joindate | datetime | YES | MUL | NULL | |
| email | varchar(60) | NO | | NULL | |
+———-+————–+——+—–+———+—————-+
4 rows in set (0.01 sec)

Table structure after adding the new column to the table.

Adding a new column to a table will not populate the existing rows with values.

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/