使用mySQL变量-DEV365 开发者社区

mySQL变量是一种从行中存储数据的方便方法，因此可以与其他数据行一起计算中。

设置变量

变量必须始终具有预设值。没有预设值，查询仍将运行，但结果将无法给出预期的结果。

可以通过使用 set

来设置变量

也可以使用子查询设置该变量。

SET @last_date = '1970-01-01'

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

设置数据库

出于本文的目的，我正在设置一个数据库，如下所示：

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');

在查询中使用变量

要返回查询中变量的值，只需将变量添加到Select语句

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

这将返回以下数据

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

为变量分配值

使用

完成为变量分配值

@last_date := last_date

：= 语法等于 set

在查询中看起来像

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

每次查询登录一行时，它将在当前表行中使用日期更新@last_date。

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

请注意，两列包含不同的日期。 @last_date正在显示当前存储的值。分配列正在显示要使用下一行要存储的内容。

重要说明：将这些命令放在问题中的顺序。如果在显示值之前设置值，则将更改结果。

如果选择了选择语句中的两个字段，则两个列将返回相同的值。

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

与
不同

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

在计算中使用变量

在查询变得更加复杂之前，最好将变量分配降级到子查询，然后返回您稍后真正需要的字段。

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

现在，我们可以返回计算。

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	天
1	19358
2	1
3	1
4	1
5	1
6	1

变量更复杂的用例

为了展示如何在更复杂的情况下使用变量，我想运行一个报告，向我展示用户在同一位置上连续工作的几天开始和结束日期。

如果他们工作了不同的船只或工作不同的位置，或者有几天的差距，那么它将需要单独的行。

前面输入的数据的最终结果应为

天	start_date	end_date	船只	位置
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

要实现这一目标，我们需要跟踪日期，船只和位置，因此我们可以将它们全部与下一行进行比较，以查看它们是否确实在第二天使用相同的船只和相同的位置。

首先添加这些变量并确保它们返回正确的值，开始构建查询。

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	船只	位置
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

现在，数据看起来正确，我们需要一种方法来将类似的项目进行比较和分组。

为此，我们可以添加一个附加变量（@Group）来存储一个ID，该ID以后可用于将项目分组在一起。

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	船只	位置
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

现在可以使用@Group来添加比较。如果容器，位置和连续日期匹配，则组ID保持不变。如果它们不匹配，请增加组ID。

为此，需要将比较添加到@Group分配

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

如果所有三个比较都是正确的，则括号中的比较将返回1，如果任何比较为false，则会返回0，这将增加组ID。

现在我们可以通过 group_by 进行分组。我们还可以返回该组中最低的start_date和组中最高的end_date，以获取他们工作的日期范围，并连续计算它们在同一容器和相同位置上工作的几天。

最终查询看起来像：

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

将返回

天	start_date	end_date	船只	位置
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

根据您的数据，您可能需要添加订单，以确保行以您查询有意义的顺序返回。