开发者

mysql - difference between two integer column doesn't works as expected

开发者 https://www.devze.com 2022-12-20 18:12 出处:网络
I\'m run this query: 开发者_如何学CSELECT id,like - dislike as result FROM mytable Where the column like and dislike are unsigned integer. If the column dislike is greater than like mysql return nu

I'm run this query:

开发者_如何学C
SELECT id,like - dislike as result
FROM mytable

Where the column like and dislike are unsigned integer. If the column dislike is greater than like mysql return number like 18446744073709551596, so seem that mysql treat this like unsigned and can't return negative number but continue the computation from a sort of MAX_UNSIGNED_INT. How can I have the correct result


Try casting the two values (or maybe only on of them)

SELECT id, convert(like, SIGNED ) - convert(dislike, SIGNED ) as result
FROM mytable

or only the result

SELECT id, convert(like - dislike, SIGNED ) as result
FROM mytable

In the first way you can get type overflow! The Second way is better, but I'm not sure it works with mysql.


You could try casting them as Int:

SELECT id, CAST(like AS INT) - CAST(dislike AS INT) as result FROM mytable
0

精彩评论

暂无评论...
验证码 换一张
取 消