MySQL：导入的CSV文件将Null转换为0-解网

问：

我已经检查了一些关于这个材料的旧讨论，它似乎并没有为我解决问题。唯一可用的解决方案是：MySQL从CSV数据加载NULL值，但我的变量比在这种情况下建议的要多得多。

我正在使用

mysql  Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

我想与您核实任何其他上传我的 csv 文件并将值保持为而不是自动转换为 .NULLNULL0

TABLE创建：

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;

Select 语句的结果

谢谢你的帮助。

mysql csv 文件上传 null load-data-infile

编辑 CSV 输入文件，将应为 NULL 的值更改为。该转义序列由加载为 NULL 。\NLOAD DATA INFILE
使用该函数转换为 NULL，如链接到的其他答案中所述。你的变量比这个答案显示的要多，但你只有 59 个，对吧？键入这些内容需要更长的时间，但它会起作用。NULLIF()''

如果您不喜欢键入那么多，可以从列名列表生成代码。

可以在如下所示的查询中获取列表：You can get the list in a query like this：

mysql> select column_name 
 from information_schema.columns 
 where table_name ='CovidDeaths';

您可以将它们与 GROUP_CONCAT（）结合使用，为您的 LOAD DATA INFILE 生成正确的代码行：

select concat(
 'LOAD DATA LOCAL INFILE ''/home/gyahia/Desktop/BootCamp/CovidVaccinations.csv''
  INTO TABLE CovidVaccinations 
  FIELDS TERMINATED BY '','' 
  ENCLOSED BY ''"'' 
  LINES TERMINATED BY ''\\n'' (',
group_concat(concat('@', column_name)),
') 
SET ',
group_concat(concat(column_name, '=NULLIF(@', column_name, ', '''')')),
' IGNORE 1 ROWS;') as _load_data_stmt
from information_schema.columns where table_name='CovidDeaths'

MySQL：导入的CSV文件将Null转换为0

MySQL: Imported CSV file converts Nulls to 0

评论

评论