1

I have read this question and the solutions but they are more or less not applicable to my case. I have only SQL option and the PROCEDURE should be avoided, we may not have the required permission.

This is a SQL script that creates missing tables, columns and indexes etc. (schema update)

I see that in MariaDB we can use IF NOT EXISTS clause but this is not available in MySQL. Is there any similar way or workaround available in MySQL?

Community
  • 1
  • 1
Izhar Aazmi
  • 915
  • 12
  • 25

1 Answers1

0

Using the IF() in a SET statement to come up with the logic works for me:

SET @s = (SELECT IF(
    (SELECT COUNT(1)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'your_table'
        AND table_schema = 'your_schema'
        AND column_name = 'column_name'
    ) > 0,
    "SELECT 1",
    "ALTER TABLE your_table ADD column_name VARCHAR(100)"
));

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Spade
  • 2,220
  • 1
  • 19
  • 29
  • Ok, I see it has to be this long anyway. Can you please help me generalise this to make this reusable as there are tens of such columns to be added/dropped. It would be a pain to do this for each one. – Izhar Aazmi May 07 '16 at 06:13
  • Unfortunately each set statement can only work on a single operand. However, you can set multiple variables in a single statement such as `set @a, @b=`; Upvote if this answer is of use to you. – Spade May 07 '16 at 06:20
  • @zhar Aazmi - do you not want to do it with some tools like liquibase. They can generate you all necessary SQLs ? – Bernd Buffen May 07 '16 at 07:37
  • @BerndBuffen In fact I have to interact with an application that I do not control. I am only allowed to supply an `update.sql` file that will be executed there. – Izhar Aazmi May 07 '16 at 10:35
  • @Izhar Aazm - Yes that is ok, but you can use liquibase to geneate this update.sql . Then you have also no problems with new INDEX or Fields to remove – Bernd Buffen May 07 '16 at 10:38
  • @BerndBuffen Really? I'll check it out soon this Monday! – Izhar Aazmi May 07 '16 at 16:51