mysql - 围绕 DECIMAL 数据类型转换的令人不安的 mysql 行为

Question

所以，这是结构:

mysql> describe tier;
+---------------------+----------------+------+-----+---------+----------------+

| Field               | Type           | Null | Key | Default | Extra          |

+---------------------+----------------+------+-----+---------+----------------+

| ID                  | int(10)        | NO   | PRI | NULL    | auto_increment |

| UP_TO               | decimal(21,10) | YES  |     | NULL    |                |

+---------------------+----------------+------+-----+---------+----------------+

2 rows in set (0.01 sec)

然后是数据:

mysql> select id, up_to from tier;
+----+-----------------+
| id | up_to           |
+----+-----------------+
|  1 | 1000.0000000000 |
|  2 | 2000.0000000000 |
|  3 | 3000.0000000000 |
|  4 |  500.0000000000 |
|  5 | 1000.0000000000 |
|  6 | 1500.0000000000 |
|  7 |  100.0000000000 |
|  8 |  200.0000000000 |
|  9 | 1000.0000000000 |
| 10 | 2000.0000000000 |
| 11 |  100.0000000000 |
| 12 |  200.0000000000 |
+----+-----------------+
12 rows in set (0.00 sec)

然后有一个小转变:

mysql> SELECT id, TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM CAST(up_to AS CH
AR) )) as converted from tier;
+----+-----------+
| id | converted |
+----+-----------+
|  1 | 1000      |
|  2 | 2000      |
|  3 | 3000      |
|  4 | 500       |
|  5 | 1000      |
|  6 | 1500      |
|  7 | 100       |
|  8 | 200       |
|  9 | 1000      |
| 10 | 2000      |
| 11 | 100       |
| 12 | 200       |
+----+-----------+
12 rows in set (0.00 sec)

很好。

让我们把它放在一个存储函数中以方便使用!

DELIMITER //
CREATE FUNCTION strip_trailing_zero(I_DEC DECIMAL(10,7)) RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
DECLARE strBuff VARCHAR(20);
DECLARE cnt  NUMERIC(2);
DECLARE tString VARCHAR(20);
    SELECT CAST(I_DEC AS CHAR) INTO tString;
    SELECT LOCATE('.',tString) INTO cnt;

    IF cnt > 0 THEN
        SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM tString)) INTO strBuff;
    ELSE
        SET strBuff = tString;
    END IF;

    RETURN strBuff;
END//

DELIMITER ;

很酷。

GRANT EXECUTE ON FUNCTION mysql.strip_trailing_zero TO 'whatever'@'localhost';

终于，现在可以试试我的新玩具了……:

mysql> select id, mysql.strip_trailing_zero(`up_to`) as converted_2 from tier;
+----+-------------+
| id | converted_2 |
+----+-------------+
|  1 | 999.9999999 |
|  2 | 999.9999999 |
|  3 | 999.9999999 |
|  4 | 500         |
|  5 | 999.9999999 |
|  6 | 999.9999999 |
|  7 | 100         |
|  8 | 200         |
|  9 | 999.9999999 |
| 10 | 999.9999999 |
| 11 | 100         |
| 12 | 200         |
+----+-------------+
12 rows in set, 7 warnings (0.02 sec)

好吧，那么，去你妈的也是 mysql!

不认真，这是一个愚蠢的笑话。我确信我做错了什么，中间有一个浮点转换，但我就是想不通!

欢迎帮助!谢谢。S.

编辑:将输入参数类型更改为DECIMAL(21,10)后的结果:

mysql> select id, mysql.strip_trailing_zero(`up_to`) as converted_2 from tier;
+----+-------------+
| id | converted_2 |
+----+-------------+
|  1 | 1000        |
|  2 | 2000        |
|  3 | 3000        |
|  4 | 500         |
|  5 | 1000        |
|  6 | 1500        |
|  7 | 100         |
|  8 | 200         |
|  9 | 1000        |
| 10 | 2000        |
| 11 | 100         |
| 12 | 200         |
+----+-------------+
12 rows in set (0.02 sec)

问题解决了...太棒了!谢谢!

Answer 1

您表中的 UP_TO 字段定义为 decimal(21,10)，但您的函数采用 decimal(10,7)。我想那会剥夺你的值(value)。

尝试更改您的函数以接受 decimal(21,10)。