☰

csv 导入 mysql 空值

tag-icon • tag-icon mysql mariadb csv
csv 导入 mysql 空值

我必须将一些 csv 格式的数据导入 mysql (mariadb) 数据库。没有 csv 标头，行如下所示：

00323acd-7909-41a4-a849-073ca3391dcf,2014-05,\N
00323acd-7909-41a4-a849-073ca3391dcf,2014-05,1

因此它们包含一些十六进制 ID、年份/月份组合和可选的 int 值。我使用 \N（十六进制：5c 4e）来标记 NULL 值（mysql 也使用此样式导出空值）。

LOAD DATA LOCAL INFILE 'path/to/data.csv' INTO TABLE data_table 
FIELDS TERMINATED BY ',' ENCLOSED BY '' 
LINES  TERMINATED BY '\n' (id, @date_time_variable, value) 
SET date = STR_TO_DATE(@date_time_variable, '%Y-%m');

但看起来数据库无法识别“\N”编码的空值。

Query OK, 38581 rows affected, 14596 warnings (0.54 sec)
Records: 38581  Deleted: 0  Skipped: 0  Warnings: 14596

MariaDB [run5]> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
' for column 'value' at row 1   |ger value: 'N
' for column 'value' at row 2   |ger value: 'N
' for column 'value' at row 3   |ger value: 'N

该模式如下所示：

CREATE TABLE `data_table` (
  `id` char(36) NOT NULL,
  `date` date NOT NULL,
  `value` int(11) DEFAULT NULL,
  KEY `mbid` (`id`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

有人知道这个错误或解决方案吗？

编辑：

下面是输出show warnings \G;：

MariaDB [run5]> show warnings \G;
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 1lue: 'N
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 2lue: 'N
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 3lue: 'N
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 4lue: 'N
*************************** 5. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 5lue: 'N
...

以下是该文件的十六进制转储：

~/D/path ❯❯❯ head -n 2 data/file.csv | hexdump -C                                           master ✱ ◼
00000000  30 30 33 32 33 63 63 64  2d 37 39 30 39 2d 34 31  |00323ccd-7909-41|
00000010  61 34 2d 61 38 34 39 2d  30 37 33 63 61 33 33 39  |a4-a849-073ca339|
00000020  31 64 63 66 2c 32 30 31  34 2d 30 35 2c 5c 4e 0d  |1dcf,2014-05,\N.|
00000030  0a 30 30 33 32 33 63 63  64 2d 37 39 30 39 2d 34  |.00323ccd-7909-4|
00000040  31 61 34 2d 61 38 34 39  2d 30 37 33 63 61 33 33  |1a4-a849-073ca33|
00000050  39 31 64 63 66 2c 32 30  31 34 2d 31 32 2c 5c 4e  |91dcf,2014-12,\N|
00000060  0d 0a                                             |..|
00000062

第三行包含2c 5c 4e，代表,\N，这是正确的，不是吗？

编辑2：

我更新了查询，因为我使用了错误的行终止符（\n而不是\r\n）。现在我收到一条有关此错误值的错误消息：

MariaDB [run5]> LOAD DATA LOCAL INFILE '/path/data.csv' INTO TABLE data_table FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES  TERMINATED BY '\n\r' (id, @date_time_variable, value) SET date = STR_TO_DATE(@date_time_variable, '%Y-%m');
Query OK, 1 row affected, 1 warning (0.01 sec)       
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

MariaDB [run5]> show warnings \G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: 'N
00323ccd-7909-41a4-a849-073ca3391dcf' for column 'value' at row 1
1 row in set (0.00 sec)

答案1

我还没有尝试过，但我读过 LOAD DATA INFILE 手册，其中“\N”序列的解释由 ESCAPED BY 子句控制，并且默认不执行转义序列处理。如果这是正确的，则将“ESCAPED BY '\\'”添加到查询中应该会得到您想要的结果。

答案2

我怀疑导入的数据格式不正确。具体来说，某些或所有数据\N都缺失。\

验证数据是否正确。你可以这样做：

$ awk -F, '$3=="\\N"{print $0}' moo.csv
00323acd-7909-41a4-a849-073ca3391dcf,2014-05,\N

$ awk -F, '$3=="N"{print $0}' moo.csv
00323acd-7909-41a4-a849-073ca3391dcf,2014-05,N

此外，警告输出被切断。再次尝试导入，然后使用以下命令：

show warnings \G

相关内容