msql 同一语句查询结果顺序不一致

0 0

msql 同一语句查询结果顺序不一致0

比较复杂的查询,执行多次, 结果顺序不一致.
语句如下:

SELECT DISTINCT a.id AS id, c.id as cid, c.p
                a.guid AS guid,
                a.channelId,
                a.contentType,
                a.catchup,
                a.ovpContentId AS ovpContentId,
                a.rating AS rating,
                a.state AS state,
                a.releaseYear AS releaseYear,
                a.publishDate AS publishDate,
                a.expiryDate AS expiryDate,
                a.startTime AS startTime,
                a.endTime AS endTime,
                a.restriction AS restriction,
                a.hitCount AS hitCount,
                a.operator AS operator,
                c.sequence AS sequence,
                a.createTime AS createTime,
                a.updateTime AS updateTime,
                a.status AS status,
                a.contentType AS contentType,
                a.channelId AS channelId,
                a.catchup AS catchup,
                a.episode AS episode,
                a.episodeGroup AS episodeGroup,
                (SELECT ifnull(count(*), 0)
                   FROM product_content pc
                  WHERE pc.productId = -1 AND pc.contentId = a.id)
                   AS signAnonymous
  FROM content a, content_properties b, category_content c
 WHERE     a.id = b.contentId
       AND b.lang = 'en-gb'
       AND a.contentType = 'vod'
       AND a.state = 'completed'
       AND (a.episode IS NULL OR a.episode = 0)
       AND a.status = 0
       AND a.publishDate <= now()
       AND a.expiryDate >= now()
       AND a.id IN
              (SELECT DISTINCT pc.contentId
                 FROM product_content pc, product p
                WHERE     pc.productId = p.id
                      AND p.state = 'reviewed'
                      AND p.status = 0
                      AND p.publishDate <= now()
                      AND p.expiryDate >= now()
                      AND pc.status = 0)
       AND a.id = c.contentId
       AND c.categoryId = 9
ORDER BY c.sequence IS NOT NULL, c.sequence DESC, c.pinTime DESC
 LIMIT 1, 10

排序情况:

ORDER BY c.sequence IS NOT NULL, c.sequence DESC, c.pinTime DESC

个人理解: 先sequence 不为空排序, 然后以sequence 倒序排序, 再以pinTime排序.

查询记录中,sequence 大小相等, pinTime 都是null.

所以其实order by sequence无效. 不知为什么查询结果不一致?

通过explaion, 结果如下:

注: 本查询是,通过java程序调用, 从控制台,打印出Id发现顺序不一致. 通过mysql客户端直接执行sql 未发现结果不一致.

问题: 在mysql中, 同一sql,是否会出现查询结果顺序不一致情况?

耗费将近一下午,在google 上,未找到此方面的知识,不知各位前辈是否遇到过次种问题.

MySQL explaion

2013年10月08日 18:12

qdpurple
1
0 0 1

查看图片附件

2个答案按时间排序按投票排序

0 0

debug 一下你的 java 代码，看看哪里的问题！

2013年10月09日 10:24

994582796
30
0 0 1

添加评论

0 0

既然客户端没问题，能发下java代码吗

2013年10月08日 18:27

zhuweisyyc
50
0 0 3

添加评论

已解决问题

未解决问题

排行榜

查看全部排名>>

msql 同一语句查询结果顺序不一致0

2个答案按时间排序按投票排序

相关推荐

相关推荐

已解决问题

未解决问题

排行榜

msql 同一语句查询结果顺序不一致0

2个答案 按时间排序 按投票排序

相关推荐

相关推荐

已解决问题

未解决问题

排行榜

2个答案按时间排序按投票排序