主页 / dba / 问题 / 291836
Accepted
Eaten by a Grue
Asked: 2021-05-20 00:41:26 +0800 CST

为什么将此字符串转换为小数会失败？

  • 772

为什么将此结果REGEXP_SUBSTR()转换为 DECIMAL 失败？

SELECT
    REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS _extracted,
    CAST(REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS DECIMAL(8,2)) AS cost_1,
    CAST((SELECT _extracted) AS DECIMAL(8,2)) AS cost_2,
    CAST((SELECT _extracted) * 1 AS DECIMAL(8,2)) AS cost_3,
    CAST('14.18' AS DECIMAL(8,2)) AS cost_4;

+------------+--------+--------+--------+--------+
| _extracted | cost_1 | cost_2 | cost_3 | cost_4 |
+------------+--------+--------+--------+--------+
| 14.18      |  14.00 |  14.00 |  14.18 |  14.18 |
+------------+--------+--------+--------+--------+

像 in 一样投射一个普通的字符串cost_4似乎有效。REGEXP_SUBSTR()将结果乘以1似乎也有效。但只是像我所做的那样简单地转换结果cost_1并且cost_2无法生成正确的定点版本_extracted.

奇怪的是，在我的应用程序中使用反向引用cost_2实际上会产生正确的结果。无法在其他地方复制，但认为值得一提。

mysql mysql-8.0

2 个回答

  1. Best Answer

    这是 MySQL 长期存在的问题，自 2011 年以来人们将这个问题报告为错误。我发现问题几乎完全取决于REGEXP_SUBSTR()函数中使用的排序规则。

    例如，如果您将结果转换REGEXP_SUBSTR()为 a CHAR(100)，您的小数将保持不变：

    mysql> SELECT CAST(CAST(REGEXP_SUBSTR('Cost (-$14.18)', '[0-9.]+') AS CHAR(100)) AS DECIMAL(8,2)) AS result;

result
----- 
14.18

    REGEXP_SUBSTR()MySQL 8.0.17 之前使用 UTF-16 字符集返回的结果。此后的版本应该使用与客户端配置的相同字符集（参见 Rick James 报告的错误#94203），但这似乎并不准确。我的 SQL 客户端配置为在任何地方使用 UTF-8。在我的客户端中运行您的初始查询会产生与您在问题中共享的完全相同的结果。

    但是，如果我CONVERT( ... USING 'UTF8')：

    SELECT CAST(CONVERT(REGEXP_SUBSTR('Cost (-$14.18)', '[0-9.]+') USING 'UTF8') AS DECIMAL(8,2)) AS result;

result
----- 
14.18

    惊喜，惊喜。一个正确的数字。

    一般来说，在这种情况下，我会做和你一样的事情cost_3；我将返回值乘以 1，然后将其转换为所需的类型。您可以通过强制转换为 来保存一个步骤FLOAT，但这有时会影响精度。

    这不是一个很好的答案，但它可以跨多个 MySQL 版本使用。

    • 2

  2. 不是CAST。利用

    FORMAT(expression, 2)  -- for displaying with 2 decimal places

ROUND(expression, 2)   -- for further computation
    • 1

相关问题