How to upgrade a MySQL database using numbered scripts based on a version field 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...
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 &lt; 2 then 
        source 001.sql;
        update service_table set version = 2;
    end if;
    if @version &lt; 2.5 then 
        source 02.sql;
        update service_table set version = 2.5;
    end if;
    if @version &lt; 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.).