MySQL ALTER TABLE Add Column

Introduction to MySQL ALTER TABLE Add Column

In MySQL, ALTER TABLE command is used to change the name of the table or rename one or more columns of the table, add new columns, remove existing ones, modify the datatype, length, index of one or more column and we can also rename the name of the table. This command is most often used with ADD, DROP and MODIFY statements depending on the operation that you wish to perform for the table, its columns, or indexes. We can even change the sequence of the columns in the table using the ALTER TABLE command. In this article, we will learn the syntax and the usage of the ALTER TABLE command statement to add the column to a table with the help of examples.

ALTER TABLE statement with ADD command –

We can add one or more columns in the existing table by using the ALTER TABLE statement with the ADD command. The syntax of adding the columns using the ALTER statement is as follows –

ALTER TABLE name_of_table
ADD name_of_new_column details_of_column
[ FIRST | AFTER name_of_existing_column ];

• name_of_table – This is the name of the existing table in which we wish to add a new column using the ALTER query.
• name_of_new_column – This is the name of the new column that we are adding to the table.
• details_of_column – This helps to specify the details and definition of the new column we are adding that includes the datatype of the column and other details such as NULL or NOT NULL, UNIQUE, etc.
• name_of_existing_column – We can specify the position of the column which we are adding with respect to the existing position of the columns in the table named name_of_table by using the FIRST and AFTER keywords. FIRST represents that the new column will be placed in the beginning while AFTER signifies the position of the new column after name_of_existing_column named column in the table. The default position where the new column is added is at the last.

Example using command-line

Consider one table named educab_writers table that is described as follows when we use

DESC educba_writers;

command:

Now, we want to add two columns named rate and joining_date columns to educba_writers table using the ALTER TABLE command. The datatype of rate will be decimal with precision and scale as (5,2) and the datatype of joining_date should be DATE. We can write the ALTER TABLE query with ADD statement in it to add two columns in the following way –

ALTER TABLE educba_writers
ADD rate DECIMAL(5,2),
ADD joining_date DATE;

Execution of above query statement gives the following output on command-line –

Let us describe the educba_writers table once again to check whether our columns are added successfully by using following query statement –

DESC educba_writers;

that gives following output –

Let us retrieve the records in the educab_writers table and see the value for the added columns for existing records –

select * from educba_writers;

that gives following output –

All column values have default value as NULL.

The default value of the newly added columns depends on the value of the SQL mode. This value can be check by executing the following query –

SELECT @@GLOBAL.sql_mode;

that gives following output on my command-line database server that is used above –

Example using SQLYog client tool –

Let us consider an existing table named developers. After executing the following SELECT command –

SELECT * FROM `developers`;

it gives the following output –

Now, we want to add one more column named experience which is of integer datatype as it will store value in years. For this we can make the use of ALTER TABLE statement with ADD command and the query will be somewhat like this –

ALTER TABLE developers
ADD experience INTEGER NOT NULL DEFAULT 0;

executing the above query will give following result –

Let us check the records by selecting it using this query –

SELECT * FROM `developers`;

that gives following output –

Note that the value of newly added experience column is set to 0 as we have mentioned it in column definition.

We can even add multiple columns in single query statement. Suppose, we want to add three more columns named joiningDate, age and address. We can do so, by executing the following ALTER TABLE command –

ALTER TABLE developers
ADD joiningDate DATE
FIRST,
ADD age INT NOT NULL
AFTER NAME,
ADD address VARCHAR(100) NOT NULL
AFTER experience;

that gives the following output –

Let us now, retrieve the records from developers table to view our changes by executing following select query –

SELECT * FROM `developers`;

that gives following output –

As we can see all the new columns are added at their respective positions that we mentioned and are initialized to its default value depending on their datatype. The default value of integer datatype column is 0, varchar i.e string is “” blank and for date is NULL. The default value of the newly added columns depends on the value of the SQL mode. This value can be check by executing the following query –

SELECT @@GLOBAL.sql_mode;

that gives following output on my remote server for which we are using the client-side tool named SQL yog that is used above –

Conclusion: MySQL ALTER TABLE Add Column

We can use the ALTER TABLE command to add, modify, and drop the columns of the table and also to rename the columns and the table names. When we use the ADD statement with ALTER TABLE command then we can add single or multiple columns to the existing tables and define the constraints and restrictions along with the data type of the new column to be added using ALTER TABLE command.

If none of the default value is mentioned for the newly added column then MYSQL assigns the default value to the column as NULL or depending on the data type that is defined for that column such as string i.e VARCHAR datatype will have “” value as default, INT or INTEGER data typed column will have 0(zero) value by default and so on depending on the value of the strict SQL mode and other variables of SQL have appropriate values assigned to them.

Recommended Articles

This is a guide to MySQL ALTER TABLE Add Column. Here we discuss Introduction, syntax, Description, and Example using command-line with code implementation. You can also go through our other related articles to learn more –

1. MySQL having
2. MySQL BLOB
3. MySQL encode()
4. MySQL today()