MySQL查询变得疯狂了吗? [英] MySQL query gone wild?

问题描述

任何人都可以告诉我为什么会发生以下情况以及如何解决该问题吗?

Can anyone tell me why the following is happening, and how to fix it?

我有一个带有ORDER BY子句的MySQL查询，如下所示.

I have a MySQL query with an ORDER BY clause that looks like this..

ORDER BY (did_voteup-did_votedown) DESC, did_voteup DESC

因此它应该按照结果的有效"等级对结果进行排序，如下所示.

So it should order the results by their "effective" rating, as in..

1st. 10 up - 1 down = 9 effective
2nd. 10 up - 5 down = 5 effective
3rd. 10 up - 7 down = 3 effective

但是，正如您在我的页面上看到的那样，这是错误的命令，然后给我这个..

However, as you can see on my page here, it's ordering them wrong, and giving me this..

1st. 1 up - 3 down = -2 effective
2nd. 16 up - 6 down = 10 effective
3rd. 15 up - 5 down = 10 effective

很显然，第一排应该不在那里.

Obviously, that 1st place row shouldn't be there.

更多信息..

CREATE TABLE dictionary_definitions (
  did_id int(11) unsigned NOT NULL auto_increment,
  did_wordid int(11) unsigned NOT NULL default '0',
  did_userid int(11) unsigned NOT NULL default '0',
  did_status tinyint(1) unsigned NOT NULL default '0',
  did_date int(11) NOT NULL default '0',
  did_definition text,
  did_example text,
  did_votecheck int(11) NOT NULL default '0',
  did_voteup smallint(5) unsigned NOT NULL default '0',
  did_votedown smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY (did_id),
  KEY (did_wordid),
  KEY (did_userid)
) ENGINE=MyISAM;

SELECT a.did_id, a.did_userid, a.did_definition, a.did_example, 
    a.did_votecheck, a.did_voteup, a.did_votedown, b.user_name, b.user_extra8 
FROM dictionary_definitions AS a LEFT JOIN users AS b ON a.did_userid=b.user_id 
WHERE did_wordid=4 
ORDER BY (did_voteup-did_votedown) DESC, did_voteup DESC LIMIT 0, 5

推荐答案

这是已知的问题关于从无符号整数中减去.

整数值之间的减法(其中一个是UNSIGNED类型的)将产生无符号 默认情况下的结果.如果结果否则为负，则它将变为 最大整数值.如果启用了NO_UNSIGNED_SUBTRACTION SQL模式，则结果为 消极的.

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, it becomes the maximum integer value. If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative.

参考:数值类型

这篇关于MySQL查询变得疯狂了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！