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，所以任何帮助都将不胜感激。
谢谢S。
编辑
显示警告的输出：
+---------+------+--------------------------------------------------------+ | 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 | +---------+------+--------------------------------------------------------+
这将做你想要的。它将第四个字段读入局部变量，然后将实际字段值设置为 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 manual 说：
使用 LOAD DATA INFILE 读取数据时，空列或缺失列将使用 '' 进行更新。如果您想在列中使用 NULL 值，则应在数据文件中使用 \N。在某些情况下也可以使用字面意思“NULL”。
所以你需要用 \N 替换空格，如下所示：
1,2,3,4,5
1,2,3,\N,5
1,2,3
Fields enclosed by: " 是 "name",\N,"stuff" 中的 "\N"
\N 的样式被接受为表示 NULL。请改用 NULL，如本例所示："name","age",NULL,"other","stuff"
行为因数据库配置而异。在严格模式下，这会引发错误，否则会发出警告。以下查询可用于识别数据库配置。
mysql> show variables like 'sql_mode';
预处理您的输入 CSV 以用 \N 替换空白条目。
尝试正则表达式：s/,,/,\n,/g 和 s/,$/,\N/g
祝你好运。
显示变量
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, '' or ' ') >> 你可以放任何条件
在 UNIX 终端中使用以下 sed 命令将输入文件转换为包含空白列数据的 \N：
sed -i 's/,,/,\\N,/g' $file_name
然后使用 LOAD DATA INFILE 命令加载到 mysql
SELECT INTO OUTFILE查询中使用IFNULL(Col,'')）时可以看到空字符串''，但随后上传时接受它们为空，而不必处理 csv 中的\N。谢谢！NULL的零0（因为有问题的数据不可能有零值）以及空字符串。如何确保零和空字符串都转换为NULL？