MySQL导入CSV时NULL被转为0的解决办法咨询

背景 #

• 已查阅相关旧讨论未解决问题，仅找到Stack Overflow的一个解决方案，但因表中字段过多无法适用
• 使用的MySQL版本：mysql Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

需求 #

寻求其他方法，在导入CSV文件时保留NULL值，防止其被自动转换为0。

相关代码与截图 #

表创建语句 #

CREATE TABLE CovidDeaths
(iso_code   varchar(100)    ,
continent   varchar(100)    ,
location    varchar(100)    ,
date    varchar(100)    ,
total_cases int ,
new_cases   int ,
new_cases_smoothed  int ,
total_deaths int,
new_deaths  int ,
new_deaths_smoothed int ,
total_cases_per_million FLOAT   ,
new_cases_per_million   FLOAT   ,
new_cases_smoothed_per_million  FLOAT   ,
total_deaths_per_million    FLOAT   ,
new_deaths_per_million  FLOAT   ,
new_deaths_smoothed_per_million FLOAT   ,
reproduction_rate   FLOAT   ,
icu_patients    int ,
icu_patients_per_million    FLOAT   ,
hosp_patients   int ,
hosp_patients_per_million   FLOAT   ,
weekly_icu_admissions   int ,
weekly_icu_admissions_per_million   FLOAT   ,
weekly_hosp_admissions  int ,
weekly_hosp_admissions_per_million  FLOAT   ,
new_tests   int ,
total_tests int ,
total_tests_per_thousand    FLOAT   ,
new_tests_per_thousand  FLOAT   ,
new_tests_smoothed  int ,
new_tests_smoothed_per_thousand FLOAT   ,
positive_rate   FLOAT   ,
tests_per_case  FLOAT   ,
tests_units int ,
total_vaccinations  int ,
people_vaccinated   int ,
people_fully_vaccinated int ,
new_vaccinations    int ,
new_vaccinations_smoothed   int ,
total_vaccinations_per_hundred  FLOAT   ,
people_vaccinated_per_hundred   FLOAT   ,
people_fully_vaccinated_per_hundred FLOAT   ,
new_vaccinations_smoothed_per_million   FLOAT   ,
stringency_index    int ,
population  int ,
population_density  FLOAT   ,
median_age  FLOAT   ,
aged_65_older   int ,
aged_70_older   int ,
gdp_per_capita  FLOAT   ,
extreme_poverty int ,
cardiovasc_death_rate   FLOAT   ,
diabetes_prevalence FLOAT   ,
female_smokers  FLOAT   ,
male_smokers    FLOAT   ,
handwashing_facilities  int ,
hospital_beds_per_thousand  FLOAT   ,
life_expectancy FLOAT   ,
human_development_index FLOAT);

文件导入语句 #

LOAD DATA LOCAL INFILE '/home/gyahia/Desktop/BootCamp/CovidVaccinations.csv'
INTO TABLE CovidVaccinations
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
-- OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

查询结果截图 #

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