MySQL загружает значения NULL из данных 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:

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, поэтому любая помощь будет принята с благодарностью.

Спасибо С.

редактировать

Вывод ПОКАЗАТЬ ПРЕДУПРЕЖДЕНИЯ:

+---------+------+--------------------------------------------------------+
| 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
person Spiros    schedule 20.04.2010    source источник
comment
С такими изменениями схемы данных я бы использовал d6tstack, который выравнивает все столбцы перед запуском LOAD DATA. См. Раздел примеры SQL d6tstack об изменении схемы данных.   -  person citynorman    schedule 15.10.2018

Ответы (7)


arrow_upward
207
arrow_downward

Это сделает то, что вы хотите. Он считывает четвертое поле в локальную переменную, а затем устанавливает фактическое значение поля в 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,'')
;
person Duncan Lock    schedule 11.05.2011
comment
Теоретически я полагаю - но все это в памяти и содержит только крошечные объемы данных в каждой строке, поэтому я бы мог представить, что это было бы бесконечно малым; но вы должны проверить это, если считаете, что это может быть проблемой. - person Duncan Lock; 19.04.2013
comment
Мне очень нравится этот ответ. Пользователи могут видеть пустые строки '' при загрузке CSV (используя IFNULL(Col,'') в SELECT INTO OUTFILE запросе) для Excel, но затем загрузки принимают их как пустые, а не имеют дело с \N в CSV. Спасибо! - person chrisan; 29.09.2013
comment
для дат я использовал 'NULLIF (STR_TO_DATE (@ date1,% d /% m /% Y), 0000-00-00)' - person Joaquín L. Robles; 24.02.2014
comment
У меня есть файл csv, содержащий нули 0, которые должны быть преобразованы в NULL (потому что невозможно иметь нулевое значение для рассматриваемых данных), а также пустые строки. Как сделать так, чтобы и нули, и пустые строки преобразовывались в NULL? - person Paul Rougieux; 11.09.2017
comment
Если нулевые значения и пустые строки находятся в отдельных столбцах, тогда просто выполните описанное выше для пустых строк и что-то вроде этого для нулей: nullif(@vone, 0). - person Duncan Lock; 12.09.2017
comment
Если они оба находятся в одном столбце, то есть в одном исходном столбце, который может содержать либо нулевые, либо пустые строки, вам, вероятно, потребуется вложить вызовы nullif. - person Duncan Lock; 12.09.2017
comment
@Blacksonic Я выполняю 10 nullif() операций, в результате чего время импорта увеличилось на 8,7%. Я импортировал 300 тысяч записей за 10 секунд. Время импорта увеличено примерно до 11 секунд с добавленными nullif() условиями. - person Kenneth; 13.03.2018
comment
Как это сделать без упоминания конкретных столбцов? только для всех? - person user8411456; 13.02.2020
comment
что, если у вас 50 столбцов? установка 50 столбцов кажется излишним. есть ли глобальный способ указать функции загрузки просто заменить пустые значения на ноль? - person ahsant; 30.10.2020

arrow_upward
145
arrow_downward

В руководстве по MySQL говорится:

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

Поэтому вам нужно заменить пробелы на \ N следующим образом:

1,2,3,4,5
1,2,3,\N,5
1,2,3
person Janci    schedule 20.04.2010
comment
Спасибо за совет - я скептически отношусь к редактированию необработанных исходных данных, но если это единственный способ обойти это, я попробую. - person Spiros; 20.04.2010
comment
Я понимаю ваш скептицизм, никто не любит редактировать необработанные данные, это просто неправильно. Однако если задуматься на минуту, должен быть способ отличить NULL от пустой строки. Если пустые записи переводятся в NULL, вам понадобится специальная последовательность для пустой строки. Было бы неплохо иметь способ, как сообщить MySQL, как обрабатывать пустые записи, хотя, что-то вроде ЗАГРУЗКИ ДАННЫХ INFILE '/tmp/testdata.txt' INTO TABLE moo TREAT BLANKS AS NULL ... - person Janci; 20.04.2010
comment
Хорошо, но если у вас Fields enclosed by: ", это "\N" из "name",\N,"stuff" - person Jonathon; 25.08.2013
comment
Я могу проверить, что, по крайней мере, для phpMyAdmin 3.5.5 стиль \N не принимается как обозначающий NULL. Вместо этого используйте NULL, как в этом примере: "name","age",NULL,"other","stuff" - person Jonathon; 25.08.2013
comment
Вы спасаете мне день. Спасибо. - person nikoskip; 03.03.2015
comment
У нас MySQL 5.5.46-0 + deb8u1. Я пробовал и NULL, и \ N, и у нас работал только \ N. - person raphael75; 30.06.2016

arrow_upward
6
arrow_downward

Поведение различается в зависимости от конфигурации базы данных. В строгом режиме это приведет к ошибке или предупреждению. Следующий запрос может использоваться для определения конфигурации базы данных.

mysql> show variables like 'sql_mode';
person Dobi    schedule 22.01.2015
comment
Спасибо! Я чесал в затылке, пытаясь понять, почему импорт CSV с пустыми столбцами, который я вчера успешно импортировал на рабочий сервер, не работал с моей новой локальной установкой - это был ответ в моем случае! - person Emma Burrows; 13.06.2016

arrow_upward
3
arrow_downward

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

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

Удачи.

person Sam Goldman    schedule 20.04.2010
comment
Это регулярное выражение работает частично, оно не решает последовательные пустые записи, например ,,,, будет, \ n ,, \ n, можно будет использовать, если вы запустите его дважды - person ievgen; 22.06.2016
comment
Обобщу ответ и предыдущий комментарий. Для меня сработало следующее: sed -i 's / ,, /, \ N / g' $ file, sed -i 's / ,, /, / g' $ file, sed -i 's / \ N, $ / \ N / g '$ файл, - person Omar Khazamov; 04.12.2016
comment
Я хотел бы сделать это, но мне непонятно, как вы используете это регулярное выражение. Если вы используете MySQL для запуска этого с файлом, это будет лучшим решением. Но вы не говорите, и я не хочу тратить кучу времени на поиски в Google того, как сделать что-то, что может оказаться невозможным. - person DonkeyKong; 19.07.2019

arrow_upward
0
arrow_downward

показать переменные

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);
person Nirmal Silwal    schedule 23.03.2019

arrow_upward
0
arrow_downward

(переменная1, @ переменная2, ..) SET переменная2 = nullif (@ переменная2, '' или '') >> вы можете поставить любое условие

person Said    schedule 25.04.2020
comment
Вероятно, это было бы лучше в качестве комментария к stackoverflow.com/a/5968530/10376320 - person enharmonic; 04.06.2021

arrow_upward
0
arrow_downward

Преобразовал входной файл, чтобы включить \N для данных пустого столбца, используя следующую команду sed в терминале UNix:

sed -i 's/,,/,\\N,/g' $file_name

а затем используйте команду LOAD DATA INFILE для загрузки в mysql

person Arnab Dutta    schedule 28.11.2020