MySQL发现诡异情况,是我写SQL的姿势不对吗!关于日期匹配和随机月份?
出现怪异的SQL如下:
SELECT *
FROM teacher
WHERE
DATE_FORMAT(DATE_ADD('2023-11-01',INTERVAL FLOOR(RAND() * DATEDIFF (CURDATE(), '2023-11-01')) DAY),'%Y-%m') = DATE_FORMAT(create_time, '%Y-%m');
原始需求:查询从给定月份到现在的时间段内,查询随机某月的数据。
当前现状:根据前面提供的SQL查出来的数据,看条目数应该是对的,但是具体的数据却不对。有没有大神看一下问题出在哪里,亦或还有否更好的思路。
创建表
create table `teacher` (
`id` int (11),
`name` varchar (96),
`create_time` datetime
);
插入数据
insert into `teacher` (`id`, `name`, `create_time`) values('10','张三','2023-11-17 09:34:23');
insert into `teacher` (`id`, `name`, `create_time`) values('12','李四','2023-10-21 09:34:55');
insert into `teacher` (`id`, `name`, `create_time`) values('13','王五','2023-12-08 09:35:13');
insert into `teacher` (`id`, `name`, `create_time`) values('14','赵六','2023-12-28 09:35:48');
insert into `teacher` (`id`, `name`, `create_time`) values('15','孙七','2024-01-11 09:36:31');
insert into `teacher` (`id`, `name`, `create_time`) values('16','钱八','2024-01-25 09:38:15');
insert into `teacher` (`id`, `name`, `create_time`) values('17','老九','2024-03-07 09:38:53');
insert into `teacher` (`id`, `name`, `create_time`) values('18','老十','2024-03-10 09:39:35');
insert into `teacher` (`id`, `name`, `create_time`) values('19','十一','2024-03-11 09:41:36');
回复
1个回答

test
2024-06-20
你现在这这样的 SQL ,where 条件中,随机日期范围的那一部分,在每次 where 的时候都会执行一次 RAND ,所以就会有问题,要解决这个问题,那就要使得这个 RAND 只执行一次,如果是在 MySQL 8 中,可以使用 With 语句。
with mo1 as (select DATE_FORMAT(DATE_ADD('2023-11-01', INTERVAL FLOOR(RAND() * DATEDIFF(CURDATE(), '2023-11-01')) DAY),
'%Y-%m') as month)
SELECT *
FROM teacher
join mo1 on mo1.month = DATE_FORMAT(create_time, '%Y-%m')
这样,就可以获得预期的结果了。但是,这样并不好。
- 1、对于这个查询条件,你完全可以在代码中进行生成,从外部传进来,而不必要使用 SQL 语句,这样在低版本中也可以使用,代码也更好读。
- 2、你在对查询列使用了 函数
DATE_FORMAT(create_time, '%Y-%m')
这样将会导致 MySQL 在查询数据的时候,无法使用到索引,如果数据量大的话,查询就会很慢。
虽然在 MySQL 8 中,你可以使用 函数索引,来创建索引从而提高效率。
alter table teacher add index idx_month((DATE_FORMAT(create_time, '%Y-%m')));
但是这样也不是很好的办法,更建议的是,从外部传入的参数,把日期补全,把查询改成 between 的方式,再直接为 create_time 创建索引。
SELECT *
FROM teacher
where create_time between '2024-01-01 00:00:00' and '2024-01-31 23:59:59';
# 或者
SELECT *
FROM teacher
where create_time >= '2024-01-01 00:00:00'
and create_time < '2024-02-01 00:00:00';
回复

适合作为回答的
- 经过验证的有效解决办法
- 自己的经验指引,对解决问题有帮助
- 遵循 Markdown 语法排版,代码语义正确
不该作为回答的
- 询问内容细节或回复楼层
- 与题目无关的内容
- “赞”“顶”“同问”“看手册”“解决了没”等毫无意义的内容