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

Question

I'm run this query:

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

Answer 1

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.

Answer 2

You could try casting them as Int:

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