mysql+add+column+int_A2-03-11.DDL-How to Add Columns to a Table Using MySQL ADD COLUMN Statement

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:

1

2

ALTERTABLEtable

ADD[COLUMN]column_namecolumn_definition[FIRST|AFTERexisting_column];

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 that COLUMNkeyword 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 the AFTER 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:

1

2

3

4

ALTERTABLEtable

ADD[COLUMN]column_name_1column_1_definition[FIRST|AFTERexisting_column],

ADD[COLUMN]column_name_2column_2_definition[FIRST|AFTERexisting_column],

...;

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:

1

2

3

4

CREATETABLEIF NOT EXISTSvendors(

idINTAUTO_INCREMENTPRIMARY KEY,

nameVARCHAR(255)

);

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.

1

2

ALTERTABLEvendors

ADDCOLUMNphoneVARCHAR(15)AFTERname;

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.

1

2

ALTERTABLEvendors

ADDCOLUMNvendor_groupINTNOT NULL;

Let’s insert some rows into the vendors table.

1

2

3

4

5

INSERTINTOvendors(name,phone,vendor_group)

VALUES('IBM','(408)-298-2987',1);

INSERTINTOvendors(name,phone,vendor_group)

VALUES('Microsoft','(408)-298-2988',1);

We can query the data of the vendors table to see the changes.

1

2

3

4

SELECT

id,name,phone,vendor_group

FROM

vendors;

Fourth, add two more columns email and hourly_rate to the vendors table at the same time.

1

2

3

ALTERTABLEvendors

ADDCOLUMNemailVARCHAR(100)NOT NULL,

ADDCOLUMNhourly_ratedecimal(10,2)NOT NULL;

Note that both email and hourly_rate columns are assigned to NOT NULL values However, the vendorstable already has data. In such cases, MySQL will use default values for those new columns.

Let’s check the data in the vendors table.

1

2

3

4

SELECT

id,name,phone,vendor_group,email,hourly_rate

FROM

vendors;

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:

1

2

ALTERTABLEvendors

ADDCOLUMNvendor_groupINTNOT NULL;

MySQL issued an error message:

1

Error

Code:1060.Duplicatecolumnname'vendor_group'

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:

1

2

3

4

5

6

7

8

SELECT

IF(count(*)=1,'Exist','Not Exist')ASresult

FROM

information_schema.columns

WHERE

table_schema='classicmodels'

ANDtable_name='vendors'

ANDcolumn_name='phone';

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.