Using MySQL Variables

MySQL variables are a handy way to store data from a row, so that it can be used in calculations with other rows of data.

Setting Variables

Variables must always have a preset value. Without the preset value, the query will still run, but the results will not give the expected outcome.

Variables can be set by using SET

The variable can also be set using a subquery.

SET @last_date = '1970-01-01'

SELECT * FROM seaservice,(SELECT @last_date := '1970-01-01') init

Setting Up Your Database

For the purposes of this article I am setting up a database as follows:

CREATE TABLE `seaservice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `vessel` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `seaservice` (`id`, `user_id`, `vessel`, `position`, `start_date`, `end_date`) VALUES
(1, 1,  1,  1,  '2023-01-01 00:00:00',  '2023-01-01 00:00:00'),
(2, 1,  1,  1,  '2023-01-02 00:00:00',  '2023-01-02 00:00:00'),
(3, 1,  2,  1,  '2023-01-03 00:00:00',  '2023-01-03 00:00:00'),
(4, 1,  1,  2,  '2023-01-04 00:00:00',  '2023-01-04 00:00:00'),
(5, 1,  1,  2,  '2023-01-05 00:00:00',  '2023-01-05 00:00:00'),
(6, 1,  2,  1,  '2023-01-06 00:00:00',  '2023-01-06 00:00:00');

Using A Variable In A Query

To return the value of a variable in a query, just add the variable to the select statement

SELECT id,@last_date FROM seaservice, 
(SELECT @last_date := '1970-01-01') init

This will return the following data

id	@last_date
1	1970-01-01
2	1970-01-01
3	1970-01-01
4	1970-01-01
5	1970-01-01
6	1970-01-01

Assigning Values To Variables

Assigning a value to a variable is done using

@last_date := last_date

the := syntax is equivalent to SET

In a query this would look like

SELECT id,@last_date,@last_date := end_date FROM seaservice, 
(SELECT @last_date := '1970-01-01') init

Each time the query hits a row, it will update the @last_date with the date in the current table row.

id	@last_date	@last_date := end_date
1	1970-01-01	2023-01-01 00:00:00
2	2023-01-01 00:00:00	2023-01-02 00:00:00
3	2023-01-02 00:00:00	2023-01-03 00:00:00
4	2023-01-03 00:00:00	2023-01-04 00:00:00
5	2023-01-04 00:00:00	2023-01-05 00:00:00
6	2023-01-05 00:00:00	2023-01-06 00:00:00

Note that the two columns contain different dates. The @last_date is displaying the currently stored value. The assigning column is displaying what is going to be stored for the next row to use.

Important Note: The order you put these in matters. If you set the value before the displaying of the value, then it will change the outcome.

Both columns will return the same value if the two fields in the select statement are swapped.

SELECT id,@last_date,@last_date := end_date FROM seaservice, 
(SELECT @last_date := '1970-01-01') init

Is not the same as

SELECT id,@last_date := end_date,@last_date FROM seaservice, 
(SELECT @last_date := '1970-01-01') init

Using The Variables In Calculations

Before the query get more complex, it can be a good idea to relegate the variable assignment to a sub query and just return the fields you really need later.

SELECT id,last_date,assigned_date 
FROM (SELECT id,@last_date last_date,
@last_date := end_date assigned_date 
FROM seaservice, 
(SELECT @last_date := '1970-01-01') init)t

Now instead of returning the date values, we can return a calculation.

SELECT id,DATEDIFF(assigned_date,last_date) as days
FROM (SELECT id,@last_date last_date,@last_date := end_date assigned_date 
FROM seaservice, 
(SELECT @last_date := '1970-01-01') init)t

id	days
1	19358
2	1
3	1
4	1
5	1
6	1

A More Complex Use Case For Variables

For a demonstration of how variables can be used in a more complex scenario, I want to run a report that shows me the start and end dates of days that a user worked consecutively, on the same vessel, in the same position.

If they worked a different vessel or worked a different position, or there was a gap of days, then it will need to be a separate row.

The end result for the data entered earlier should be

days	start_date	end_date	vessel	position
2	2023-01-01 00:00:00	2023-01-02 00:00:00	1	1
1	2023-01-03 00:00:00	2023-01-03 00:00:00	2	1
2	2023-01-04 00:00:00	2023-01-05 00:00:00	1	2
1	2023-01-06 00:00:00	2023-01-06 00:00:00	2	1

To achieve this we would need to keep track of the date, the vessel and the position, so we can compare them all to the next row to see if they really did work the same vessel and same position the next day.

Start building the query by first adding those variables in and making sure they are returning correct values.

SELECT   last_type,last_position,last_date,start_date,
end_date, vessel, position 
FROM ( 
  SELECT   @last_type last_type,@last_position last_position ,
@last_date last_date,start_date, 
end_date,position, vessel, 
           @last_type := vessel, 
           @last_position := position, 
           @last_date := end_date 
  FROM     seaservice, ( 
             SELECT  
                    @last_type := NULL, 
                    @last_position := NULL, 
                    @last_date := NULL 
           ) init 
) t

last_type	last_position	last_date	start_date	end_date	vessel	position
NULL	NULL	NULL	2023-01-01 00:00:00	2023-01-01 00:00:00	1	1
1	1	2023-01-01 00:00:00	2023-01-02 00:00:00	2023-01-02 00:00:00	1	1
1	1	2023-01-02 00:00:00	2023-01-03 00:00:00	2023-01-03 00:00:00	2	1
2	1	2023-01-03 00:00:00	2023-01-04 00:00:00	2023-01-04 00:00:00	1	2
1	2	2023-01-04 00:00:00	2023-01-05 00:00:00	2023-01-05 00:00:00	1	2
1	2	2023-01-05 00:00:00	2023-01-06 00:00:00	2023-01-06 00:00:00	2	1

Now that the data looks like it is correct, we need a way to compare and group like items together.

For this we can add an additional variable (@group) to store an ID that could later be used to group items together.

SELECT   group_by,last_type,last_position,last_date,start_date,
end_date, vessel, position FROM ( 
  SELECT   @group group_by,@last_type last_type,
@last_position last_position ,@last_date last_date,
start_date, end_date,position, vessel, 
           @group     := @group + 1,
           @last_type := vessel, 
           @last_position := position, 
           @last_date := end_date 
  FROM     seaservice, ( 
             SELECT  
                    @group     := 0,
                    @last_type := NULL, 
                    @last_position := NULL, 
                    @last_date := NULL 
           ) init 
) t

group_by	last_type	last_position	last_date	start_date	end_date	vessel	position
0	NULL	NULL	NULL	2023-01-01 00:00:00	2023-01-01 00:00:00	1	1
1	1	1	2023-01-01 00:00:00	2023-01-02 00:00:00	2023-01-02 00:00:00	1	1
2	1	1	2023-01-02 00:00:00	2023-01-03 00:00:00	2023-01-03 00:00:00	2	1
3	2	1	2023-01-03 00:00:00	2023-01-04 00:00:00	2023-01-04 00:00:00	1	2
4	1	2	2023-01-04 00:00:00	2023-01-05 00:00:00	2023-01-05 00:00:00	1	2
5	1	2	2023-01-05 00:00:00	2023-01-06 00:00:00	2023-01-06 00:00:00	2	1

The @group can now be used to add a comparison. If the vessel, position and consecutive dates match up, the group ID stays the same. If they don't match , increment the Group ID.

To do this, the comparisons needs to be added to the @group assignment

  @group     := @group + 1 - ( 
  vessel      <=> @last_type 
  AND position <=> @last_position 
  AND start_date <=> @last_date + INTERVAL 1 DAY 
  ) group_by

The comparisons in the parentheses will return 1 if all three comparisons are true and 0 if any of the comparisons are false, which will increment the group ID.

And now we can group by group_by. We can also return the lowest start_date in the group and the highest end_date in the group to get the date range they worked, and calculate the number of days in a row they worked on the same vessel and same position.

The final query will look like:

SELECT DATEDIFF(max(end_date),min(start_date))+ 1 as days, 
MIN(start_date) as start_date, MAX(end_date) as end_date, 
vessel, position FROM ( 
  SELECT   start_date, end_date,position, vessel, 
           @group     := @group + 1 - ( 
           vessel      <=> @last_type 
           AND position      <=> @last_item 
           AND start_date <=> @last_date + INTERVAL 1 DAY 
           ) group_by, 
           @last_type := vessel, 
           @last_item := position, 
           @last_date := end_date 
  FROM     seaservice, ( 
             SELECT @group     := 0, 
                    @last_type := NULL, 
                    @last_item := NULL, 
                    @last_date := NULL 
           ) init 

) t GROUP BY group_by

Which will return

days	start_date	end_date	vessel	position
2	2023-01-01 00:00:00	2023-01-02 00:00:00	1	1
1	2023-01-03 00:00:00	2023-01-03 00:00:00	2	1
2	2023-01-04 00:00:00	2023-01-05 00:00:00	1	2
1	2023-01-06 00:00:00	2023-01-06 00:00:00	2	1

Depending on your data, you may need to add ORDER BY to ensure the rows are returning in an order that makes sense for your query.

Blog