Update MySQL database table schema

Question

I want to update a MySQL database schema (with MySQL code) but I am unfortunately not sure of the state of the tables, as they are distributed..

Let's say some 'clients' have a table called "user" with a schema like

name VARCHAR(64) NOT NULL
password VARCHAR(64) NOT NULL

I want to add an email column, but it's possible that they already have an email column (depending on their installation version).

How can I run a command that ensures that there is a email column and does nothing if it's already there? Keep in mind I would be doing this for many tables that are more complex.

I know I could be creating temp tables and re-populating (and will if it's the only solution) but I figure there might be some kind of CREATE or UPDATE table command that has "oh you already have that column, skip" logic.

Answer 1

You can try like this:

DELIMITER $$
CREATE PROCEDURE Alter_MyTable()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_NAME = 'user' AND 
                            COLUMN_NAME = 'email');
    IF _count = 0 THEN
        ALTER TABLE user
            ADD COLUMN email varchar(512);
    END IF;
END $$
DELIMITER ;

or rather make it a generic stored procedure like this:

create procedure AddColumnIfDoesntExists(
    IN dbName tinytext,
    IN tableName tinytext,
    IN fieldName tinytext,
    IN fieldDef text)
begin
    IF NOT EXISTS (
        SELECT * FROM information_schema.COLUMNS
        WHERE column_name=fieldName
        and table_name=tableName
        and table_schema=dbName
        )
    THEN
        set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
            ' ADD COLUMN ',fieldName,' ',fieldDef);
        prepare stmt from @ddl;
        execute stmt;
    END IF;
end;
//

delimiter ';'

Answer 2

If the column already exists the ALTER TABLE ADD COLUMN statement will throw an error, so if you are thinking that you might lose data because of trying to add a column that already exists that won't be the case, if any you need to handle error. See add column to mysql table if it does not exist

There are also resources telling you how to deal with these with store procedures, etc. See MySQL add column if not exist.

Hope it helps.