Overview
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/
For more information on MySQL Database: