MySQL загружает нулевые значения из данных CSV

Question

MySQL загружает нулевые значения из данных CSV

у меня есть файл, который может содержать от 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 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 на этом этапе, так что любая помощь будет оценена.

спасибо
С.

edit

вывод шоу предупреждения:



+---------+------+--------------------------------------------------------+

| 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             | 

+---------+------+--------------------------------------------------------+

515 4

mysql csv load-data-infile

4 ответов:

Comments

Ничего не найдено.

Duncan Lock · Accepted Answer · 2013-07-29 09:53:37

это будет делать то, что вы хотите. Он считывает четвертое поле в локальную переменную, а затем устанавливает фактическое значение поля равным 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,'')
;

Janci · Accepted Answer · 2010-04-20 16:44:29

MySQL manual говорит:

при чтении данных с данными нагрузки INFILE, пустые или отсутствующие столбцы обновлено с ". Если вы хотите NULL значение в столбце, вы должны использовать \N в файле данных. Буквальное слово "NULL" также может использоваться под некоторыми обстоятельства.

поэтому вам нужно заменить пробелы на \N следующим образом:
1,2,3,4,5
1,2,3,\N,5
1,2,3

Dobi · Accepted Answer · 2015-01-22 19:10:39

поведение отличается в зависимости от конфигурации базы данных. В строгом режиме это вызовет ошибку или предупреждение. Следующий запрос может быть использован для идентификации конфигурации базы данных.
mysql> show variables like 'sql_mode';

Sam Goldman · Accepted Answer · 2010-04-20 16:29:38

предварительно обработайте входной CSV, чтобы заменить пустые записи на \N.

попытка регулярного выражения: s/,,/,\n,/g и s/,$/,\N / g

удачи.