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:-
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.
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.