这个 MYSQL 查询有什么问题

发布于 2024-10-24 12:00:21 字数 821 浏览 1 评论 0原文

    SELECT * FROM event JOIN user ON event.event_user_id=user.user_id
  WHERE ((event.event_date BETWEEN '2011-03-01' AND '2011-04-01' ) 
    AND user.user_id in (SELECT user_id FROM user_map 
      WHERE (user_map.user_id_map=1 AND user_map.user_relation<=7)));

这在 Toad/MYsql 中工作正常,但当我使用 java + mysql 驱动程序中的准备好的语句执行此操作时失败

    SELECT * FROM event JOIN user ON event.event_user_id=user.user_id
  WHERE ((event.event_date BETWEEN ? AND ? ) 
    AND user.user_id in (SELECT user_id FROM user_map 
      WHERE (user_map.user_id_map=? AND user_map.user_relation<=?)));

...

pstmt.setDate(1, <sqlDate>);
pstmt.setDate(2, <sqlDate>);
pstmt.setLong(3, <int>);
pstmt.setLong(4, <int>);

pstmt.executeQuery();
    SELECT * FROM event JOIN user ON event.event_user_id=user.user_id
  WHERE ((event.event_date BETWEEN '2011-03-01' AND '2011-04-01' ) 
    AND user.user_id in (SELECT user_id FROM user_map 
      WHERE (user_map.user_id_map=1 AND user_map.user_relation<=7)));

This is working fine in Toad/MYsql but fails when I do it with prepared statement in java + mysql driver as

    SELECT * FROM event JOIN user ON event.event_user_id=user.user_id
  WHERE ((event.event_date BETWEEN ? AND ? ) 
    AND user.user_id in (SELECT user_id FROM user_map 
      WHERE (user_map.user_id_map=? AND user_map.user_relation<=?)));

...

pstmt.setDate(1, <sqlDate>);
pstmt.setDate(2, <sqlDate>);
pstmt.setLong(3, <int>);
pstmt.setLong(4, <int>);

pstmt.executeQuery();

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

清醇 2024-10-31 12:00:21

它与问题没有密切关系,但查询效率不高,可以改进,

SELECT * 
FROM   event 
       JOIN USER 
         ON event.event_user_id = USER.user_id 
       JOIN user_map 
         ON user_map.user_id = USER.user_id 
            AND user_map.user_id_map = 1
            AND user_map.user_relation<=7 
WHERE  ( event.event_date BETWEEN '2011-03-01' AND '2011-04-01' ) 
GROUP  BY USER.user_id 

您需要

  • (event.event_user_id,event.event_date)
  • (USER.user_id)
  • (user_map.user_id, user_map.user_id_map,user_map.user_relation)上的索引

It is not closely related to problem but the query is not efficient and can be improved

SELECT * 
FROM   event 
       JOIN USER 
         ON event.event_user_id = USER.user_id 
       JOIN user_map 
         ON user_map.user_id = USER.user_id 
            AND user_map.user_id_map = 1
            AND user_map.user_relation<=7 
WHERE  ( event.event_date BETWEEN '2011-03-01' AND '2011-04-01' ) 
GROUP  BY USER.user_id 

you need indexes on

  • (event.event_user_id,event.event_date)
  • (USER.user_id)
  • (user_map.user_id, user_map.user_id_map,user_map.user_relation)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文