mysql从csv数据加载空值

balp4ylt 于 2021-06-18 发布在 Mysql

关注(0)|答案(7)|浏览(358)

我有一个文件，可以包含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出于某种原因不使用columns默认值（为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             | 
+---------+------+--------------------------------------------------------+

mysql csv load-data-infile

来源：https://stackoverflow.com/questions/53274968/can-a-mysql-table-with-column-data-type-as-double-hold-null-values

7条答案

按热度按时间

qlfbtfca1#

这会做你想做的。它将第四个字段读入局部变量，然后如果局部变量最终包含空字符串，则将实际字段值设置为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,'')
;

赞(0）回复(0）举报 2021-06-18

cyvaqqii2#

根据数据库配置，行为会有所不同。在严格模式下，这将抛出错误，否则将发出警告。以下查询可用于标识数据库配置。

mysql> show variables like 'sql_mode';

赞(0）回复(0）举报 2021-06-18

9cbw7uwe3#

预处理您的输入csv以将空白项替换为\n。
尝试正则表达式：s/，/，\n，/g和s/，$/，\n/g
祝你好运。

赞(0）回复(0）举报 2021-06-18

sg24os4d4#

（variable1，@variable2，…）设置variable2=nullif（@variable2，''或''）>>您可以设置任何条件

赞(0）回复(0）举报 2021-06-18

bq9c1y665#

mysql手册上说：
使用加载数据填充读取数据时，空列或缺少的列将更新为“”。如果要在列中使用空值，则应在数据文件中使用\n。在某些情况下，也可以使用“空”字。
因此，您需要将空格替换为\n如下所示：

1,2,3,4,5
1,2,3,\N,5
1,2,3

赞(0）回复(0）举报 2021-06-18

polkgigr6#

显示变量 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);

赞(0）回复(0）举报 2021-06-18

jbose2ul7#

已将输入文件转换为包含 \N 对于UNIX终端中使用下面SED命令的空白列数据： sed -i 's/,,/,\\N,/g' $file_name 然后使用 LOAD DATA INFILE 要加载到mysql的命令

赞(0）回复(0）举报 2021-06-18