MySQL Workbench错误码1292：截断不正确整数值问题修复咨询

错误原因：covidvaccinations表的new_vaccinations字段中存在空字符串（''），执行convert(vac.new_vaccinations, signed int)时，MySQL无法将空字符串转换为有效整数，触发截断错误。

以下是几种可行的修复方案：

方案1：转换前处理空字符串（推荐） #

将空字符串替换为0或NULL后再转换，确保转换操作合法，同时保留所有记录：

Insert into percentpopulationvaccinated
Select death.continent, death.location, death.date, death.population, vac.new_vaccinations, 
sum(convert(COALESCE(NULLIF(vac.new_vaccinations, ''), 0), signed int)) over (partition by death.location order by death.location, death.date) as People_Vaccinated
from coviddeaths as death
join covidvaccinations as vac
    on death.location = vac.location
    and death.date = vac.date;

• NULLIF(vac.new_vaccinations, '')：把空字符串转为NULL
• COALESCE(..., 0)：把NULL替换为0，确保转换后是有效整数

方案2：过滤含空字符串的记录 #

如果不需要统计空字符串对应的记录，直接在查询中过滤：

Insert into percentpopulationvaccinated
Select death.continent, death.location, death.date, death.population, vac.new_vaccinations, 
sum(convert(vac.new_vaccinations, signed int)) over (partition by death.location order by death.location, death.date) as People_Vaccinated
from coviddeaths as death
join covidvaccinations as vac
    on death.location = vac.location
    and death.date = vac.date
WHERE vac.new_vaccinations != '' AND vac.new_vaccinations IS NOT NULL;

方案3：用CASE语句做更灵活的转换 #

通过CASE判断字段值，针对性处理空字符串：

Insert into percentpopulationvaccinated
Select death.continent, death.location, death.date, death.population, vac.new_vaccinations, 
sum(CAST(CASE WHEN vac.new_vaccinations = '' THEN 0 ELSE vac.new_vaccinations END AS SIGNED)) over (partition by death.location order by death.location, death.date) as People_Vaccinated
from coviddeaths as death
join covidvaccinations as vac
    on death.location = vac.location
    and death.date = vac.date;

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