INSERT执行报SQL 1292截断DOUBLE值错误但同逻辑SELECT正常

同一段SELECT语句单独执行无错、放入INSERT ... SELECT结构触发1292错误，本质是两种场景下MySQL优化器选择的执行路径、类型校验严格度不一致：

• home_assistant.states表的state字段为字符串类型，你写的s.state > 0属于数值比较逻辑，MySQL会隐式将两侧值转换为DOUBLE类型再计算，当遇到state='unknown'这类非数值字符串时，就会触发「Truncated incorrect DOUBLE value」错误。
• 单独执行SELECT时，优化器通常会优先走entity_id相关索引，先过滤出sensor.pond_last_refill对应的所有行，再执行s.state > 0的判断，这批行里没有'unknown'这类非法值，因此不会触发转换报错。
• 执行INSERT ... SELECT时，一方面语句为了保证写入数据的一致性会启用更严格的类型校验规则，另一方面优化器为了提升扫描效率，会将s.state > 0这个判断条件下推到存储引擎层，在全表扫描阶段就逐行做判断——此时还没执行entity_id的过滤逻辑，扫到任意实体对应的state='unknown'行，都会触发隐式转换失败抛错。
• 你之前尝试显式转换state为FLOAT仍报错，是因为转换逻辑只写在了聚合函数SUM()中，WHERE子句里的s.state > 0仍然是对原始字符串字段做隐式转换，没有解决条件下推时碰到非法值的问题。

核心思路是在数值比较前，先把非数值的state值过滤掉，避免隐式转换碰到非法字符串，推荐两种稳定写法：

1. 先用正则过滤合法数值行，再做数值判断和聚合

INSERT INTO ha_archive.pond(last_updated, water)
SELECT DATE(s.last_updated) AS last_updated,
            SUM(CAST(s.state AS DECIMAL(10,2))) AS water
    FROM home_assistant.states s
    WHERE s.entity_id = 'sensor.pond_last_refill' 
      -- 先过滤掉非数值格式的state，比如'unknown'
      AND s.state REGEXP '^-?[0-9]+(\\.[0-9]+)?$'
      AND CAST(s.state AS DECIMAL(10,2)) > 0
    GROUP BY DATE(s.last_updated)
ON DUPLICATE KEY UPDATE
    water = VALUES(water);

2. 用子查询提前做值转换，把非法值转为NULL再过滤

INSERT INTO ha_archive.pond(last_updated, water)
SELECT DATE(last_updated) AS last_updated,
            SUM(state_num) AS water
    FROM (
        SELECT 
            s.last_updated,
            -- 合法数值转DECIMAL，非法值直接置为NULL
            IF(s.state REGEXP '^-?[0-9]+(\\.[0-9]+)?$', CAST(s.state AS DECIMAL(10,2)), NULL) AS state_num
        FROM home_assistant.states s
        WHERE s.entity_id = 'sensor.pond_last_refill'
    ) t
    WHERE state_num > 0
    GROUP BY DATE(last_updated)
ON DUPLICATE KEY UPDATE
    water = VALUES(water);

注意：不推荐通过修改sql_mode关闭严格校验的方式规避错误，会导致非法值被隐式转换为0写入表，产生脏数据。

内容的提问来源于stack exchange，提问作者Joost