Last week I found myself needing to quickly seed a column in a database with incremental datetime values. This is not something I’d ever needed to do before in MySQL, but the solution I found was beautifully simple. So I am going to share here how I did this and show you how you can easily populate a column for existing records with an incremental value.
To start with let’s look at a simple example of incrementing an integer value.
Simple integer incrementation
In MySQL we can create user-defined variables like @i. To set a value to a variable we use the assignment operator :=. So we can initiate a variable like this:-
SET @i:=0;Then we can use our variable to populate a column in our database table. So if we have a table foo and a column bar we can do:-
UPDATE foo SET bar = @i:=(@i+1) WHERE 1=1;Here we’re iterating the value of our variable for each record that gets updated by our query, @i:=(@i+1). So the first record updated will have the value 1 stored against bar, the second record will have 2, and so on.
Simple!
Datetime incrementation
So for my problem I had a tasks table with a notbefore column that I need to seed with iterative datetime values. Using the process we’ve just looked at for iterating an integer value, we can do something similar but incrementing a datetime by 5 minute intervals:-
SET @i:=NOW();
UPDATE tasks SET notbefore = @i:=(@i+Interval 5 minute) WHERE 1=1;There you have it, a simple and quick approach to iteratively seeding a column.