MySQL项目报错：int位置无效，转换signed/unsigned int求和失败

核心错误点 #

1. 语法缺失逗号：SELECT子句中vac.new_vaccinations与后续的SUM函数之间缺少逗号，这是触发语法错误的直接原因。
2. CONVERT类型参数规范：MySQL中使用CONVERT转换整数类型时，推荐使用SIGNED或UNSIGNED作为类型参数（虽然INT也可识别，但前者更符合MySQL官方规范）。

修正后的查询语句 #

SELECT 
    dea.continent,
    dea.location,
    dea.date,
    dea.population,
    vac.new_vaccinations,
    SUM(CONVERT(vac.new_vaccinations, SIGNED)) OVER (
        PARTITION BY dea.location 
        ORDER BY dea.location, dea.date
    ) AS RollingPeopleVaccinated
FROM Portfolio.CovidDeaths AS dea
JOIN Portfolio.CovidVaccine AS vac
    ON dea.location = vac.location 
    AND dea.date = vac.date
WHERE dea.continent IS NOT NULL
ORDER BY 2, 3;

额外优化建议 #

如果vac.new_vaccinations字段存在NULL值，转换后求和会自动忽略NULL，若希望将NULL视为0参与计算，可以用COALESCE处理：

SUM(CONVERT(COALESCE(vac.new_vaccinations, 0), SIGNED)) OVER (
    PARTITION BY dea.location 
    ORDER BY dea.location, dea.date
) AS RollingPeopleVaccinated

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