MySQL回合一半

本文介绍了MySQL回合一半的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在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:

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回合一半的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！