mysql - DOUBLE 作为 BLOB 返回

Question

我对我的 SELECT 有一些困惑:

SELECT  
    mergedData.rawValue,
    mergedData.validatedValue,
    IFNULL(mergedData.validatedValue, mergedData.rawValue) as mergedValue
FROM
    [...]

虽然 mergedData.rawValue 和 mergedData.validatedValue 返回 DOUBLE，但 IFNULL 语句返回 BLOB。为什么会发生这种情况？我怎样才能让它返回一个 DOUBLE 呢？

编辑:我通过模拟 FULL JOIN 来合并 Select 中具有完全相同结构的两个表。数据在两个表中都以 DOUBLE 形式存储:

TableRawData(NOX 加载到 mergedData.rawValue):

timed (BIGINT(20)) --- NOX (DOUBLE)
1380583800000          8.972167

TableValidatedData(NOX 加载到 mergedData.validatedValue 中):

timed (BIGINT(20)) --- NOX (DOUBLE)
1380583800000          9.506166

EDIT2:我的完整选择:

SET @StartTime  = 1356998400000;
SET @EndTime    = 1386546000000;

SELECT  
    mergedData.rawValue,
    mergedData.validatedValue,
    CAST(IFNULL(mergedData.validatedValue, mergedData.rawValue) AS DECIMAL(10, 5)) as mergedValue
FROM
 (SELECT 
        Month(FROM_UNIXTIME(timed / 1000)) as months,
            Year(FROM_UNIXTIME(timed / 1000)) as years,
            DAYOFMONTH(FROM_UNIXTIME(timed / 1000)) as days,
            HOUR(FROM_UNIXTIME(timed / 1000)) as hours,
            MINUTE(FROM_UNIXTIME(timed / 1000)) as minutes,
            IF(true, avg(NOX), null) as rawValue,
            FROM_UNIXTIME(timed / 1000) as timeValue,
            IF(true, 1, 0) as IsValid,
            NULL as validatedValue
    FROM
        nabelnrt_bas
    WHERE
        timed > @StartTime
            AND timed < @EndTime
    GROUP BY concat(years, months, days) UNION (
 SELECT 
        Month(FROM_UNIXTIME(timed / 1000)) as months,
            Year(FROM_UNIXTIME(timed / 1000)) as years,
            DAYOFMONTH(FROM_UNIXTIME(timed / 1000)) as days,
            HOUR(FROM_UNIXTIME(timed / 1000)) as hours,
            MINUTE(FROM_UNIXTIME(timed / 1000)) as minutes,
            IF(true, avg(NOX), null) as validatedValue,
            FROM_UNIXTIME(timed / 1000) as timeValue,
            IF(true, 1, 0) as IsValid,
            NULL as rawValue
    FROM
        nabelvalidated_bas
    WHERE
        timed > @StartTime
            AND timed < @EndTime
    GROUP BY concat(years, months, days))) as mergedData

Answer 1

直接转换

SELECT  
    mergedData.rawValue,
    mergedData.validatedValue,
    CAST(IFNULL(mergedData.validatedValue, mergedData.rawValue) AS DECIMAL[(M[,D])]) as mergedValue
FROM

请注意DECIMAL[(M[,D])]，您应该在定义表时填写它。

编辑，COALESCE可能也很有趣。使用示例；

SELECT  
    mergedData.rawValue,
    mergedData.validatedValue,
    COALESCE(mergedData.validatedValue, mergedData.rawValue) as mergedValue
FROM

也许你不需要转换它