Skip to main content

How to add column safely (if not exists) in MySql?

On production environment if we want to run a script to add a column in a table it is
better to add it safely, but in MySql there is no direct way to do this.
Here safely means, if this column already exists in table ignore it otherwise add this new column.

Check the below query to safely add column safely in mysql:

Let's say we've a table "TEST_TO_DELETE" and we want to safely add new column "MY_NEW_COLUMN"

-- Drop table if exists
DROP TABLE IF EXISTS TEST_TO_DELETE;

-- Create table
CREATE TABLE TEST_TO_DELETE 
(
   Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   FirstName VARCHAR(50)
);

-- Step1. check if column exists or not
SELECT COUNT(*) INTO @countOfColumn
FROM `INFORMATION_SCHEMA`.COLUMNS 
WHERE TABLE_SCHEMA = 'TEST' 
        AND TABLE_NAME = 'TEST_TO_DELETE' 
        AND COLUMN_NAME = 'MY_NEW_COLUMN';

-- Step2. if column exists then @countOfColumn > 0 and execute the query
--    in this case execute the query
SET @sqlQuery = IF(@countOfColumn <= 0, ' ALTER TABLE TEST_TO_DELETE ADD COLUMN MY_NEW_COLUMN BIT NOT NULL DEFAULT 0 ', ' SELECT 1 ; ');

-- Step3. prepare the statement to 
PREPARE prpStatementToExecute FROM @sqlQuery;

-- Step4. Execute the query
EXECUTE prpStatementToExecute;




Comments

Popular posts from this blog

EFCore - Collate function

Search in SQL server query is generally case insensitive (by default or based on database level collation). Suppose we have an employees table with a row having first-name column value as "My-First-Name", so if we want to do the case-sensitive search we have to explicitly use the related collate: In EF 5 (currently in Release Candidate version [RC.2.20475.6]) Collate function got introduced which helps us to use our specific collation based search.  C# with EF5 code sample: var employeeCaseSensitiveSearch = _dbContext.Employees .Where(x => EF.Functions.Collate(x.FirstName, "Latin1_General_CS_AS") == "my-first-name") .FirstOrDefault(); A related database query will be something like this: T-SQL: Case sensitive search (use specific collation e.g.: Latin1_General_CS_AS) SELECT * FROM dbo.Employees AS e WHERE e.FirstName Collate Latin1_General_CS_AS = 'my-first-name' Some of the useful CSharp function which g...

Azure SQL - AAD Authentication from you client application in user context

When we try to migrate our IaaS SQL to Azure SQL along with other code changes & refactoring, one of the most prominent things that come into the picture is Authentication through a client application (web app or windows form, excel-add-ins...). With IaaS/OnPrem SQL we could use Windows Authentication to let your client application interact with the SQL server with the user's context. But to Azure SQL we can't do the Windows Authentication and also we should avoid SQL-based login (user/password).  To login to Azure SQL, it's always preferable to use Azure Active Directory-based authentication (integrated flow, MFA, or AAD based password). Now the problem comes how to achieve this with your client application.  The solution to this problem statement is, u se AAD app with delegated permission to access the users' claim [email, sid] in access token & configure it based on your client (e.g. windows form, web application) Here are ...

EFCore - Scaffold-DbContext how to use

EFCore versions, we don't have GUI to manage the DB-entities & dbContext like we used to have with EF6 (or EF5/4 version). With EFCore we've the Scaffold-DbContext command which helps us to create entities & DB context from our DB-objects.  Let's see how to use Scaffold-DbContext: Assume we're connecting with SQL Server so after creating a sample project you'll have to install EFCore related libraries as mentioned below.  Install entity-framework-core for SQL Server, at this time 3.1.9 is the latest stable version   Microsoft.EntityFrameworkcore   Microsoft.EntityFrameworkCore.SqlServer  To use Scaffold-DbContext, following two NuGet packages are required Microsoft.EntityFrameworkCore.Tools  Microsoft.EntityFrameworkCore.Design Let's say we've two DB tables (e.g. dbo.Employees & dbo.Users )  Now let's see how to use Scaffold-DbContext to create c-...