MySQL回合一半 [英] MySQL round half

问题描述

在MySQL中，是否有可能像PHP那样舍入一半特定的方式?

Is it possible, in MySQL, to round half a specific way like PHP would do?

• PHP_ROUND_HALF_UP
• PHP_ROUND_HALF_DOWN
• PHP_ROUND_HALF_EVEN
• PHP_ROUND_HALF_ODD

• PHP_ROUND_HALF_UP
• PHP_ROUND_HALF_DOWN
• PHP_ROUND_HALF_EVEN
• PHP_ROUND_HALF_ODD

http://php.net/manual/fr/function.round.php

还是我们真的仅限于四舍五入吗?

Or are we really limited to only rounding up?

推荐答案

1.从Oracle文档中:

基于MySQL官方文档，舍入函数的作用如下:

1. From Oracle documentation:

Based on MySQL official documentation, the rounding function act as the following:

对于精确值数字，ROUND()使用从零开始舍入一半" 或四舍五入"规则:小数部分为.5的值 大于或等于正则向下舍入为下一个整数 下一个整数(如果为负). (换句话说，四舍五入 小数部分小于.5的值将四舍五入为 如果是正数，则为下一个整数；如果为负数，则为下一整数.

For exact-value numbers, ROUND() uses the "round half away from zero" or "round toward nearest" rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

http://dev.mysql.com /doc/refman/5.7/en/mathematical-functions.html#function_round

这意味着我们只能使用舍入功能进行舍入.我编写了以下UDF来解决此限制.

which means that we can only use the rounding function to round up. I have written the following UDF to work around this limitation.

CREATE FUNCTION roundHalf (
    numberToRound DECIMAL(20,6),
    roundingPrecision TINYINT(2),
    roundingType ENUM (
        'ROUND_HALF_UP',
        'ROUND_HALF_DOWN',
        'ROUND_HALF_EVEN',
        'ROUND_HALF_ODD'
    )
)
    RETURNS DECIMAL(20,6)
BEGIN
    DECLARE digitEvenOdd TINYINT (2) UNSIGNED DEFAULT 255;
    DECLARE digitPosition TINYINT (2) UNSIGNED DEFAULT 0;
    DECLARE digitToRound TINYINT (2) DEFAULT -1;
    DECLARE roundedNumber DECIMAL(20,6) DEFAULT 0;

    SET digitPosition = INSTR(numberToRound, '.');

    IF (roundingPrecision < 1) THEN

        SET digitPosition = digitPosition + roundingPrecision;
    ELSE

        SET digitPosition = digitPosition + roundingPrecision + 1;
    END IF;

    IF (digitPosition > 0 AND 
        digitPosition <= CHAR_LENGTH(numberToRound)
    ) THEN

        SET digitToRound = CAST(
                SUBSTR(
                numberToRound, 
                digitPosition, 
                1
            ) AS UNSIGNED
        );

        SET digitPosition = digitPosition - 1;

        IF (digitPosition > 0 AND 
            digitPosition <= CHAR_LENGTH(numberToRound)
        ) THEN    

            SET digitEvenOdd = CAST(
                SUBSTR(
                    numberToRound, 
                    digitPosition, 
                        1
                ) AS UNSIGNED
            );
        END IF;
    END IF;

    IF (digitToRound > -1) THEN

        CASE roundingType

            WHEN 'ROUND_HALF_UP' THEN

                IF (digitToRound >= 5) THEN

                    SET roundedNumber = ROUND(numberToRound, roundingPrecision);
                ELSE
                    SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
                END IF;

            WHEN 'ROUND_HALF_DOWN' THEN

                IF (digitToRound > 5) THEN

                    SET roundedNumber = ROUND(numberToRound, roundingPrecision);
                ELSE

                    SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
                END IF;

            WHEN 'ROUND_HALF_EVEN' THEN

                IF (digitToRound >= 5 AND 
                    digitEvenOdd IN (1,3,5,7,9)
                ) THEN

                    SET roundedNumber = ROUND(numberToRound, roundingPrecision);
                ELSE

                    SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
                END IF;

            WHEN 'ROUND_HALF_ODD' THEN

                IF (digitToRound >= 5 AND
                    digitEvenOdd IN (0,2,4,6,8)
                ) THEN

                    SET roundedNumber = ROUND(numberToRound, roundingPrecision);
                ELSE

                    SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
                END IF;
        END CASE;

    ELSEIF (roundingPrecision > 0) THEN

        SET roundedNumber = numberToRound;
    END IF;

    RETURN roundedNumber;
END //

注意:可以根据项目的需要将要舍入的值增加到总计65位(在这种情况下，请不要忘记更改).

Note: The value the number to round can be increased based on the need of your project up to 65 digits in total (in that case, do not forget to change all the instances of DECIMAL(20,6) accordingly).

https://stackoverflow.com/a/19201329/4949388

http://sandbox.onlinephpfunctions.com/code/054de06b074c2b3ece5fb6e5d4>

4.单元测试(在SQL中):

http://sandbox.onlinephpfunctions.com/code/054de06b074c2b3ece5fb6e5d4180524cd2207e2

/* round number not enough digits */

IF (roundHalf(1.455, 7, 'ROUND_HALF_UP') <> 1.455) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_1';

ELSEIF (roundHalf(1.455, -5, 'ROUND_HALF_UP') <> 0) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_2';

ELSEIF (roundHalf(555, -1, 'ROUND_HALF_UP') <> 560) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_3';

END IF;


/* round half up */

IF (roundHalf(1.541, 2, 'ROUND_HALF_UP') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_1';

ELSEIF (roundHalf(1.545, 2, 'ROUND_HALF_UP') <> 1.55) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_2';

ELSEIF (roundHalf(555, 0, 'ROUND_HALF_UP') <> 555) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_3';

ELSEIF (roundHalf(1000999, -2, 'ROUND_HALF_UP') <> 1001000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_4';

ELSEIF (roundHalf(1000999, -3, 'ROUND_HALF_UP') <> 1001000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_5';

ELSEIF (roundHalf(1000999, -4, 'ROUND_HALF_UP') <> 1000000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_6';
END IF;


/* round half down */

IF (roundHalf(1.541, 2, 'ROUND_HALF_DOWN') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_1';

ELSEIF (roundHalf(1.545, 2, 'ROUND_HALF_DOWN') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_2';

ELSEIF (roundHalf(555, 0, 'ROUND_HALF_DOWN') <> 555) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_3';

ELSEIF (roundHalf(1000999, -2, 'ROUND_HALF_DOWN') <> 1001000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_UP_TEST_4';

ELSEIF (roundHalf(1000999, -3, 'ROUND_HALF_DOWN') <> 1001000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_5';

ELSEIF (roundHalf(1000999, -4, 'ROUND_HALF_DOWN') <> 1000000) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_DOWN_TEST_6';
END IF;


/* round half even */

IF (roundHalf(1.541, 2, 'ROUND_HALF_EVEN') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_EVEN_TEST_1';

ELSEIF (roundHalf(1.544, 2, 'ROUND_HALF_EVEN') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_EVEN_TEST_2';

ELSEIF (roundHalf(1.455, 2, 'ROUND_HALF_EVEN') <> 1.46) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_EVEN_TEST_3';
END IF;

/* round half odd */

IF (roundHalf(1.544, 2, 'ROUND_HALF_ODD') <> 1.54) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_1';

ELSEIF (roundHalf(1.545, 2, 'ROUND_HALF_ODD') <> 1.55) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_2';

ELSEIF (roundHalf(1.455, 2, 'ROUND_HALF_ODD') <> 1.45) THEN

  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'INVALID_ROUND_HALF_ODD_TEST_3';
END IF;

请随意使用该编码，但不要忘了喜欢我的帖子.谢谢大家的评论和建议.

Use that coding as you please, but don't forget to like my post. Thank you all for your comments and suggestions.

这篇关于MySQL回合一半的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！