為什麼將此字元串轉換為小數會失敗？

為什麼將此結果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 長期存在的問題，自 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 版本使用。

引用自：https://dba.stackexchange.com/questions/291836