MySQL报错1292：截断不正确的INTEGER值''，临时表插入失败求助

错误原因 #

你遇到的Error Code: 1292是因为vac.new_vaccinations字段（varchar类型）中存在空字符串''，当用convert(vac.new_vaccinations, signed int)或cast(vac.new_vaccinations as signed)尝试将其转为整数时，MySQL无法解析空字符串为有效整数，触发截断错误。

另外注意你源表字段的拼写笔误：dea.polulation应该是dea.population，实际使用时要修正。

解决方案 #

1. 先排查无效数据 #

先执行以下查询，确认new_vaccinations中的无效值：

SELECT vac.new_vaccinations
FROM CovidVaccinations vac
WHERE vac.new_vaccinations = '' OR vac.new_vaccinations REGEXP '[^0-9.]';

这条语句会找出所有空字符串或包含非数字/小数点的记录，方便你明确数据问题范围。

2. 修改INSERT语句处理无效值 #

针对空字符串和非数字值，转换时先做处理，避免触发错误。以下提供两种常用处理方式：

方式一：将空字符串转为NULL

insert into PercentPopulationVaccinated
select 
  dea.continent, 
  dea.location, 
  str_to_date(dea.date, '%Y-%M-%D'), 
  convert(dea.population, signed int), 
  convert(NULLIF(vac.new_vaccinations, ''), signed int), 
  sum(cast(NULLIF(vac.new_vaccinations, '') as signed)) 
      over(partition by dea.location 
           order by dea.location, dea.date ROWS UNBOUNDED PRECEDING) RunningTotalVaccinations
From CovidDeaths dea
join CovidVaccinations vac
    on dea.location = vac.location
    and dea.date = vac.date
-- where dea.continent is not null
order by 2, 3;

NULLIF(vac.new_vaccinations, '')会把空字符串转为NULL，转换整数时NULL不会触发错误，求和时NULL会被自动忽略。

方式二：将空字符串转为0

如果需要把空值当作0参与累计计算，用IF函数处理：

insert into PercentPopulationVaccinated
select 
  dea.continent, 
  dea.location, 
  str_to_date(dea.date, '%Y-%M-%D'), 
  convert(dea.population, signed int), 
  convert(IF(vac.new_vaccinations = '', '0', vac.new_vaccinations), signed int), 
  sum(cast(IF(vac.new_vaccinations = '', '0', vac.new_vaccinations) as signed)) 
      over(partition by dea.location 
           order by dea.location, dea.date ROWS UNBOUNDED PRECEDING) RunningTotalVaccinations
From CovidDeaths dea
join CovidVaccinations vac
    on dea.location = vac.location
    and dea.date = vac.date
-- where dea.continent is not null
order by 2, 3;

3. 额外优化点 #

• 窗口函数中order by dea.location无意义（已按该字段分区），建议加上dea.date按日期顺序累计，否则累计值可能不符合预期。
• 如果new_vaccinations中还有其他非数字字符（如字母、符号），可以先用REGEXP_REPLACE清理：REGEXP_REPLACE(vac.new_vaccinations, '[^0-9]', '')，再进行转换。

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