MySql 日期范围查询

[街头小贩]

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

[街头小贩]

现在用的是

SELECT * FROM T1 AS tr WHERE STR_TO_DATE(CONCAT_WS('-',tr.`YEAR`,tr.`MONTH`,tr.`DAY`),'%Y-%m-%e') BETWEEN ? AND ?

还有没有更合适的函数

[mingqing6364]

如果表中记录不多，那就只能这样了。
如果表中有很多记录，且这是一个常用的查询，需要注意这样的写法是无法利用到索引的：
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)

[AHUA1001]

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

[Rotel-刘志东]

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