How to upgrade a MySQL database using numbered scripts based on a version field

Question

I have a database that contains a version table with one value stored in it which is the database version number.

I would like to be able to update the database version automatically using numbered scripts.

If the database is version "6" and I have a dir of scrips numbered inconsistently as per the below:

001.sql, 02.sql, 3.sql, 4script.sql, 5.sql, 6.upgrade.sql, 7.sql, 8.data.sql, 009.updated.sql

What is the best way to only run scripts which are numbered greater than the version number and to do this in sequence?

Should I use an SQL script or create a bash script?

I am a little stuck on how to approach this so any pointers or advice will be much appreciated...

• Philᵀᴹ: Well, I'd start by renaming the files for consistency - you're asking for trouble otherwise.

Answer 1

Create "main script" which detects current DB version and shells only scripts proper for it. For example, it can look like

drop procedure if exists main_proc;
delimiter @@;
create procedure main_proc
 select version into @version from service_table;
 if @version < 2 then 
 source 001.sql;
 update service_table set version = 2;
 end if;
 if @version < 2.5 then 
 source 02.sql;
 update service_table set version = 2.5;
 end if;
 if @version < 3 then 
 source 3.sql;
 update service_table set version = 3;
 end if;
 -- and so on
 end; 
@@;
delimiter ;
call main_proc;
drop procedure main_proc;

Don't forget error handlers (no script file or access error, error during script execution, etc.).