date_format 中的 MySQL 串联列

Question

我有这个表:table1，和四个变量 $START_MONTH $START_YEAR $END_MONTH $END_YEAR 其中返回:月:1，年:2014 和 2015 年 3

+----+-----+-------+------+-------------+
| ID | DAY | MONTH | YEAR | DESCRIPTION |
+----+-----+-------+------+-------------+
| 1  | 1   |   1   | 2014 | Product 1   |
| 2  | 1   |   2   | 2015 | Product 2   |
| 3  | 2   |   3   | 2014 | Product 1   |
| 4  | 10  |   1   | 2015 | Product 3   |
| 5  | 9   |   5   | 2015 | Product 3   |
+----+-----+-------+------+-------------+

SELECT 
  DATE_FORMAT (CONCAT (DAY,' ', MONTH,' ',YAR), GET_FORMAT(DATE,'EUR')) AS NEW_DATA,
  DESCRIPTION

FROM table1

WHERE
NEW_DATA BETWEEN $START_MONTH . $START_YEAR AND $END_MONTH . $END_YEAR

我需要返回1.2014和3.2015之间包含的行。谢谢!

Answer 1

在 MySQL 中，您可以按组合列进行过滤:

SELECT *
FROM table1
WHERE (`YEAR`, `MONTH`) >= (2014,1)
  AND (`YEAR`, `MONTH`) <= (2015,3)

http://sqlfiddle.com/#!9/87000/2

更新:正如 Strawberry 所说，MySQL 不会在这种情况下使用索引。但这并不是说不可能——MySQL 只是缺少这种优化。据我所知，PostgreSQL 可以很好地进行这种索引范围检查。所以我们希望MySQL/MariaDB将来能够支持它。

但是，如果您遇到性能问题，您可以为年份范围添加冗余条件:

WHERE (`YEAR`, `MONTH`) >= (2014,1)
  AND (`YEAR`, `MONTH`) <= (2015,3)
  AND `YEAR` >= 2014
  AND `YEAR` <= 2015

这样，MySQL 将首先使用 YEAR, MONTH 或 YEAR, MONTH, DAY (或任何以 YEAR 开头的索引)。

我知道的所有替代方案可读性都不是很好，而且它们都不能使用索引:

WHERE DATE(CONCAT_WS('-', `YEAR`, `MONTH`, 1)) >= '2014-01-01'
  AND DATE(CONCAT_WS('-', `YEAR`, `MONTH`, 1)) <= '2015-03-01'

或者

WHERE (`YEAR` = 2014 AND `MONTH` >= 1)
   OR (`YEAR` = 2015 AND `MONTH` >= 3)
   OR (`YEAR` > 2014 AND `YEAR` < 2015)

如果您争分夺秒，您还可以创建 indexed generated (virtual) column (MySQL 5.7) :

`DATE` AS STR_TO_DATE(CONCAT_WS('-', `YEAR`, `MONTH`, `DAY`),'%Y-%m-%d') VIRTUAL KEY

查询如下:

WHERE `DATE` >= '2014-01-01'
  AND `DATE`  < '2015-04-01'