MySQL 从 CSV 数据加载 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2675323/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL load NULL values from CSV data
提问by Spiros
I have a file that can contain from 3 to 4 columns of numerical values which are separated by comma. Empty fields are defined with the exception when they are at the end of the row:
我有一个文件，可以包含 3 到 4 列用逗号分隔的数值。空字段被定义为例外，当它们位于行的末尾时：
1,2,3,4,5
1,2,3,,5
1,2,3
The following table was created in MySQL:
下表是在 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 | | +-------+--------+------+-----+---------+-------+
I am trying to load the data using MySQL LOAD command:
我正在尝试使用 MySQL LOAD 命令加载数据：
LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS
TERMINATED BY "," LINES TERMINATED BY "\n";
The resulting table:
结果表：
+------+------+-------+------+------+ | one | two | three | four | five | +------+------+-------+------+------+ | 1 | 2 | 3 | 4 | 5 | | 1 | 2 | 3 | 0 | 5 | | 1 | 2 | 3 | NULL | NULL | +------+------+-------+------+------+
The problem lies with the fact that when a field is empty in the raw data and is not defined, MySQL for some reason does not use the columns default value (which is NULL) and uses zero. NULL is used correctly when the field is missing alltogether.
问题在于，当原始数据中的字段为空且未定义时，MySQL 出于某种原因不使用列的默认值（即 NULL）而使用零。当字段完全丢失时，可以正确使用 NULL。
Unfortunately, I have to be able to distinguish between NULL and 0 at this stage so any help would be appreciated.
不幸的是，在这个阶段我必须能够区分 NULL 和 0，所以任何帮助将不胜感激。
Thanks S.
谢谢 S。
edit
编辑
The output of SHOW WARNINGS:
显示警告的输出：
+---------+------+--------------------------------------------------------+ | 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 | +---------+------+--------------------------------------------------------+
回答by Duncan Lock
This will do what you want. It reads the fourth field into a local variable, and then sets the actual field value to NULL, if the local variable ends up containing an empty string:
这会做你想做的。它将第四个字段读入局部变量，然后将实际字段值设置为 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,'')
;
If they're all possibly empty, then you'd read them all into variables and have multiple SET statements, like this:
如果它们都可能为空，那么您会将它们全部读入变量并具有多个 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,'')
;
回答by Janci
MySQL manualsays:
MySQL手册说：
When reading data with LOAD DATA INFILE, empty or missing columns are updated with ''. If you want a NULL value in a column, you should use \N in the data file. The literal word “NULL” may also be used under some circumstances.
使用 LOAD DATA INFILE 读取数据时，空列或缺失列将更新为 ''。如果要在列中使用 NULL 值，则应在数据文件中使用 \N。在某些情况下，也可以使用字面词“NULL”。
So you need to replace the blanks with \N like this:
所以你需要像这样用 \N 替换空格：
1,2,3,4,5
1,2,3,\N,5
1,2,3
回答by Dobi
The behaviour is different depending upon the database configuration. In the strict mode this would throw an error else a warning. Following query may be used for identifying the database configuration.
行为因数据库配置而异。在严格模式下，这会引发错误，否则会引发警告。以下查询可用于识别数据库配置。
mysql> show variables like 'sql_mode';
回答by Sam Goldman
Preprocess your input CSV to replace blank entries with \N.
预处理您的输入 CSV 以用 \N 替换空白条目。
Attempt at a regex: s/,,/,\n,/g and s/,$/,\N/g
尝试使用正则表达式：s/,,/,\n,/g 和 s/,$/,\N/g
Good luck.
祝你好运。
回答by Said
(variable1, @variable2, ..) SET variable2 = nullif(@variable2, '' or ' ') >> you can put any condition
(variable1, @variable2, ..) SET variable2 = nullif(@variable2, '' or ' ') >> 你可以输入任何条件
回答by Nirmal Silwal
show variables
显示变量
Show variables like "`secure_file_priv`";
Show variables like "`secure_file_priv`";
Note: keep your csv file in location given by the above command.
注意：将您的 csv 文件保存在上述命令指定的位置。
create table assessments (course_code varchar(5),batch_code varchar(7),id_assessment int, assessment_type varchar(10), date int , weight int);
Note: here the 'date' column has some blank values in the csv file.
注意：这里的 ' 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);
