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)
);
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
Post a Comment