mysql - 变量未将正确的数据或任何数据传递到查询中

Question

日期变量未在存储过程中正确传递。这部分返回 null

(CONVERT_TZ( (FROM_UNIXTIME(@fromDate/1000)),''UTC'', @timeZone) )AS startingDate, 
(CONVERT_TZ( (FROM_UNIXTIME(@toDate/1000)),''UTC'', @timeZone) ) AS endingDate,

这些日期显示为空

l1.started BETWEEN @startDate AND @endDate

我缺少的是值没有传递到查询中

CREATE PROCEDURE reportFreeCoolingTrackerTest (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
             SET mylogID = (SELECT logID FROM logs l WHERE l.details LIKE 'FCT%');
             SET @tbl = CONCAT('log',mylogID);

 set @q1 = CONCAT('SELECT 
    i.details,l1.item31985,l1.item31987,
    ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
    ((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , '':'' ) +1))) AS cost,
    (((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , '':'' ) +1)))
    *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600)) AS costT,
      time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
      time_to_sec(timediff(@endDate, @startDate)) / 3600 AS totalTimeRange,
    (CONVERT_TZ( (FROM_UNIXTIME(@fromDate/1000)),''UTC'', @timeZone) )AS startingDate, 
    (CONVERT_TZ( (FROM_UNIXTIME(@toDate/1000)),''UTC'', @timeZone) ) AS endingDate,DATABASE() AS databaseName,
     CASE
        when l1.activityId = t2.activityId THEN 1
        ELSE 0
    END AS errorCheck, t2.completed AS errorDay,a.scheduleType,@lgName,@mylogID  


  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN  ',  @tbl,'  l1 ON a.activityId = l1.activityId ');

   set @q2 = CONCAT(@q1, 'INNER JOIN ', @tbl);

   SET @q3 = CONCAT(@q2, ' t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE ''%KW%''
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE ''%KW%''
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started
        BETWEEN @startDate
            AND @endDate
     ORDER BY l1.recordId,l1.started');   

    PREPARE stmt FROM @q3;

     EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
    END //

DELIMITER ;

Answer 1

您的代码存在很多问题。以下是主要内容:

1. 您将过程输入参数和局部变量与用户( session )变量混合在一起。例如。您有一个输入参数 fromDate 但您在查询 @fromDate 中引用它，这是用户( session )变量，与参数无关。这就是为什么您得到 NULL 而不是实际值。

2. 您无需将查询字符串与变量值连接起来，而是将变量名称保留为字符串文字的一部分。

3. 在查询字符串中插入日期字符串文字(例如变量 fromDate 和 startDate)时，应使用引号引起来。 timeZone 值也是如此。

4. 在查询字符串中，您引用的是 @lgName 变量，该变量很可能不包含任何值。即使确实如此，您也应该通过另一个过程参数来完成此操作，而不是通过用户( session )变量传递它。

5. 这部分

   SET mylogID = (SELECT logID FROM logs l WHERE l.details LIKE 'FCT%');
   

   应该返回唯一的值，否则会破坏你的代码。使用LIKE可以轻松生成多行。也就是说，要么应用LIMIT 1(或聚合函数，例如MIN()或MAX)，要么使用基于列的条件重写它具有 PK 或 UNIQE 约束。

6. fromDate、startDate、mylogID 是局部变量。但由于某种原因，您使用用户变量 @tbl 来保存表名称值。放弃它，只需在构建查询字符串时连接 'log' 和 mylogID 值即可。

话虽这么说，你的程序可能看起来像这样

DELIMITER$$
CREATE PROCEDURE reportFreeCoolingTrackerTest (
  IN fromDate VARCHAR (50),
  IN toDate   VARCHAR (50),
  IN timeZone VARCHAR (50)
)
BEGIN
  DECLARE startDate VARCHAR (50);
  DECLARE endDate   VARCHAR (50);
  DECLARE mylogID   INT;

  SET startDate = FROM_UNIXTIME(fromDate/1000);
  SET endDate   = FROM_UNIXTIME(toDate/1000);
  SET mylogID   = 1; -- (SELECT logID FROM logs WHERE details LIKE 'FCT%' LIMIT 1);

  SET @sql = NULL;
  SET @sql = CONCAT(
  'SELECT i.details,l1.item31985,l1.item31987,
    ((l1.item31985 - l1.item31987) * (time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
    ((l1.item31985 - l1.item31987) * (substr(l.details, instr(l.details , '':'' ) +1))) AS cost,
    (((l1.item31985 - l1.item31987) * (substr(l.details, instr(l.details , '':'' ) +1)))
    *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600)) AS costT,
      time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
      time_to_sec(timediff(''', endDate, ''', ''', startDate, ''')) / 3600 AS totalTimeRange,
    CONVERT_TZ(''', startDate, ''', ''UTC'', ''', timeZone, ''') AS startingDate, 
    CONVERT_TZ(''', endDate, ''', ''UTC'', ''', timeZone, ''') AS endingDate,
    DATABASE() AS databaseName,
    CASE WHEN l1.activityId = t2.activityId THEN 1 ELSE 0 END AS errorCheck, 
    t2.completed AS errorDay,
    a.scheduleType,
    @lgName,
    ', mylogID, '
  FROM logs l 
       INNER JOIN groups g ON g.groupId = l.groupId
       LEFT JOIN groups g1 ON g.parentId = g1.groupId
       LEFT JOIN groups g2 ON g1.parentId = g2.groupId
       LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
       INNER JOIN activities a ON l.logId = a.logId 
       INNER JOIN log', mylogID, ' l1 ON a.activityId = l1.activityId
       INNER JOIN log', mylogID, ' t2 ON t2.recordId = l1.recordid + 1
       INNER JOIN items i ON l.logId = i.logId AND i.name LIKE ''%KW%''
       INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE ''%KW%''
  WHERE i.itemID = 31985  AND  l1.activityId = 1257
    AND l1.started BETWEEN ''', startDate, ''' AND ''', endDate, '''
  ORDER BY l1.recordId,l1.started');   

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

这里是SQLFiddle 演示显示最终查询字符串而不是执行它