Search code examples
mysqlunsignedsigned

How to bitshift negative numbers with mySQL to get results like in Java, Python etc?

When i want to bitshift -2 >> 4 it should give me -1. python and java do give me -1. But if i try it on my mySQL server i get 1152921504606846975. I tryed to inverse the bits to cast it etc But i am not able to get a -1. So does someone know how to bitshift it to get -1.

Solution

  • According to the documentation, MySQL's bit shift operators generate an unsigned 64-bit integer. Therefore, if you want to get the expected behavior with negative numbers, you may add your own logic:

    WITH yourTable AS (
    SELECT 4 AS val UNION ALL
    SELECT -4
)

SELECT
    IF(val > 0, val >> 2, -1.0*((-1.0*val) >> 2)) AS result
FROM yourTable;

    This outputs:

    1
-1.0