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.

score 5 · Accepted Answer · 2015-04-27T18:44:12.030

5

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 ';'

edited Apr 27 '15 at 18:44

answered Apr 27 '15 at 18:27

Would there be a pragmatic way to do this for every column for every table, or would I need to do it individually? – tdoakiiii Apr 27 '15 at 18:43
2

@tdoakiiii:- Updated my answer with a generic stored procedure. Now you need to call this stored procedure by providing the tablename and columname. Hope that helps :) – Apr 27 '15 at 18:45
2

@tdoakiiii:- Call it like `call AddColumnIfDoesntExists(Database(), 'user', 'email', 'varchar(100) null');` – Apr 27 '15 at 18:46
Brilliant, thanks. This will not gen an error if the column already exists right? – tdoakiiii Apr 27 '15 at 18:53
I'm getting an error on the line ' ADD COLUMN ',fieldName,' ',fieldDef); #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 15 – tdoakiiii Apr 27 '15 at 19:04

score 1 · Answer 2 · edited Dec 22 '20 at 20:22

1

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.

edited Dec 22 '20 at 20:22

Alexis Wilke

19,179
10
84
156

answered Apr 27 '15 at 18:28

jbarrueta

4,907
2
20
21

The issue is I don't know if one column is missing or 5 or missing, and I was trying to avoid a mess of add columns. Instead I was wishing for a magic "update" procedure without going through each column. This is the result of no foresight prior to me jumping in. – tdoakiiii Apr 27 '15 at 18:42

Update MySQL database table schema

2 Answers2