MySQL - is it possible to conditionally run queries?

Question

I was wondering if it's possible to conditionally run certain statements in MySQL. Something like this:

IF EXISTS (SELECT * FROM information_schema.columns WHERE TABLE_NAME = 'test_table' AND COLUMN_NAME = 'userid' AND IS_NULLABLE = 'NO') 
THEN
    ALTER TABLE test_table MODIFY userid INT(11) NULL;
END IF;

I've done some googleing and I'm not pulling up anything useful or sane. Wondering if I'm missing something or if this is just a serious MySQL limitation.

There is an IF statement in MySQL - what happened when you tried to run this? — CHill60, Oct 01 '15 at 16:27
http://stackoverflow.com/questions/24571611/mysql-alter-table-if-column-not-exists — William_Wilson, Oct 01 '15 at 16:27
You need a shell aroung flow statements like `if`. A procedure, event, trigger or function — juergen d, Oct 01 '15 at 16:30
@William_Wilson so you're telling me I need to make a SP every time I want to conditionally update my schema? That seems totally backwards! — mBrice1024, Oct 01 '15 at 16:31

score 0 · Answer 1 · answered Oct 01 '15 at 16:39

0

Thanks to William_Wilson, Here's my working query. I still think this is totally backwards, but I supposed that's how MySQL rolls...

delimiter //
create procedure update_stuff() 

    begin
    IF EXISTS (SELECT * FROM information_schema.columns WHERE TABLE_NAME = 'test_table' AND COLUMN_NAME = 'userid' AND IS_NULLABLE = 'NO')
    THEN
        ALTER TABLE test_table MODIFY userid INT(11) NULL;
    END IF;

END
//

delimiter ;

-- Execute the procedure
call update_stuff();

-- Drop the procedure
drop procedure update_stuff;

answered Oct 01 '15 at 16:39

mBrice1024

790
5
25

It is a little backwards, yes. FYI, the query you're running doesn't need to be conditional. You can run it whether the column is nullable or not with the same result. – William_Wilson Oct 01 '15 at 16:43
Well this is just a simple test. I have a need for if statements with more complex queries. For example: I add a new column to a 200mill row table null by default. Then I need to batch update this new column and finally change it to not null. I want to make absolutely sure that if some joe shmo running my script against prod later on during a patch release isn't going to tie up server resources needlessly by running the update script again. The if protects against this. And no, default values when adding a column to a huge table will never work. – mBrice1024 Oct 01 '15 at 16:54
Fair enough! The full update process you describe should not be necessary unless the batch update requires null values for some reason (which if all rows are null is unlikely). In MySQL the 2 queries: adding the column and disabling nullable will both take approximately the same amount of time (with or without data in the column). You can save even more resource occupying time by create the column as non-nullable to start with and the rows will be assigned the default value (0, NULL, '', etc.) which can then be replaced by your batch update. – William_Wilson Oct 01 '15 at 17:13

MySQL - is it possible to conditionally run queries?

1 Answers1