Mysql - Round float to nearest integer-CodePudding

How can I round a FLOAT to the nearest INTEGER in MySql?

I know about the ROUND() function already. The reason for my question is that the return value of MySql's ROUND() function "has the same type as the first argument" (https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_round)

So if I pass in a FLOAT 3.6 then I will get a FLOAT 3.0 as my answer. How can I instead get an integer 3?

I have thought about using FLOOR(ROUND(x)). My worry here is that a floating-point rounding issue might result in the wrong answer. For example, is it possible that something like ROUND(3.2) might, due to floating point number implementation return a FLOAT 2.99999999999999999999 and then FLOOR(ROUND(3.2)) would give the wrong answer 2. I also wonder whether there is a more efficient method than combining 2 functions.

CodePudding user response：

There are 2 options to cast or convert a FLOAT directly to an integer: CONVERT and CAST. Both options work essentially the same way, and both options require making a choice between signed and unsigned integers, so if you are using a mixture of negative and positive numbers between -2,147,483,648 to 2,147,483,647, you will have to use signed integers. On the other hand, if you need numbers really big positive numbers up to 4,294,967,295 then you will need to use unsigned integers.

Here are some examples:

SELECT CONVERT(-3.2, SIGNED);

Produces an output of -3

SELECT CAST(3.6 as SIGNED);

Produces an output of 4

SELECT CONVERT(3.3, UNSIGNED);

Produces an output of 3. Note: If performing arithmetic functions with the converted integers, there are some rules that must be adhered to. Here is a link to additional information on the cast and convert functions: mySequel_Dev_CastFunctions

CodePudding user response：

It seems that in my version of MySql at least, the solution is trivial:

CAST(x AS UNSIGNED) (or CAST(x AS SIGNED)) seem to have the exact behaviour that I want which is to round to the nearest integer.

Although so far, I can't find anything in the documentation that confirms exactly what the behaviour is when converting FLOAT to INTEGER. I don't know whether there are implementations out there that might truncate towards zero or use floor instead...

Tests

SELECT CAST(-1.8 AS SIGNED); /* -2 */
SELECT CAST(-1.5 AS SIGNED); /* -2 */
SELECT CAST(-1.2 AS SIGNED); /* -1 */
SELECT CAST(1.2 AS SIGNED); /* 1 */
SELECT CAST(1.5 AS SIGNED); /* 2 */
SELECT CAST(1.8 AS SIGNED); /* 2 */

SELECT CAST(-1.8 AS UNSIGNED); /* 0 */
SELECT CAST(-1.5 AS UNSIGNED); /* 0 */
SELECT CAST(-1.2 AS UNSIGNED); /* 0 */
SELECT CAST(1.2 AS UNSIGNED); /* 1 */
SELECT CAST(1.5 AS UNSIGNED); /* 2 */
SELECT CAST(1.8 AS UNSIGNED); /* 2 */