Altering MySQL Tables Using MySQL Add Column Statements

In this post, we will look at the different statements DBA’s used to alter MySQL database tables. Among the statements we will focus on is MySQL add column and MySQL drop column statements.

Introducing The Add Column Statement In MySQL

The MySQL add column statement is not solitary – before using it; you have to start with the ALTER TABLE table_name statement as shown below:

ALTER TABLE sample
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column];

So, what is happening with the statement above? The first part specifies the table that you are altering. In the second part, you add a column, name it, and add a definition. At the end of the second statement, you can specify whether you want to add the new column after a particular column.

Failure to specify the AFTER section automatically communicates to the system to add the column as the last one in the table.

You can also add more than one column at the same time. Below is the statement to add two columns simultaneously:

ALTER TABLE sample
ADD [COLUMN] column_name_1 column_1_definition [FIRST|AFTER existing_column],
ADD [COLUMN] column_name_2 column_2_definition [FIRST|AFTER existing_column];

To put this into better perspective, let us create a table and add a few columns. To create the table, enter the following command:

CREATE TABLE IF NOT EXISTS vendors (
		Id INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255)
);

The second step after creating the table is to start adding the columns. We will add our first column contact and another column vendor_group. For the first column, we will specify it to be added after the column name.

ALTER TABLE vendors
ADD COLUMN contact VARCHAR(15) AFTER name;

We will then add another column as below:

ALTER TABLE vendors
ADD COLUMN vendors_group INT NOT NULL;

Once that is done, we will now proceed to insert a couple of rows into the table as follows:

INSERT INTO vendors(name, contact, vendor_group)
VALUES('IBM','(408)-387-8729',1);
INSERT INTO vendors(name, contact, vendor_group)
VALUES('Microsoft','(408)-892-8829',1);

There are numerous other columns you can add to a table and assign them different data types. It is easy to add columns to a small table with a few columns. But in some situations, it is important that you first check whether the column you want to add already exists.

The statement below will fetch data from the column table of the information_schema from where you can check whether the column exists:

SELECT IF(count(*) =1, 'Exist','Not Exist') AS result
FROM Information_schema.columns
WHERE Table_schema = 'classicmodesl'
		AND table_name = 'vendors'
    AND column_name = 'phone';

Now that you know how to use MySQL add column statements, you need to know how to use the MySQL drop column statement too as it is inevitable that you will use it at some point.

Introducing The MySQL Drop Column Statement

The statement for dropping columns from MySQL tables is ALTER TABLE DROP COLUMN. An example of a drop statement is:

ALTER TABLE vendors
DROP COLUMN contacts;

The first part of the statement specifies the table name while the second specifies the column you want to drop from the table. The column name comes after the DROP COLUMN statement. In the DROP COLUMN statement, using the keyword COLUMN is not mandatory:

ALTER TABLE vendors
DROP contacts;

You can also drop multiple columns at the same time using the command:

ALTER TABLE vendors
DROP COLUMN contacts,
DROP COLUMN vendor_group,
...;

Before removing any columns from your MySQL tables, you need to remember a few points:

• If there are database objects like views, triggers, and stored procedures that depend on the column, deleting the column invalidates these database objects. To restore any objects functionality after deleting a column they depend on, you need to manually change the objects code.
• If there are applications that depend on the column you drop, the applications code must also be changed for proper functionality.
• If your table is large, removing a column can impact your database’s performance

While there are many other considerations you need to make when adding or removing columns, this post covers the basics of column manipulation.