Início / dba / Perguntas / 291836
Accepted
Eaten by a Grue
Asked: 2021-05-20 00:41:26 +0800 CST

Por que a conversão dessa string como decimal falha?

  • 772

Por que a conversão desse resultado REGEXP_SUBSTR()para um DECIMAL falha?

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 |
+------------+--------+--------+--------+--------+

Lançar uma string simples como cost_4parece funcionar. Multiplicar o REGEXP_SUBSTR()resultado por 1também parece funcionar. Mas simplesmente lançar o resultado como eu fiz cost_1e cost_2não produzir a versão correta de ponto fixo do _extracted.

Estranhamente, no meu aplicativo, usando a referência anterior, como fiz, cost_2realmente produz o resultado correto. Não foi possível reproduzir em outro lugar, mas achei que vale a pena mencionar.

mysql mysql-8.0

2 respostas

  1. Best Answer

    Este tem sido um problema de longa data com o MySQL, com pessoas relatando esse problema como um bug desde 2011 . Descobri que o problema é quase completamente dependente do agrupamento que está sendo usado na REGEXP_SUBSTR()função.

    Por exemplo, se você converter o resultado de REGEXP_SUBSTR()como CHAR(100), seus decimais permanecerão intactos:

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

result
----- 
14.18

    O resultado retornado por REGEXP_SUBSTR()usou um conjunto de caracteres UTF-16 anterior ao MySQL 8.0.17. Versões posteriores supostamente usam o mesmo conjunto de caracteres configurado pelo cliente (veja o bug #94203 relatado por Rick James), mas isso não parece preciso. Meu cliente SQL está configurado para usar UTF-8 em todos os lugares. Executar sua consulta inicial no meu cliente produz exatamente os mesmos resultados que você compartilhou na pergunta.

    No entanto, se eu CONVERT( ... USING 'UTF8'):

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

result
----- 
14.18

    Surpresa surpresa. Um número correto.

    Geralmente nesta situação eu faço a mesma coisa que você fez cost_3; Eu multiplico o valor retornado por 1 e depois o converto no tipo desejado. Você pode salvar uma etapa lançando como FLOAT, mas isso às vezes tem implicações de precisão.

    Não é uma ótima resposta, mas pode ser usada em várias versões do MySQL.

    • 2

  2. Não CAST. Usar

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

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

relate perguntas