Why tilde on negative number(eg ~~-1) return 184467440737... in MySql query

Question

I just found a way that I think simpler and faster to remove decimal using double tilde ~~ in some programming languages.

I'm curious what's the meaning of tilde, then I found out from this answer:

The operator ~ is a binary negation operator (as opposed to boolean negation), and being that, it inverses all the bits of its operand. The result is a negative number in two's complement arithmetic.

that answer is for PHP language, and I think it's same for MySQL too. I think I could use ~ to revert negation(also remove decimal) and ~~ to just remove decimal number

I tried in PHP and JS:

single tilde:

    ~-1 // = 1
    ~1 // = -1
    ~-1.55 // = 1
    ~1.55 // = -1

double tilde:

    ~~-1 // = -1
    ~~1 // = 1
    ~~1.55 // = 1
    ~~-1.55 // = -1

but why I tried in MySQL shows different result:

select ~1; // 18446744073709551614
select ~-1; // 0
select ~-111; // 110
select ~1.55; // 18446744073709551613
select ~-1.55; // 1
select ~~1; // 1
select ~~-1; // 18446744073709551615
select ~~1.55; // 2
select ~~-1.55; // 18446744073709551614

from above queries, I can get conclusion if ~~ is can be used to remove decimal(with round half up) on positive number, but doesn't work for negative number(will return 18446744073...). And I don't know the use of ~ in MySQL. Anyone can explain it for me?

Answer 1

"... faster to remove decimal ..." -- Don't bother optimizing at this level. Stick to the overall structure of SQL.

For converting floating point values to integers, use a function:

mysql> SELECT FLOOR(12.7), CEIL(12.7), ROUND(12.7), ROUND(12.777, 2), FORMAT(1234.7, 0)\G
*************************** 1. row ***************************
      FLOOR(12.7): 12
       CEIL(12.7): 13
      ROUND(12.7): 13
 ROUND(12.777, 2): 12.78
FORMAT(1234.7, 0): 1,235

As for what ~ does with floating-point numbers, we need to get into the IEEE-754 standard. But your eyes may glaze over.