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:
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:
To put this into better perspective, let us create a table and add a few columns. To create the table, enter the following command:
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.
We will then add another column as below:
Once that is done, we will now proceed to insert a couple of rows into the table as follows:
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:
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:
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:
You can also drop multiple columns at the same time using the command:
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.