MySQL Add Columns Tutorial and Examples

Sometimes, you need to add additional columns to an existing table to hold some necessary information.

Suppose, you have a user table that stores usernames, emails, passwords, etc. But as the needs of the system change, you need to store the mobile number in the user table. To do this, you don’t need to recreate a table, just add a column to the existing table.

MySQL allows you to use the ALTER TABLE statement to modify an existing table. To add one or more columns to a table, use the ALTER TABLE ... ADD COLUMN statement.

MySQL ADD COLUMN syntax

The following is the syntax of MySQL ALTER TABLE ... ADD COLUMN statement:

ALTER TABLE table_name
ADD [COLUMN] column_definition [FIRST|AFTER existing_column]
[, ADD [COLUMN] column_definition [FIRST|AFTER existing_column]];

In this syntax:

• The table_name after the ALTER TABLE keyword is the name of the table to add the column to.
• After the ADD [COLUMN] keyword is the definition of the column. You can omit the COLUMN keyword.
• You need to define a column in column_definition, include: column name, column data type, column constrains and so on.
• By default, the new column will be added as the last column of the table. You can add a new column as the first column of a table by using the FIRST keyword, or add a column after an existing existing_column column using AFTER existing_column.
• If you need to add multiple columns in one statement, use multiple ADD COLUMN clauses spearated by comma.

Determine if column exists

Before adding a column to a table, you may need to determine if a column with the same name exists in the table first.

To view information for all columns in a table, you can use the DESC statement or the SHOW COLUMNS statements. Both statements can display all the columns in a table, but the SHOW COLUMNS statement is more flexible and convenient because it can filter the result set according to your requirements.

The syntax of the DESC statement is as follows:

DESC table_name;

The syntax of the SHOW COLUMNS statement is as follows:

SHOW [FULL] COLUMNS FROM table_name [LIKE pattern]

The LIKE clause is used to specify the filter pattern.

MySQL ADD COLUMN Examples

This example demonstrates how to use the ALTER TABLE ... ADD COLUMN statement to add one or two columns to a table.

Suppose, we have a user table with two columns: ID and username.

We create a table named user in the testdb database to store user information using the following statement:

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

If you do not have the testdb database, please create the database and select the database using the following statement:

CREATE DATABASE testdb;
use testdb;

After creating the table, you can view all the columns in this table using the DESC statement:

DESC user;

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

Insert a row of data into the user table, as follows;

INSERT INTO user (name) values ('Tim');

Add a column to a table using MySQL ADD COLUMN

To store the user’s age in the user table, you need to add column named age using the following statement,

ALTER TABLE user
ADD COLUMN age INT NOT NULL DEFAULT 20;

Here, we’ve added a age column of type INT, and it is a non-null column with a default value of 20.

Note that if you do not specify a default value via the DEFAULT keyword, MySQL will use 0 as the default value of INT.

Let’s view all the columns in the user table with the DESC statement:

DESC user;

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| age   | int          | NO   |     | 20      |                |
+-------+--------------+------+-----+---------+----------------+

We can also verify the data by querying the table with the following SELECT statement:

SELECT * FROM user;

+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tim  |  20 |
+----+------+-----+

Add two columns to a table using MySQL ADD COLUMN

As the system evolves, you may need to store the user’s email and phone number columns in the user table. This is almost a must-have information for users.

To add email and mobile number columns to the user table, use the following statement:

ALTER TABLE user
ADD COLUMN email VARCHAR(255) NOT NULL,
ADD COLUMN phone VARCHAR(255) NOT NULL;

Here, we’ve added two columns to the user table: email for email addresses and phone for mobile numbers.

Let’s examine the rows of the user table:

SELECT * FROM user;

+----+------+-----+-------+-------+
| id | name | age | email | phone |
+----+------+-----+-------+-------+
|  1 | Tim  |  20 |       |       |
+----+------+-----+-------+-------+

We found that MySQL uses the empty string as the default value for non-null VARCHAR columns.

Conclusion

MySQL provides the ALTER TABLE ... ADD COLUMN statement to add one or more columns to a table. With the ALTER TABLE statement, you can rename tables, rename columns, add columns, delete columns, modify column properties, and more.