Support for adding a column if it doesn't exist

[ScottPierce]

Dialect

MySQL

Failing SQL

SELECT count(*) INTO @EXIST
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydatabase'
  AND COLUMN_NAME = 'mycolumn'
  AND TABLE_NAME = 'mytable' LIMIT 1;

SET @query = IF (@exist <= 0, 'ALTER TABLE mydatabase.`mytable`  ADD COLUMN `mycolumn` MEDIUMTEXT NULL',
    'select \' COLUMN EXISTS\' status');
PREPARE stmt FROM @query;
EXECUTE stmt;

Description

I'm trying to write my migration files in a repeatable manner (So I don't have to create a new migration version EVERY time I need to make a change), but I can't seem to do this when altering a table when adding a column or adding an index. I don't necessarily need to do it in the above way, but I do think this should be supported some way.

[ScottPierce]

Ran into the desire to do this again:

IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tableName'
             AND table_schema = 'database_name'
             AND column_name = 'columnName')  THEN
 
  ALTER TABLE Example ADD COLUMN location VARCHAR(50) NULL;
 
END IF;

[AlecKazakova]

is that valid MySQL? thats nutts if you can just do if statements

[ScottPierce]

I'll be honest, I'm no mysql expert. I'm trying to find a way to make my migrations idempotent so that they can be re-run. every time there is a change. I took the syntax from here: https://www.w3schools.blog/alter-table-if-column-not-exist-in-mysql