MySQL发现诡异情况，是我写SQL的姿势不对吗！关于日期匹配和随机月份？

Question

出现怪异的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');

Answer 1

你现在这这样的 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';

Answer 2

where 条件是每条记录执行一次, 所以每次的rand()生成的值都不相同.

可以如下试试

set @date = DATE_FORMAT(DATE_ADD('2023-11-01',INTERVAL FLOOR(RAND() * DATEDIFF (CURDATE(), '2023-11-01')) DAY),'%Y-%m');
select * from teacher where @date = DATE_FORMAT(create_time, '%Y-%m');

但最好是使用 between 来当条件