P.Linux Laboratory

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/program/mysql_cast_ifnull_problem.html

关于MySQL的一个CAST和IFNULL函数诡异问题，不明原因，记录一下，明天继续分析。

这个是WP-PostView插件的一段SQL：

SELECT p.ID, p.post_title,
IFNULL( CAST( pm.meta_value AS UNSIGNED ) , 0 ) AS views
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm ON pm.post_id = p.ID
AND pm.meta_key =  "views"
WHERE p.post_date <  "2010-03-05 22:19:55"
AND p.post_status =  "publish"
AND p.post_type =  "post"
AND p.post_password =  ""
ORDER BY views DESC LIMIT 5

在我的数据库上查询结果非常诡异：

ID post_title views
10 [论文]对简易几何机械化证明的进一步研究 9
32 页面总算基本修正好了 9
51 一个很好玩的钢琴Flash游戏 9
60 修改友情链接的排列方式 9
93 UML之父——Ivar Jacobson介紹SMART方法 9

选出来的views列全部是9，不知道为什么。

如果我去掉IFNULL，

SELECT p.ID, p.post_title, 
CAST( pm.meta_value AS UNSIGNED ) AS views
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm ON pm.post_id = p.ID
AND pm.meta_key =  "views"
WHERE p.post_date <  "2010-03-05 22:19:55"
AND p.post_status =  "publish"
AND p.post_type =  "post"
AND p.post_password =  ""
ORDER BY views DESC

得到的结果将是对的，
如果去掉CAST，

SELECT p.ID, p.post_title, IFNULL( pm.meta_value, 0 ) AS views
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm ON pm.post_id = p.ID
AND pm.meta_key =  "views"
WHERE p.post_date <  "2010-03-05 22:19:55"
AND p.post_status =  "publish"
AND p.post_type =  "post"
AND p.post_password =  ""
ORDER BY views DESC LIMIT 5

得到的就是错的，并且只有2位数一下的结果，2位数以上的结果出不来，但不是9或者99了。

明天继续分析这个问题。
暂时通过去掉IFNULL来解决。