Alter table if exists or create if doesn't

Question

I need to run an installer which can also be an updater. The installer needs to be able to end up having a certain scheme/structure of the mysql database, regardless if some of the tables existed, missed a few columns, or need not to be changed because their structure is up to date.

How can I make an elegant combination of ALTER and CREATE?

I was thinking there must be something like "ADD... IF... Duplicate"

Say I have table A. In one client the table has one column -A1, and another client has the same table but with column A1 and column A2.

I want my sql command to make both clients' table A hold three columns : A1, A2, and A3.

Again, my script is a sql file that I dump to mysql.

How do I do it? Thanks :-)

Answer 1

MySQL INFORMATION_SCHEMA database to the rescue:

-- First check if the table exists
IF EXISTS(SELECT table_name 
            FROM INFORMATION_SCHEMA.TABLES
           WHERE table_schema = 'db_name'
             AND table_name LIKE 'wild')

-- If exists, retreive columns information from that table
THEN
   SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
     FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'tbl_name'
      AND table_schema = 'db_name';

   -- do some action, i.e. ALTER TABLE if some columns are missing 
   ALTER TABLE ...

-- Table does not exist, create a new table
ELSE
   CREATE TABLE ....

END IF;

More information:

• MySQL Reference Manual: Chapter 19. INFORMATION_SCHEMA Tables
• MySQL Reference Manual: The INFORMATION_SCHEMA TABLES Table
• MySQL Reference Manual: The INFORMATION_SCHEMA COLUMNS Table

UPDATE:

Another option, possibly easier, is to drop the existing table and re-create it again with the new schema. To do this, you need:

1. Create temporary table, an exact copy of the existing table
2. Populate the temporary table with the data from the old table
3. Drop the old table
4. Create the new table with new schema
5. Populate the new table with the information from the temporary table
6. Drop temporary table.

So, in SQL code:

CREATE TABLE old_table_copy LIKE old_table;

INSERT INTO old_table_copy
SELECT * FROM old_table;

DROP TABLE old_table;

CREATE TABLE new_table (...new values...);

INSERT INTO new_table ([... column names from old table ...])
SELECT [...column names from old table ...] 
FROM old_table_copy;

DROP TABLE old_table_copy;

Actually the last step, "Drop temporary table.", you could skip for a while. Just in case, you would want to have some sort of backup of the old table, "just-in-case".

More information:

• MySQL Reference Manual: CREATE TABLE Syntax
• MySQL Reference Manual: INSERT Syntax
• MySQL Reference Manual: INSERT ... SELECT Syntax

Answer 2

Well if you are using a coding language do:

SHOW TABLES LIKE 'myTable'

if it return a value call the alter else call the create

Answer 3

Although this topic is a few years old, I was looking for the solution of a similar problem. Regarding to the answer of @GregD, I found a solution that works for me. The SQL script creates tables, however, if there are already tables in the database, it should apply changes. In this version, it only works if there are additional columns. Modified (remaining) columns do not work.

-- We create a procedure that is dropped if it exists already.
DROP PROCEDURE IF EXISTS changeFunction;
-- We have to change the delimiter for the procedure.
DELIMITER $$
CREATE PROCEDURE changeFunction()
    BEGIN
-- Check if table already exists
IF EXISTS(SELECT table_name 
            FROM INFORMATION_SCHEMA.TABLES
           WHERE table_schema = 'your_database_name'
             AND table_name LIKE 'your_table_name')

-- It exists, so create a new table and copy the data.
THEN
    -- Copy the data into a copy of the table.
    CREATE TABLE `your_table_name_copy` LIKE `your_table_name`;
    INSERT INTO `your_table_name_copy` SELECT * FROM `your_table_name`;
    -- Remove old table
    DROP TABLE `your_table_name`;
    -- Create the new table
    CREATE TABLE IF NOT EXISTS `your_table_name` (
        -- Your columns
    );
    -- Copy values, it determines the old column names by itself
    SET @v1 := (SELECT GROUP_CONCAT(COLUMN_NAME)
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name_copy');
    -- Since the columns are detected automatically, we have to execute the 
    -- statement as prepared statement.
    SET @q1 := CONCAT('INSERT INTO `your_table_name` (', @v1, ') ',
                      'SELECT ', @v1, ' FROM `your_table_name_copy`');
    PREPARE stmt FROM @q1;
    EXECUTE stmt;
    -- Remove copy
    DROP TABLE `your_table_name_copy`;
ELSE
    -- It does not exist, simply create it
    CREATE TABLE IF NOT EXISTS `your_table_name` (
        -- Your columns
    );
END IF;
END $$
-- Reset the delimiter
DELIMITER ;
-- Call the function
CALL changeFunction();

Answer 4

I'm going to give two examples of how to do this using MySQL 8.0+. First for the alter statement to work if something exists it MUST be in a stored procedure. The example below shows how to drop and add a constraint if it exists or not already. If it doesn't exist it will simply ignore the if and create it. If it DOES exist it will drop it and then subsequently create it again. This is useful for debugging so you can quickly change your constraint logic and test it. For those that are new to DELIMITER, it essentially changes what would be the ; to $$ in this case. This is necessary so that the procedure knows the ; belong to it and not that the full SQL query is ending.

    DROP PROCEDURE IF EXISTS trading.create_constraint;
    DELIMITER $$
    CREATE PROCEDURE trading.create_constraint()
        BEGIN
            IF EXISTS (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME = 'assigned_equity_percent_not_over_100')
            THEN
                ALTER TABLE Strategies DROP CONSTRAINT assigned_equity_percent_not_over_100;
            END IF;
            ALTER TABLE Strategies ADD CONSTRAINT assigned_equity_percent_not_over_100 CHECK (assigned_equity_percent <= 100 AND assigned_equity_percent>=0);
        END$$
    DELIMITER ;
    CALL create_constraint();

Another example below, is how you can create a more advanced procedure with a trigger that will count the sum of all values and make sure they do not exceed 100 when updating or inserting by throwing an error via the SIGNAL SQLSTATE '45000' error.

DROP PROCEDURE IF EXISTS trading.check_sum_strategy_assigned_equity;
DELIMITER $$
CREATE PROCEDURE trading.check_sum_strategy_assigned_equity(IN new_equity_percentage DECIMAL(5,2),IN old_equity_percentage DECIMAL(5,2),IN updating BOOLEAN)
BEGIN
    IF (updating=false) THEN
        IF ((SELECT SUM(assigned_equity_percent) total FROM Strategies WHERE strategy_pack_id = strategy_pack_id)+new_equity_percentage)>100
        THEN
            SIGNAL SQLSTATE '45000' SET message_text = 'Can not INSERT a value of 100 for assigned equity percent with the given strategy_pack_id';
        END IF;
    ELSE
        IF ((SELECT SUM(assigned_equity_percent) total FROM Strategies WHERE strategy_pack_id = strategy_pack_id)-old_equity_percentage+new_equity_percentage)>100
        THEN
            SIGNAL SQLSTATE '45000' SET message_text = 'Can not UPDATE a value of 100 for assigned equity percent with the given strategy_pack_id';
        END IF;
    END IF;
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS ins_sum_strat_ass_eq;
CREATE TRIGGER ins_sum_strat_ass_eq
BEFORE INSERT ON Strategies
FOR EACH ROW
CALL trading.check_sum_strategy_assigned_equity(NEW.assigned_equity_percent,0.00,FALSE);
DROP TRIGGER IF EXISTS up_sum_strat_ass_eq;
CREATE TRIGGER up_sum_strat_ass_eq
BEFORE UPDATE ON Strategies
FOR EACH ROW
CALL trading.check_sum_strategy_assigned_equity(NEW.assigned_equity_percent,OLD.assigned_equity_percent,TRUE);

Something to note is that DELIMITER only works via MySQL client, not through the API's, so you will get errors saying that it does not recognize DELIMITER. To account for this you could run code something like what I did in javascript below.

//@JA - This function sanitizes custom SQL so that any delimiter code is removed which only works via client or phpmyadmin.
var sanitize_for_mysql = function(sql) {
    sql = sql.replace(/([$][$])/gm, ";");
    sql = sql.replace(/DELIMITER ;/gm, "");
    return sql;
};

module.exports = sanitize_for_mysql;

By doing this you can still run this code using an ORM like Sequelize for example.