如何在MySQL查询中判断日期是否在关联数组并获取对应值

我有如下PHP关联数组：

$a = [ 
  "2023-05-18" => 10.0,
  "2023-07-14" => 2.0,
  "2023-11-01" => 16.0,
  "2023-11-11" => 2.0,
  "2023-12-25" => 8.0,
  "2024-01-01" => 2.0,
  "2024-04-01" => 22.0
];

希望在SQL查询中实现逻辑：当table.date存在于上述数组的键中时，获取对应键的值；否则返回1。可使用IF、CASE WHEN等语法实现。

方法1：CASE WHEN分支拼接 #

这是最直观的实现方式，通过PHP循环数组生成对应的CASE分支：

function buildSqlCondition($dateValues, $dbConn) {
    $caseClauses = [];
    foreach ($dateValues as $date => $value) {
        // 转义处理避免SQL注入
        $escapedDate = "'" . mysqli_real_escape_string($dbConn, $date) . "'";
        $escapedValue = (float)$value;
        $caseClauses[] = "WHEN table.date = $escapedDate THEN $escapedValue";
    }
    $caseStr = implode("\n              ", $caseClauses);
    $sql = "SELECT 
              ... 其他字段 ...,
              CASE 
                $caseStr
                ELSE 1
              END AS calculated_value
            FROM table";
    return $sql;
}

生成后的SQL示例：

SELECT 
  ... 其他字段 ...,
  CASE 
    WHEN table.date = '2023-05-18' THEN 10.0
    WHEN table.date = '2023-07-14' THEN 2.0
    WHEN table.date = '2023-11-01' THEN 16.0
    ... 剩余日期分支 ...
    ELSE 1
  END AS calculated_value
FROM table

方法2：MySQL专属的ELT+FIELD函数组合 #

如果使用MySQL，可以利用FIELD和ELT函数简化映射逻辑：

function buildSqlCondition($dateValues, $dbConn) {
    $dates = array_keys($dateValues);
    $values = array_values($dateValues);
    
    // 转义处理
    $escapedDates = array_map(function($date) use ($dbConn) {
        return "'" . mysqli_real_escape_string($dbConn, $date) . "'";
    }, $dates);
    $escapedValues = array_map(fn($val) => (float)$val, $values);
    
    $dateStr = implode(', ', $escapedDates);
    $valueStr = implode(', ', $escapedValues);
    
    $sql = "SELECT 
              ... 其他字段 ...,
              IF(FIELD(table.date, $dateStr) > 0, ELT(FIELD(table.date, $dateStr), $valueStr), 1) AS calculated_value
            FROM table";
    return $sql;
}

原理：FIELD函数返回日期在列表中的位置，ELT函数根据位置取对应的值；若日期不在列表中，FIELD返回0，此时IF返回1。

方法3：子查询/临时表关联（适用于数据量较大场景） #

当数组数据较多时，可将数组转为子查询临时表，通过JOIN关联实现映射：

function buildSqlCondition($dateValues, $dbConn) {
    $rows = [];
    foreach ($dateValues as $date => $value) {
        $escapedDate = "'" . mysqli_real_escape_string($dbConn, $date) . "'";
        $escapedValue = (float)$value;
        $rows[] = "($escapedDate, $escapedValue)";
    }
    $rowStr = implode(', ', $rows);
    
    $sql = "SELECT 
              t.*,
              COALESCE(m.map_value, 1) AS calculated_value
            FROM table t
            LEFT JOIN (
              SELECT date_col, map_value FROM (VALUES $rowStr) AS temp(date_col, map_value)
            ) m ON t.date = m.date_col";
    return $sql;
}

这种方式逻辑更清晰，数据量大时性能更优，避免了过长的CASE分支。

关键注意事项 #

• SQL注入防护：所有拼接进SQL的变量必须做转义处理，优先使用PDO预处理语句：

  // PDO预处理示例
  function buildSqlCondition($dateValues, $pdo) {
      $placeholders = [];
      $params = [];
      foreach ($dateValues as $date => $value) {
          $placeholders[] = "(?, ?)";
          $params[] = $date;
          $params[] = $value;
      }
      $placeholderStr = implode(', ', $placeholders);
      
      $sql = "SELECT 
                t.*,
                COALESCE(m.map_value, 1) AS calculated_value
              FROM table t
              LEFT JOIN (
                SELECT date_col, map_value FROM (VALUES $placeholderStr) AS temp(date_col, map_value)
              ) m ON t.date = m.date_col";
      
      $stmt = $pdo->prepare($sql);
      $stmt->execute($params);
      return $stmt;
  }
  

• 确保table.date字段类型与数组中的日期格式匹配（如均为DATE类型或字符串类型），避免匹配失败。

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