mybatis能查到数据复制到sql查不到怀疑是框架本身bug

Question

在mybatisplus 中查到数据，复制到MySQL中却查不到 我怀疑是 mybatisplus 的bug
版本是

com.baomidou mybatis-plus-boot-starter 3.3.2

 MPJLambdaWrapper<BroadcastSchedule> wrapper = new MPJLambdaWrapper();
 wrapper.nested(qw -> qw.eq(BroadcastSchedule::getIsDel,0));
        wrapper.nested(qw ->qw.eq(BroadcastSchedule::getWorkplace,broadcastSchedule.getWorkplace()));
        if(broadcastSchedule.getId()!=null && !broadcastSchedule.getId().isEmpty()){
            wrapper.nested(qw->qw.ne(BroadcastSchedule::getId,broadcastSchedule.getId()));
        }


wrapper.nested(qw -> qw.between(BroadcastSchedule::getRecordingTime,broadcastSchedule.getRecordingTime(),broadcastSchedule.getEndTime())
                .and(qw2 -> qw2.le(BroadcastSchedule::getEndTime,broadcastSchedule.getEndTime()))
        );
        if (broadcastSchedule.getSceneryTime()!=null && !broadcastSchedule.getSceneryTime().equals("")){
            wrapper.or(qw -> qw.between(BroadcastSchedule::getSceneryTime,broadcastSchedule.getSceneryTime(),broadcastSchedule.getEndTime())
                            .and(qw2 -> qw2.le(BroadcastSchedule::getEndTime,broadcastSchedule.getEndTime())));
        }
        if (broadcastSchedule.getRehearsalTime()!=null && !broadcastSchedule.getRehearsalTime().equals("")){
            wrapper.or(qw -> qw.between(BroadcastSchedule::getRehearsalTime,broadcastSchedule.getRehearsalTime(),broadcastSchedule.getEndTime())
                    .and(qw2 -> qw2.le(BroadcastSchedule::getEndTime,broadcastSchedule.getEndTime())));

        }
        if(broadcastSchedule.getDressRehearsalTime()!=null && !broadcastSchedule.getDressRehearsalTime().equals("")){
            wrapper.or(qw -> qw.between(BroadcastSchedule::getDressRehearsalTime,broadcastSchedule.getDressRehearsalTime(),broadcastSchedule.getEndTime())
                    .and(qw2 -> qw2.le(BroadcastSchedule::getEndTime,broadcastSchedule.getEndTime())));
        }
        List<BroadcastSchedule> broadcastSchedules = broadcastScheduleDao.selectJoinList(BroadcastSchedule.class, wrapper);
        if(broadcastSchedules.size()!=0){
            return true;
        }
        return false;

它生成的sql语句和查询数据
、

1是sql语句
2是参数
3是字段
4是数据
我把它复制出来替换参数，然后放到 数据库里面执行，查不到数据，理论上查不到才是正确的

SELECT
    t.id,
    t.department_id,
    t.program_name,
    t.contact_phone,
    t.contact_name,
    t.production_form,
    t.tv_platform,
    t.online_platform,
    t.workplace,
    t.workplace_name,
    t.second_workplace,
    t.departure_time,
    t.scenery_time,
    t.recording_time,
    t.rehearsal_time,
    t.end_time,
    t.dress_rehearsal_time,
    t.vehicle_used,
    t.recording_format,
    t.equipment,
    t.communication_channels,
    t.review_status,
    t.applicant_user_id,
    t.broadcast_platform,
    t.SECURITY,
    t.signalling_mode,
    t.attendances,
    t.json_expands,
    t.created_at,
    t.updated_at,
    t.is_del,
    t1.department_id AS joina_department_id,
    t1.department_name,
    t1.created_at AS joina_created_at,
    t1.updated_at AS joina_updated_at,
    t2.user_id,
    t2.username,
    t2.PASSWORD,
    t2.phone,
    t2.department_id AS joinb_department_id,
    t2.role_id,
    t2.created_at AS joinb_created_at,
    t2.updated_at AS joinb_updated_at,
    t3.id AS joina_id,
    t3.location_name,
    t3.color,
    t3.remake1,
    t3.remake2,
    t3.remake3,
    t3.remake4,
    t3.remake5,
    t3.created_at AS joinc_created_at,
    t3.updated_at AS joinc_updated_at 
FROM
    broadcast_schedule t
    LEFT JOIN departments t1 ON ( t1.department_id = t.department_id )
    LEFT JOIN users t2 ON ( t2.user_id = t.applicant_user_id )
    LEFT JOIN workplace t3 ON ( t3.id = t.workplace ) 
WHERE
    ((
            t.is_del = 0 
            ) 
        AND ( t.workplace = '20231128134155629' ) 
        AND ( t.id <> '20240524151327449' ) --这里已经排除了当前id，是查不到的
        AND (
            t.recording_time BETWEEN '2024-07-01 18:30:00.0' 
            AND '2024-07-01 19:00:00.0' 
        AND ( t.end_time <= '2024-07-01 19:00:00.0' )) 
        OR (
            t.scenery_time BETWEEN '2024-07-01 16:00:00.0' 
            AND '2024-07-01 19:00:00.0' 
        AND ( t.end_time <= '2024-07-01 19:00:00.0' )) 
        OR (
            t.rehearsal_time BETWEEN '2024-07-01 17:00:00.0' 
            AND '2024-07-01 19:00:00.0' 
        AND ( t.end_time <= ' 2024-07-01 19:00:00.0' )) 
        OR (
            t.dress_rehearsal_time BETWEEN '2024-07-01 18:00:00.0' 
        AND '2024-07-01 19:00:00.0' 
    AND ( t.end_time <= '2024-07-01 19:00:00.0' )))

Answer 1

引自免费微信小程序：皆我百晓生

经过分析，看起来MyBatisPlus生成的SQL语句逻辑上没有明显错误。然而，如果你在数据库中手动运行这个查询仍然得不到结果，那么可能存在以下几种情况：

1. 数据问题：确保提供的查询条件（例如broadcastSchedule.getRecordingTime()、broadcastSchedule.getEndTime()等）与数据库中的实际值匹配。时间戳可能因格式问题而无法正确比较。

2. 数据库设置问题：检查MySQL的时区设置是否与应用程序一致。不一致的时间设置可能导致日期/时间比较出错。

3. 源码问题：确保其他地方没有对这些查询条件进行额外的修改或过滤。

4. 数据库缓存：尝试清空数据库缓存，或者在MySQL中使用FLUSH TABLES命令刷新表的数据。

5. 数据库隔离级别：确认数据库事务的隔离级别，不同的隔离级别可能会影响查询结果。

为了进一步排查问题，建议你在MyBatisPlus执行查询之前打印完整的SQL语句，包括所有的参数值，这样可以确保所有传递给数据库的参数都是预期的。这可以通过在你的代码中添加日志输出实现，比如使用Log4j、SLF4J或者其他你喜欢的日志框架。

import org.apache.log4j.Logger;

private static final Logger LOGGER = Logger.getLogger(YourClass.class);

// ...

LOGGER.debug("Generated SQL: {}", mapper.buildSqlString(wrapper));

然后，根据日志输出的SQL语句，确保其与你贴出来的SQL完全一致，并且检查传入的参数值。如果还是有问题，考虑检查数据库的索引设置，看是否存在覆盖这些查询的索引，这可能影响查询效率和结果。

如果以上步骤都不能解决问题，可能是MyBatisPlus的某个特定功能导致的问题，此时可以考虑创建一个最小可复现的测试案例并提交到MyBatisPlus的GitHub仓库作为 issue，这样开发者可以更方便地定位问题。同时，也可以查阅MyBatisPlus的官方文档或社区论坛，看看是否有类似的问题讨论。

请按照上述步骤操作，如果还有疑问或需要进一步的帮助，请随时告诉我。