MySQL 从 CSV 数据加载 NULL 值

我有一个文件，可以包含3至4列的数值，它们由逗号分隔。当空字段位于行的末尾时，将定义一个异常:

1,2,3,4,5
1,2,3,,5
1,2,3

下表是在 MySQL 中创建的:

+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| one   | int(1) | YES  |     | NULL    |       |
| two   | int(1) | YES  |     | NULL    |       |
| three | int(1) | YES  |     | NULL    |       |
| four  | int(1) | YES  |     | NULL    |       |
| five  | int(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+

我正在尝试使用 MySQL LOAD 命令加载数据:

LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS
TERMINATED BY "," LINES TERMINATED BY "\n";

由此产生的表格:

+------+------+-------+------+------+
| one  | two  | three | four | five |
+------+------+-------+------+------+
|    1 |    2 |     3 |    4 |    5 |
|    1 |    2 |     3 |    0 |    5 |
|    1 |    2 |     3 | NULL | NULL |
+------+------+-------+------+------+

问题在于，当原始数据中的字段为空且未定义时，MySQL 出于某种原因不使用列默认值(即 NULL) ，而使用零。当字段完全丢失时，正确使用 NULL。

不幸的是，在这个阶段，我必须能够区分 NULL 和0，所以任何帮助都是值得感激的。

谢谢 是的。

编辑

显示警告的输出:

+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'four' at row 2 |
| Warning | 1261 | Row 3 doesn't contain data for all columns             |
| Warning | 1261 | Row 3 doesn't contain data for all columns             |
+---------+------+--------------------------------------------------------+
190792 次浏览
小开

预处理您的输入 CSV 以将空白条目替换为 N。

尝试正则表达式: s/,/，n,/g 和 s/，$/，N/g

祝你好运。

小开

MySQL 手册 说:

使用 LOADDATA 读取数据时 INFILE，空的或缺少的柱子是 更新为”。如果希望使用 NULL 值，则应该使用 N 在数据文件中。字面上的单词 “ NULL”也可以用在 情。

所以你需要像这样用 N 代替空格:

1,2,3,4,5
1,2,3,\N,5
1,2,3
小开
最佳答案

这就是你想要的。它将第四个字段读入一个局部变量，然后将实际字段值设置为 NULL，如果局部变量最终包含一个空字符串:

LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(one, two, three, @vfour, five)
SET four = NULLIF(@vfour,'')
;

如果它们都可能是空的，那么你就把它们读入变量，并且有多个 SET 语句，像这样:

LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(@vone, @vtwo, @vthree, @vfour, @vfive)
SET
one = NULLIF(@vone,''),
two = NULLIF(@vtwo,''),
three = NULLIF(@vthree,''),
four = NULLIF(@vfour,'')
;
小开

根据数据库配置的不同，行为是不同的。在严格模式下，这将抛出一个错误，否则一个警告。 下面的查询可用于标识数据库配置。

mysql> show variables like 'sql_mode';
小开

显示变量

Show variables like "`secure_file_priv`";

注意: 将 csv 文件保存在上面命令给出的位置。

create table assessments (course_code varchar(5),batch_code varchar(7),id_assessment int, assessment_type varchar(10), date int , weight int);

注意: 这里的‘ date’列在 csv 文件中有一些空白值。

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/assessments.csv'
INTO TABLE assessments
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(course_code,batch_code,id_assessment,assessment_type,@date,weight)
SET date = IF(@date = '', NULL, @date);
小开

(variable1,@variable2，. .) SET variable2 = nullif (@variable2,”或’’) > > 您可以放置任何条件

小开

使用 UNIX 终端中的 sed 命令转换输入文件以包含空白列数据的 \N:

sed -i 's/,,/,\\N,/g' $file_name

然后使用 LOAD DATA INFILE命令加载到 mysql


提交答案