MySql 日期范围查询

Question

T1表中有`YEAR`,`MONTH`,`DAY`;这三列，现在要进行Date BETWEEN AND查询,如何把这三列转成Date

Answer 1

现在用的是SELECT * FROM T1 AS tr WHERE STR_TO_DATE(CONCAT_WS('-',tr.`YEAR`,tr.`MONTH`,tr.`DAY`),'%Y-%m-%e') BETWEEN ? AND ?还有没有更合适的函数

Answer 2

如果表中记录不多，那就只能这样了。
如果表中有很多记录，且这是一个常用的查询，需要注意这样的写法是无法利用到索引的：
MySQL5.7或以上版本，建议使用计算列，并在计算列上建立索引。
MySQL5.7以下版本，建议改写SQL。/* 创建测试表 */
CREATE TABLE `date` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `y` varchar(255) NOT NULL,
  `m` varchar(255) NOT NULL,
  `d` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ymd1` (`y`,`m`,`d`)
);
/* 插入测试数据 */
INSERT INTO
`date` ( `y`, `m`, `d` )
VALUES
( '2018', '10', '01' ),
( '2018', '10', '02' ),
( '2018', '10', '03' ),
( '2018', '10', '04' ),
( '2018', '10', '05' ),
( '2018', '10', '06' ),
( '2018', '10', '07' ),
( '2018', '10', '08' ),
( '2018', '10', '09' ),
( '2018', '10', '10' ),
( '2018', '10', '01' ),
( '2018', '10', '02' ),
( '2018', '10', '03' ),
( '2018', '10', '04' ),
( '2018', '10', '05' ),
( '2018', '10', '06' ),
( '2018', '10', '07' ),
( '2018', '10', '08' ),
( '2018', '10', '09' ),
( '2018', '10', '10' );
/* 函数拼接字段查询，因为是索引覆盖，所以扫描了整个索引，如果还有其他字段，可能就是全表扫描了 */
EXPLAIN SELECT
*
FROM
date
WHERE
STR_TO_DATE(CONCAT_WS('-', y, m, d),'%Y-%m-%d') BETWEEN '2018-10-01' AND '2018-10-02';
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | date  | index | NULL          | ymd1 | 2301    | NULL |   20 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.02 sec)/* 改写成and连接条件，只扫描了4行，能直接获取需要的数据 */
EXPLAIN SELECT
*
FROM
date
WHERE
y BETWEEN '2018' AND '2018'
AND m BETWEEN '10' AND '10'
AND d BETWEEN '01' AND '02';
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | date  | range | ymd1          | ymd1 | 2301    | NULL |    4 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.03 sec)/* 加上计算列，并在计算列建索引 */
ALTER TABLE `test`.`date` 
ADD COLUMN `ymd` date AS (STR_TO_DATE(CONCAT_WS('-', y, m, d),'%Y-%m-%d')) AFTER `d`,
ADD INDEX `ymd2`(`ymd`);/* 利用计算列查询，也只需要扫描4行 */
EXPLAIN SELECT
date.y,
date.m,
date.d,
date.ymd 
FROM
date 
WHERE
date.ymd BETWEEN '2018-10-01' AND '2018-10-02';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | date  | range | ymd2          | ymd2 | 4       | NULL |    4 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

Answer 3

SELECT
  *
FROM
  T1
WHERE STR_TO_DATE(
    CONCAT(T1.YEAR, T1.MONTH, T1.DAY),
    '%Y%m%d'
  ) BETWEEN ?
  AND ? ;

Answer 4

用concat_ws函数拼接年月日，后to_date转化，貌似没有函数。