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?
"... 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.