,

MySQL 5.7 – Check if column Exist

Posted by

I have go through more than 30 post in Stack Overflow, but all solution look similar and end up its not straight forward syntax to follow. You need procedure to execute it. So I write this post to help others developer to solved the problem to check if column exist in table in MySql

Yup, that is a true story that happened to me last month when I just want to check if a column exist in MySql but end up can’t find a solution for this simple question. I believe many of you have done some searching on google about this problem. try this keyword, change to another, and put more details in the query to Google but all solutions look the same and when you try in your MySQL editor, it is show that the syntax is not valid.

Scenario

You want to check if a column exists in the table Orders. If exist, then do this else do that.  — this is the scenario I want to achieve. 

Solution

MySQL Syntax

 
SHOW COLUMNS FROM eComm.Orders LIKE 'BaseOrderGrandTotal'.
 

Code Behind – c#

In C# code I used this syntax like below.

 
DataSet dsColumns = new DataSet();
string checkColumn = string.Format("SHOW COLUMNS FROM {0}.Orders LIKE 'BaseOrderGrandTotal';", DBName);
MySqlDataAdapter ColumnsAvailable = new MySqlDataAdapter(checkColumn, dbConn);
ColumnsAvailable.Fill(dsColumns, "Columns");
if (DataHelper.DataSourceIsEmpty(dsColumns))
{
//data set empty, dont have new columns, alter table.
string alterStatement = String.Format(@"ALTER TABLE {0}.Orders                        
ADD COLUMN BaseOrderGrandTotal decimal(19,2) NULL", DBName);

dbComm = new MySqlCommand(alterStatement, dbConn);
rc = dbComm.ExecuteNonQuery();

}


Happy coding. B-)

UPDATE SOLUTION

In MySQL, you can add a column to a table if it does not already exist using the ALTER TABLE statement with the ADD COLUMN clause along with the IF NOT EXISTS condition. Here’s the syntax:

ALTER TABLE your_table
ADD COLUMN IF NOT EXISTS column_name column_definition;

Replace your_table with the actual name of your table, column_name with the name of the column you want to add, and column_definition with the definition of the new column (e.g., data type and any additional attributes like NOT NULL, DEFAULT value, etc.).

Here’s an example of adding a column named new_column with the data type VARCHAR(50) to a table named your_table:

ALTER TABLE your_table
ADD COLUMN IF NOT EXISTS new_column VARCHAR(50);

This statement will only add the new_column if it doesn’t already exist in the your_table table.