How to add a column in a table in MySQL?

To add a column in a table in MySQL, we can use ALTER command with add column command. First, let us create a table with columns Id and Name. After that, we will add column name Age and Address with the help of ALTER command.

The following is the query to create a table.

mysql> create table AddingColumnNameDemo
   -> (
   -> Id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.65 sec)

We can check information about the table with the help of DESC command.

The query is as follows −

mysql> desc AddingColumnNameDemo;

Here is the output.

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Id    | int(11)      | YES  |     | NULL    |       |
| Name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

As you can see above, there are only two columns.

The following is the syntax to add column Age and Address with the help of ALTER command.

alter table yourTableName
add column yourColumnName1 dataType,
add column yourColumnName2 dataType,
.
.
add column yourColumnNameN dataType,

Apply the above syntax in the following query to add more than one columns into the table.

mysql> alter table AddingColumnNameDemo
   -> add column Age int,
   -> add column Address varchar(300);
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

We have added two more column names (Age, Address) successfully. Let us now check with the help of DESC command. The following is the query.

mysql> DESC AddingColumnNameDemo;

Here is the output.

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| Id      | int(11)      | YES  |     | NULL    |       |
| Name    | varchar(100) | YES  |     | NULL    |       |
| Age     | int(11)      | YES  |     | NULL    |       |
| Address | varchar(300) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Chandu yadav

Published on 30-Nov-2018 11:15:55

• Related Questions & Answers
• How to add a column to a MySQL table in Python?
• Can we add a column to a table from another table in MySQL?
• How to add subtotal to a table column displaying NULL in MySQL?
• How to add a column using MySQL SELECT in an already created table?
• Add a single year to all the date records in a MySQL table column
• How to delete a column from a table in MySQL?
• How to add a NOT NULL column in MySQL?
• How can we add a time interval to date stored in a column of MySQL table?
• How to substring value in a MySQL table column?
• How to add a “created at” column in a table to set the timestamp in MySQL?
• How to check if a column exist in a MySQL table?
• Add leading zeros to a MySQL column?
• Add 30 days to a value in a MySQL table?
• How do I add a check constraint to a table in MySQL?
• How to rename a column in an existing MySQL table?