mysql - MySQL转换十进制后的子字符串丢失数字

我正在运行以下查询：

SELECT
    SUBSTRING(pluginText,LOCATE('cvss', pluginText), 21) AS CVSS_Base_Score_Text,
    CONVERT(SUBSTRING_INDEX(SUBSTRING(pluginText,
        LOCATE('cvss', pluginText), 21),' : ',-1),UNSIGNED INTEGER)
    AS CVSS_Base_Score_Number
FROM vulnerabilities_internal
WHERE LCASE(pluginText) REGEXP '.*cvss.*'

它返回两列

CVSS_Base_Score_Text         CVSS_Base_Score Number
CVSS Base Score: 4.3         4

如何修改此查询，以便CVSS_Base_Score_number列中的数字保留小数点并显示为4.3？
回答了我自己的问题：

SELECT
SUBSTRING(pluginText,LOCATE('cvss', pluginText), 21) AS CVSS_Base_Score_Text,
CONVERT(SUBSTRING_INDEX(SUBSTRING(pluginText,LOCATE('cvss', pluginText), 21),' : ',
-1),
DECIMAL(10,1)) AS CVSS_Base_Score_Number
FROM vulnerabilities_internal

最佳答案

下面的查询将给出您想要的结果。

SELECT
    SUBSTRING(pluginText,LOCATE('cvss', pluginText), 21) AS CVSS_Base_Score_Text,
    CAST(SUBSTRING_INDEX(SUBSTRING(pluginText,
        LOCATE('cvss', pluginText), 21),' : ',-1) as decimal(2,1))
    AS CVSS_Base_Score_Number
FROM vulnerabilities_internal
WHERE LCASE(pluginText) REGEXP '.*cvss.*'