从 CSV 上传 MYSQL 保持空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18015048/
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 upload from CSV maintaining nulls
提问by Ambulare
I have a MYSQL database containing a bunch of fields, many of which are configures as numerical values, either:
我有一个包含一堆字段的 MYSQL 数据库，其中许多字段配置为数值，或者：
thicknessdouble(7,2) DEFAULT NULL,
thickness双（7,2）默认为空，
or
或者
has_inputtinyint(4) DEFAULT NULL,
has_inputtinyint(4) 默认为空，
I have to import a csv file into this table and have been using
我必须将一个 csv 文件导入到这个表中并且一直在使用
load data local infile 'myfilepath/file.csv' into table
combinedfields terminated by ',' enclosed by '"' lines terminated by '\r\n';
将数据本地 infile 'myfilepath/file.csv' 加载到
combined以 ',' 结尾的表字段中，该字段由以 '\r\n' 结尾的 '"' 行括起来；
but all numerical fields are having null values replaced by zero. This is really annoying as some fields have a legitimate value of zero and I need to be able to distinguish these from the fields with no value.
但所有数字字段都将空值替换为零。这真的很烦人，因为某些字段的合法值为零，我需要能够将这些字段与没有值的字段区分开来。
As you can see above, the field defaults are set to null and, on other advice, I've entered \N in all the blank fields in the csv to indicate null.
正如您在上面看到的，字段默认值设置为空，并且根据其他建议，我在 csv 的所有空白字段中输入了 \N 以指示空。
Nevertheless, the nulls are still being replaced with zero. Can you help?
尽管如此，空值仍然被零替换。你能帮我吗？
回答by Sylvain Leroux
As the rules for handling NULL columns while importing CSV are rather complexes, there is a dedicated section on handling NULL value in the doc for LOAD DATA INFILE:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
由于在导入 CSV 时处理 NULL 列的规则相当复杂，因此在 LOAD DATA INFILE 的文档中有专门的部分处理 NULL 值：http:
//dev.mysql.com/doc/refman/5.1/en/load -data.html
In your specific case:
在您的具体情况下：
If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.
如果 FIELDS ENCLOSED BY 不为空，则包含文字字 NULL 作为其值的字段被读取为 NULL 值。这与包含在 FIELDS ENCLOSED BY 字符中的单词 NULL 不同，后者被读取为字符串 'NULL'。
Try to pre-process the CSV file by replacing missingvalues by the word NULL(without quotes).
尝试通过用单词NULL（不带引号）替换缺失值来预处理 CSV 文件。
If you have something like that in your orginal CSV file:
如果您的原始 CSV 文件中有类似内容：
0,1,2,,5
It should be transformed like that in order for MySQL to correctly insert NULL in the 4th column:
它应该像这样转换，以便 MySQL 在第 4 列中正确插入 NULL：
0,1,2,NULL,5
回答by wchiquito
It could be treated in the same sentence.
它可以在同一个句子中处理。
Given the following table:
鉴于下表：
CREATE TABLE `testcsv` (
`col0` INT(11) NOT NULL,
`col1` VARCHAR(20) DEFAULT NULL,
`col2` DECIMAL(5,2) DEFAULT NULL
);
and the following file .csv: test.csv
和以下文件 .csv：test.csv
1,\N,0
2,"string",102.20
3,\N,
4,"string",\N
5,"string",NULL
6,"string",
to run:
跑步：
LOAD DATA INFILE 'path/test.csv' INTO TABLE `testcsv`
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(`col0`, `col1`, @col2)
SET `col2` = IF(CHAR_LENGTH(TRIM(@col2)) = 0, NULL, @col2);
result:
结果：
mysql> SELECT `col0`, `col1`, `col2` FROM `testcsv`;
+------+--------+--------+
| col0 | col1 | col2 |
+------+--------+--------+
| 1 | NULL | 0.00 |
| 2 | string | 102.20 |
| 3 | NULL | NULL |
| 4 | string | NULL |
| 5 | string | NULL |
| 6 | string | NULL |
+------+--------+--------+
6 ROWS IN SET (0.00 sec)
