SQL脚本触发#1292警告：截断不正确DECIMAL值问题求助

执行以下SQL时触发#1292 Truncated incorrect DECIMAL value警告，所有字段均为VARCHAR类型：

INSERT INTO `BOP_APPENDICI`(RAMO, RISCHIO, PREMIO)            
SELECT 
substring_index(substring_index(nomefilepdf, '_', 10), '_', -1) as ramo_view,
substring_index(substring_index(nomefilepdf, '_', 11), '_', -1) as rischio_view,
substring_index(substring_index(nomefilepdf, '_', 12), '_', -1) as premio_view
FROM `BOP_importazioneAppendici
WHERE 
substring_index(substring_index(nomefilepdf, '_', 4), '_', -1) IN 
    (SELECT TIPO_DOC FROM BOP_DOMINIO_DOCUMENTI)
AND NOT EXISTS (
    SELECT 1 
    FROM BOP_DOMINIO_RAMO_RISCHIO d
    WHERE 
        d.ramo = substring_index(substring_index(nomefilepdf, '_', 10), '_', -1)
        AND d.rischio = substring_index(substring_index(nomefilepdf, '_', 11), '_', -1)
)
AND cast(substring_index(substring_index(nomefilepdf, '_', 12), '_', -1) as int) = 0

问题根源 #

警告并非来自你尝试移除的CAST语句，而是WHERE子句中的隐式类型转换：MySQL在比较不同类型的值时，会自动将字符串转换为数值类型。如果字符串包含非数字字符，转换过程中就会触发截断警告。

具体可能的触发点：

• IN子查询中，BOP_DOMINIO_DOCUMENTI.TIPO_DOC如果是数值类型，而substring_index返回的字符串无法完整转换为数值
• NOT EXISTS关联条件中，BOP_DOMINIO_RAMO_RISCHIO.d.ramo或d.rischio是数值类型，与字符串形式的substring_index结果比较时触发转换

解决方法 #

统一比较两边的类型，避免隐式转换：

1. 处理IN子查询的类型不匹配
   如果TIPO_DOC是数值类型，将左侧字符串转为数值：

   CAST(substring_index(substring_index(nomefilepdf, '_', 4), '_', -1) AS UNSIGNED) IN 
   (SELECT TIPO_DOC FROM BOP_DOMINIO_DOCUMENTI)
   

   如果TIPO_DOC是字符串类型，确保左侧结果也是字符串（本来就是，但可以显式转换确认）：

   substring_index(substring_index(nomefilepdf, '_', 4), '_', -1) IN 
   (SELECT CAST(TIPO_DOC AS CHAR) FROM BOP_DOMINIO_DOCUMENTI)
   

2. 处理NOT EXISTS的关联条件
   如果d.ramo/d.rischio是数值类型，将字段转为字符串再比较：

   CAST(d.ramo AS CHAR) = substring_index(substring_index(nomefilepdf, '_', 10), '_', -1)
   AND CAST(d.rischio AS CHAR) = substring_index(substring_index(nomefilepdf, '_', 11), '_', -1)
   

修改后的完整SQL示例 #

INSERT INTO `BOP_APPENDICI`(RAMO, RISCHIO, PREMIO)            
SELECT 
substring_index(substring_index(nomefilepdf, '_', 10), '_', -1) as ramo_view,
substring_index(substring_index(nomefilepdf, '_', 11), '_', -1) as rischio_view,
substring_index(substring_index(nomefilepdf, '_', 12), '_', -1) as premio_view
FROM `BOP_importazioneAppendici` -- 补全缺失的反引号
WHERE 
CAST(substring_index(substring_index(nomefilepdf, '_', 4), '_', -1) AS UNSIGNED) IN 
    (SELECT TIPO_DOC FROM BOP_DOMINIO_DOCUMENTI)
AND NOT EXISTS (
    SELECT 1 
    FROM BOP_DOMINIO_RAMO_RISCHIO d
    WHERE 
        CAST(d.ramo AS CHAR) = substring_index(substring_index(nomefilepdf, '_', 10), '_', -1)
        AND CAST(d.rischio AS CHAR) = substring_index(substring_index(nomefilepdf, '_', 11), '_', -1)
)
AND CAST(substring_index(substring_index(nomefilepdf, '_', 12), '_', -1) AS UNSIGNED) = 0

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