If conditional in SQL Script for Mysql
I just wrap my SQL script in a procedure, where conditional code is allowed. If you'd rather not leave the statements lying around, you can drop the procedure when you're done. Here's an example:
delimiter //create procedure insert_games() begin set @platform_id := (select id from platform where name = 'Nintendo DS'); -- Only insert rows if the platform was found if @platform_id is not null then insert into game(name, platform_id) values('New Super Mario Bros', @platform_id); insert into game(name, platform_id) values('Mario Kart DS', @platform_id); end if;end;//delimiter ;-- Execute the procedurecall insert_games();-- Drop the proceduredrop procedure insert_games;
If you haven't used procedures, the "delimiter" keyword might need some explanation. The first line switches the delimiter to "//" so that we can include semi-colons in our procedure definition without MySQL attempting to interpret them yet. Once the procedure has been created, we switch the delimiter back to ";" so we can execute statements as usual.
After doing some research I think I may have found a way to work around this. I was looking for a way to verify if a script had already executed against a target database. This will be primarily for version control of my databases. I have a table created to keep track of the scripts that have been executed and wanted some flow inside my scripts to check that table first before execution. While I have not completely solved the problem yet I have created a simple script that basically does what I need, I just need to wrap the DDL into the selects based on the value of the variables.
step 1 - Setup a bit variable to hold the resultstep 2 - do your select and set the variable if the result is foundstep 3 - Do what you need to do on false resultstep 4 - Do what you need to do on true result
Here is the example script
set @schemachangeid = 0;
select @schemachangeid := 1 from SchemaChangeLog where scriptname = '1_create_tables.sql';
select 'scriptalreadyran' from dual where @schemachangeid = 1;
select 'scriptnotran' from dual where @schemachangeid = 0;
I also recognize this is an old thread but maybe this will help someone out there trying to do this kind of thing outside of a stored procedure like me.