Summary: in this tutorial, we will show you how to add a column to a table using MySQL ADD COLUMN statement.
Introduction to MySQL ADD COLUMN statement
To add a new column to an existing table, you use the ALTER TABLE
ADD COLUMN
statement as follows:
Let’s examine the statement in more detail.
- First, you specify the table name after the
ALTER TABLE
clause. - Second, you put the new column and its definition after the
ADD COLUMN
clause. Note thatCOLUMN
keyword is optional so you can omit it. - Third, MySQL allows you to add the new column as the first column of the table by specifying the
FIRST
keyword. It also allows you to add the new column after an existing column using theAFTER existing_column
clause. If you don’t explicitly specify the position of the new column, MySQL will add it as the last column.
To add two or more columns to a table at the same time, you use the following syntax:
Let’s take a look some examples of adding a new column to an existing table.
MySQL ADD COLUMN examples
First, we create a table named
vendors
for the demonstration purpose using the following statement:
Second, we add a new column named
phone
to the vendors
table. Because we specify the position of the phone
column explicitly after the name
column, MySQL will obey this.
Third, we add a new column named
vendor_group
to the vendors
table. At this time, we don’t specify the new column’s position so MySQL adds the vendor_group
column as the last column of the vendors
table.
Let’s insert some rows into the
vendors
table.
We can query the data of the
vendors
table to see the changes.
Fourth, add two more columns
email
and hourly_rate
to the vendors
table at the same time.
Note that both
Let’s check the data in the
email
and hourly_rate
columns are assigned to NOT NULL values
However, the vendors
table already has data. In such cases, MySQL will use default values for those new columns.Let’s check the data in the
vendors
table.
The email column is populated with blank values, not the
NULL
values. And the hourly_rate column is populated with 0.00
values.
If you accidentally add a column that already exists in the table, MySQL will issue an error. For example, if you execute the following statement:
MySQL issued an error message:
For the table with a few columns, it is easy to see which columns are already there. However, with a big table with hundred of columns, it is more difficult.
In some situations, you want to check whether a column already exists in a table before adding it. However, there is no statement like
ADD COLUMN IF NOT EXISTS
available. Fortunately, you can get this information from the columns
table of the information_schema
database as the following query:
In the WHERE clause, we passed three arguments: table schema or database, table name, and column name. We used IF function to return whether the column exists or not.
In this tutorial, you have learned how to add one or more columns to a table using MySQL ADD COLUMN statement.
0 comments:
Post a Comment