如何将返回表的MySQL存储过程转换为视图？

是否可行？ #

直接创建带参数的标准视图不可行，因为MySQL的视图不支持传入参数。但可以通过封装逻辑的查询语句结合用户变量，或者创建通用视图后在查询时补充参数与计算逻辑，来实现和原存储过程完全一致的输出效果。

具体实现方案 #

方案1：用用户变量模拟参数，执行封装逻辑的查询 #

先设置与原存储过程对应的参数变量，再执行包含所有逻辑的查询，效果和调用存储过程完全一致：

-- 设置参数变量，对应原存储过程的location_id, service_id, specification_id, entrytime, entrydate
SET @location_id = 51;
SET @service_id = 2;
SET @specification_id = 2;
SET @entrytime = 1130;
SET @entrydate = '2023-08-24';

-- 执行查询，1:1复现存储过程逻辑
SELECT r.deviceid
FROM reservations r
JOIN service_location_device d ON r.deviceid = d.deviceid
JOIN (
    -- 计算starttime和endtime
    SELECT 
        -- 取整entrytime到下一个60的倍数得到starttime
        CASE WHEN @entrytime % 60 > 0 THEN @entrytime + (60 - (@entrytime % 60)) ELSE @entrytime END AS starttime,
        -- 计算endtime：starttime + slot_length（slot_length为空则等于starttime）
        CASE WHEN slot_length IS NOT NULL THEN 
            CASE WHEN @entrytime % 60 > 0 THEN @entrytime + (60 - (@entrytime % 60)) ELSE @entrytime END + slot_length
        ELSE 
            CASE WHEN @entrytime % 60 > 0 THEN @entrytime + (60 - (@entrytime % 60)) ELSE @entrytime END
        END AS endtime
    FROM (
        -- 获取对应slot_length，LIMIT 1和原存储过程逻辑一致
        SELECT timeslot_length AS slot_length
        FROM service_location_device
        WHERE locationidfk = @location_id
          AND serviceidfk = @service_id
          AND specificationid = @specification_id
        LIMIT 1
    ) AS slot_sub
) AS time_calc
WHERE 
    -- 时间范围匹配逻辑完全照搬原存储过程
    ((r.slot_start >= @entrytime AND r.slot_start <= time_calc.endtime)
     OR (r.slot_end >= @entrytime AND r.slot_end <= time_calc.endtime))
  AND d.locationidfk = @location_id
  AND d.serviceidfk = @service_id
  AND d.specificationid = @specification_id
  AND r.is_cancelled = 0
  AND r.slot_date = @entrydate;

方案2：创建通用视图，查询时补充参数与逻辑 #

先创建包含基础关联关系的视图：

CREATE VIEW ReservedDevicesView AS
SELECT 
    r.deviceid,
    r.slot_start,
    r.slot_end,
    r.is_cancelled,
    r.slot_date,
    d.locationidfk,
    d.serviceidfk,
    d.specificationid,
    d.timeslot_length
FROM reservations r
JOIN service_location_device d ON r.deviceid = d.deviceid;

查询视图时传入参数并补充计算逻辑：

SET @location_id = 51;
SET @service_id = 2;
SET @specification_id = 2;
SET @entrytime = 1130;
SET @entrydate = '2023-08-24';

SELECT DISTINCT deviceid
FROM ReservedDevicesView
CROSS JOIN (
    SELECT 
        CASE WHEN @entrytime % 60 > 0 THEN @entrytime + (60 - (@entrytime % 60)) ELSE @entrytime END AS starttime
) AS start_calc
CROSS JOIN (
    -- 处理slot_length为空的情况，默认取0
    SELECT COALESCE(
        (SELECT timeslot_length 
         FROM service_location_device
         WHERE locationidfk = @location_id
           AND serviceidfk = @service_id
           AND specificationid = @specification_id
         LIMIT 1), 0) AS slot_length
) AS slot_calc
WHERE 
    locationidfk = @location_id
    AND serviceidfk = @service_id
    AND specificationid = @specification_id
    AND is_cancelled = 0
    AND slot_date = @entrydate
    AND ((slot_start >= @entrytime AND slot_start <= start_calc.starttime + slot_calc.slot_length)
         OR (slot_end >= @entrytime AND slot_end <= start_calc.starttime + slot_calc.slot_length));

关键说明 #

两种方案均完全复现原存储过程的所有逻辑：包括entrytime的取整规则、slot_length的获取逻辑、endtime的计算方式，以及最终的过滤条件。方案1更贴近原存储过程的封装逻辑，执行效率与原存储过程相当；方案2的视图通用性更强，但查询时需要额外补充计算逻辑。

内容的提问来源于stack exchange，提问作者Vanessa