13.2.6 LOAD DATA Statement — MySQL 5.7

13.2.6 Оператор LOAD DATA

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

Оператор LOAD DATA с высокой скоростью считывает строки из текстового файла в таблицу. Файл может быть прочитан с серверного или клиентского хоста в зависимости от того, задан ли модификатор LOCAL. LOCAL также влияет на интерпретацию данных и обработку ошибок.

LOAD DATA является дополнением к SELECT ... INTO OUTFILE. (См. Раздел 13.2.9.1, «Оператор SELECT ... INTO».) Для записи данных из таблицы в файл используйте SELECT ... INTO OUTFILE. Для чтения файла обратно в таблицу используйте LOAD DATA. Синтаксис пунктов FIELDS и LINES одинаков для обоих операторов.

Утилита mysqlimport предоставляет ещё один способ загрузки файлов данных; она работает путём отправки оператора LOAD DATA на сервер. См. Раздел 4.5.5, «mysqlimport — Программа импорта данных».

Сведения об эффективности оператора INSERT по сравнению с LOAD DATA и ускорения LOAD DATA см. в Разделе 8.2.4.1, «Оптимизация операторов INSERT».

Не-LOCAL против LOCAL операции

Модификатор LOCAL влияет на эти аспекты LOAD DATA по сравнению с не-LOCAL операцией:

Он изменяет ожидаемое расположение входного файла; см. Расположение входного файла.
Он изменяет требования к безопасности оператора; см. Требования к безопасности.
Если также не указан REPLACE, LOCAL имеет тот же эффект, что и модификатор IGNORE на интерпретацию содержимого входного файла и обработку ошибок; см. Обработку ошибок и дубликатов ключей и Назначение значений столбцам.

LOCAL работает только если сервер и ваш клиент настроены для этого. Например, если mysqld был запущен с отключенной системной переменной local_infile, LOCAL выдаст ошибку. См. Раздел 6.1.6, «Рассмотрение безопасности для LOAD DATA LOCAL».

Кодировка входного файла

Имя файла должно быть задано как строковая константа. В Windows укажите обратные слеши в именах путей как обратные косые черты или двойные обратные слеши. Сервер интерпретирует имя файла, используя кодировку, указанную системной переменной character_set_filesystem.

По умолчанию сервер интерпретирует содержимое файла, используя кодировку, указанную системной переменной character_set_database. Если содержимое файла использует кодировку, отличную от этой по умолчанию, рекомендуется указать эту кодировку, используя пункт CHARACTER SET. Кодировка binary означает “нет преобразования”.

SET NAMES и значение character_set_client не влияют на интерпретацию содержимого файла.

LOAD DATA интерпретирует все поля в файле как имеющие одинаковую кодировку, независимо от типов данных столбцов, в которые загружаются значения полей. Для правильной интерпретации файла необходимо убедиться, что он был записан с правильной кодировкой. Например, если вы записываете файл данных с помощью mysqldump -T или выполнив оператор SELECT ... INTO OUTFILE в mysql, обязательно используйте опцию --default-character-set для записи вывода в кодировке, которая будет использоваться при загрузке файла с помощью LOAD DATA.

Примечание

Невозможно загрузить файлы данных, которые используют кодировки ucs2, utf16, utf16le или utf32.

END_OF_DOCUMENT_MARKER

Расположение входного файла

Эти правила определяют расположение входного файла для LOAD DATA:

Если LOCAL не указано, файл должен находиться на хосте сервера. Сервер считывает файл напрямую, определяя его местоположение следующим образом:
- Если имя файла является абсолютным путем, сервер использует его в указанном виде.
- Если имя файла — относительный путь с ведущими компонентами, сервер ищет файл относительно своего каталога данных.
- Если имя файла не содержит ведущих компонентов, сервер ищет файл в каталоге базы данных по умолчанию.
Если LOCAL указано, файл должен находиться на хосте клиента. Программа клиента считывает файл, определяя его местоположение следующим образом:
- Если имя файла является абсолютным путем, программа клиента использует его в указанном виде.
- Если имя файла — относительный путь, программа клиента ищет файл относительно каталога вызова.
При использовании LOCAL, программа клиента считывает файл и отправляет его содержимое серверу. Сервер создает копию файла в каталоге, где хранятся временные файлы. См. Раздел B.3.3.5, «Расположение временных файлов MySQL». Недостаточно места для копии в этом каталоге может привести к отказу оператора LOAD DATA LOCAL.

Правила, отличные от LOCAL, означают, что сервер считывает файл с именем ./myfile.txt относительно своего каталога данных, в то время как он считывает файл с именем myfile.txt из каталога базы данных по умолчанию. Например, если выполняется следующая команда LOAD DATA, а db1 — это база данных по умолчанию, сервер считывает файл data.txt из каталога базы данных для db1, даже если оператор явно загружает файл в таблицу в базе данных db2:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Требования к безопасности

Для операции загрузки, отличной от LOCAL, сервер считывает текстовый файл, расположенный на хосте сервера, поэтому необходимо выполнить следующие требования к безопасности:

Вы должны иметь право FILE. См. Раздел 6.2.2, «Предоставленные права MySQL».
Операция подчиняется настройке системной переменной secure_file_priv:
- Если значение переменной — это имя непустого каталога, файл должен находиться в этом каталоге.
- Если значение переменной пустое (что небезопасно), файл должен быть только доступным для чтения сервером.

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

Обработка дублирующих ключей и ошибок

Модификаторы REPLACE и IGNORE управляют обработкой новых (входных) строк, которые дублируют существующие строки таблицы по значениям уникальных ключей (PRIMARY KEY или UNIQUE значения индексов):

С помощью REPLACE новые строки, имеющие то же значение уникального ключа, что и в существующей строке, заменяют существующую строку. См. Раздел 13.2.8, «Оператор REPLACE».
С помощью IGNORE новые строки, дублирующие существующую строку по значению уникального ключа, отбрасываются. Дополнительная информация приведена в Разделе «Влияние IGNORE на выполнение оператора».

Если также не указан REPLACE, модификатор LOCAL имеет тот же эффект, что и IGNORE. Это происходит потому, что сервер не может остановить передачу файла в середине операции.

Если ни REPLACE, ни IGNORE, ни LOCAL не указаны, при обнаружении значения дублирующего ключа возникает ошибка, и остальная часть текстового файла игнорируется.

Помимо влияния на обработку дублирующих ключей, как описано выше, IGNORE и LOCAL также влияют на обработку ошибок:

Когда ни IGNORE, ни LOCAL не указаны, ошибки интерпретации данных приводят к завершению операции.
Если указан IGNORE — или LOCAL без REPLACE — ошибки интерпретации данных становятся предупреждениями, и операция загрузки продолжается, даже если режим SQL ограничен. Примеры см. в разделе «Присваивание значений столбцов».

Обработка индексов

Чтобы проигнорировать ограничения внешних ключей во время операции загрузки, выполните оператор SET foreign_key_checks = 0 перед выполнением LOAD DATA.

Если вы используете LOAD DATA на пустой MyISAM таблице, все не уникальные индексы создаются в отдельной партии (как для REPAIR TABLE). Обычно это значительно ускоряет LOAD DATA при большом количестве индексов. В некоторых крайних случаях вы можете еще быстрее создать индексы, отключив их с помощью ALTER TABLE ... DISABLE KEYS перед загрузкой файла в таблицу и повторно создав индексы с помощью ALTER TABLE ... ENABLE KEYS после загрузки файла. См. Раздел 8.2.4.1, «Оптимизация операторов INSERT».

END_OF_DOCUMENT_MARKER

Обработка полей и строк

Для обоих операторов LOAD DATA и SELECT ... INTO OUTFILE, синтаксис пунктов FIELDS и LINES одинаков. Оба пункта необязательны, но FIELDS должен предшествовать LINES, если оба указаны.

Если вы укажете пункт FIELDS, каждый из его подпунктов (TERMINATED BY, [OPTIONALLY] ENCLOSED BY и ESCAPED BY) также необязателен, за исключением того, что вы должны указать хотя бы один из них. Аргументы этих пунктов могут содержать только символы ASCII.

Если вы не укажете пункты FIELDS или LINES, значения по умолчанию будут такими же, как если бы вы написали следующее:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

Обратный слэш является символом экранирования MySQL в строках SQL-выражений. Таким образом, чтобы указать литеральный обратный слэш, вам необходимо указать два обратных слэша, чтобы значение интерпретировалось как один обратный слэш. Последовательности экранирования '\t' и '\n' обозначают соответственно табуляцию и перевод строки.

Другими словами, значения по умолчанию заставляют LOAD DATA действовать следующим образом при чтении входных данных:

Искать разделители строк в переводах строк.
Не пропускать никакие префиксы строк.
Разбивать строки на поля по табуляции.
Не ожидать, что поля будут заключены в какие-либо символы кавычек.
Интерпретировать символы, предваряемые символом экранирования \, как последовательности экранирования. Например, \t, \n и \\ обозначают соответственно табуляцию, перевод строки и обратный слэш. Полный список последовательностей экранирования см. в обсуждении FIELDS ESCAPED BY.

Обратно, значения по умолчанию заставляют SELECT ... INTO OUTFILE действовать следующим образом при записи выходных данных:

Записывать табуляцию между полями.
Не заключать поля в какие-либо символы кавычек.
Использовать \ для экранирования случаев табуляции, перевода строки или \, которые встречаются в значениях полей.
Записывать переводы строк в конце строк.

Примечание

Для текстового файла, сгенерированного на системе Windows, для правильного чтения файла может потребоваться LINES TERMINATED BY '\r\n', поскольку программы Windows обычно используют два символа в качестве разделителя строк. Некоторые программы, такие как WordPad, могут использовать \r в качестве разделителя строк при записи файлов. Для чтения таких файлов используйте LINES TERMINATED BY '\r'.

Если у всех входных строк есть общий префикс, который вы хотите пропустить, вы можете использовать LINES STARTING BY 'prefix_string' для пропуска префикса и всего, что находится перед ним. Если строка не содержит префикс, вся строка пропускается. Предположим, что вы выполняете следующее выражение:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

Если файл данных выглядит так:

xxx"abc",1
something xxx"def",2
"ghi",3

Результирующие строки — ("abc",1) и ("def",2). Третья строка файла пропускается, так как она не содержит префикс.

Пункт IGNORE number LINES можно использовать для пропуска строк в начале файла. Например, вы можете использовать IGNORE 1 LINES для пропуска начальной заголовка со столбцами:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

Когда вы используете SELECT ... INTO OUTFILE в связке с LOAD DATA для записи данных из базы данных в файл, а затем для повторного чтения файла в базу данных позже, параметры обработки полей и строк для обоих операторов должны совпадать. В противном случае LOAD DATA не правильно интерпретирует содержимое файла. Предположим, что вы используете SELECT ... INTO OUTFILE для записи файла с полями, разделенными запятыми:

SELECT * INTO OUTFILE 'data.txt'
  FIELDS TERMINATED BY ','
  FROM table2;

Для чтения файла с разделителями запятыми правильное выражение:

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY ',';

Если вместо этого вы попытаетесь прочитать файл с помощью следующего выражения, оно не сработает, потому что оно указывает LOAD DATA искать табуляцию между полями:

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY '\t';

Вероятным результатом будет то, что каждая входная строка будет интерпретирована как одно поле.

LOAD DATA может использоваться для чтения файлов, полученных из внешних источников. Например, многие программы могут экспортировать данные в формате CSV (comma-separated values), при котором строки имеют поля, разделенные запятыми и заключенными в двойные кавычки, с начальной строкой названий столбцов. Если строки в таком файле завершаются парами возврат каретки/перевод строки, показанное здесь выражение иллюстрирует параметры обработки полей и строк, которые вы бы использовали для загрузки файла:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

Если входные значения не обязательно заключены в кавычки, используйте OPTIONALLY перед опцией ENCLOSED BY.

Любой из параметров обработки полей или строк может задавать пустую строку (''). Если она не пустая, значения FIELDS [OPTIONALLY] ENCLOSED BY и FIELDS ESCAPED BY должны быть одиночными символами. Значения FIELDS TERMINATED BY, LINES STARTING BY и LINES TERMINATED BY могут быть более чем одним символом. Например, для записи строк, завершаемых парами возврат каретки/перевод строки, или для чтения файла, содержащего такие строки, укажите пункт LINES TERMINATED BY '\r\n'.

Для чтения файла, содержащего шутки, разделенные строками, состоящими из %%, можно сделать так

CREATE TABLE jokes
  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY управляет цитированием полей. Для вывода (SELECT ... INTO OUTFILE), если вы опустите слово OPTIONALLY, все поля заключаются в символ ENCLOSED BY. Пример такого вывода (с запятой в качестве разделителя полей) показан здесь:

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

Если вы укажете OPTIONALLY, символ ENCLOSED BY используется только для заключения значений из столбцов, имеющих строковый тип данных (таких как CHAR, BINARY, TEXT или ENUM):

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

Вхождения символа ENCLOSED BY в значении поля экранируются путем добавления символа ESCAPED BY перед ними. Кроме того, если вы зададите пустое значение ESCAPED BY, то можно непреднамеренно создать вывод, который нельзя будет правильно прочитать с помощью LOAD DATA. Например, предшествующий вывод будет выглядеть следующим образом, если символ экранирования пустой. Обратите внимание, что во втором поле в четвертой строке присутствует запятая после кавычки, что (неправильно) указывает на окончание поля:

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

Для ввода символ ENCLOSED BY, если он присутствует, удаляется с концов значений полей. (Это верно независимо от того, задан ли OPTIONALLY; OPTIONALLY не влияет на интерпретацию ввода.) Вхождения символа ENCLOSED BY, предваряемые символом ESCAPED BY, интерпретируются как часть текущего значения поля.

Если поле начинается с символа ENCLOSED BY, вхождения этого символа распознаются как завершающие значение поля только если за ними следует последовательность поля или строки TERMINATED BY. Чтобы избежать неоднозначностей, вхождения символа ENCLOSED BY в значении поля могут быть удвоены и интерпретируются как один экземпляр символа. Например, если задан ENCLOSED BY '"', кавычки обрабатываются так:

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY управляет чтением или записью специальных символов:

Для входных данных, если символ FIELDS ESCAPED BY не пустой, все вхождения этого символа удаляются, а следующий символ интерпретируется буквально как часть значения поля. Некоторые двухсимвольные последовательности являются исключениями, где первый символ — символ экранирования. Эти последовательности показаны в следующей таблице (используя \ для символа экранирования). Правила обработки NULL описаны позже в этом разделе.

Символ	Последовательность экранирования
`\0`	Символ ASCII NUL (`X'00'`)
`\b`	Символ возврата на позицию ввода
`\n`	Символ новой строки (перевод строки)
`\r`	Символ возврата каретки
`\t`	Символ табуляции
`\Z`	ASCII 26 (Ctrl+Z)
`\N`	NULL

Дополнительную информацию об синтаксисе экранирования \ см. в разделе 9.1.1, «Строковые литералы».

Если символ FIELDS ESCAPED BY пустой, интерпретация последовательностей экранирования не происходит.

Для вывода, если символ FIELDS ESCAPED BY не пустой, он используется в качестве префикса для следующих символов на выходе:
- Символ FIELDS ESCAPED BY.
- Символ FIELDS [OPTIONALLY] ENCLOSED BY.
- Первый символ значений FIELDS TERMINATED BY и LINES TERMINATED BY, если символ ENCLOSED BY пустой или не указан.
- ASCII 0 (то, что на самом деле написано после символа экранирования, — это ASCII 0, а не байт со значением ноль).
Если символ FIELDS ESCAPED BY пустой, символы не экранируются, а NULL выводится как NULL, а не \N. Вероятно, не следует указывать пустой символ экранирования, особенно если значения полей в ваших данных содержат какие-либо из символов, указанных в списке выше.

В некоторых случаях параметры обработки полей и строк взаимодействуют:

Если LINES TERMINATED BY — пустая строка, а FIELDS TERMINATED BY — непустая, строки также завершаются символом FIELDS TERMINATED BY.
Если значения FIELDS TERMINATED BY и FIELDS ENCLOSED BY оба пусты (''), используется формат фиксированной строки (без разделителей). В формате фиксированной строки разделители между полями не используются (но вы по-прежнему можете использовать разделитель строк). Вместо этого значения столбцов читаются и записываются с шириной поля, достаточной для размещения всех значений в поле. Для TINYINT, SMALLINT, MEDIUMINT, INT и BIGINT ширины полей составляют соответственно 4, 6, 8, 11 и 20, независимо от объявленной ширины отображения.

LINES TERMINATED BY по-прежнему используется для разделения строк. Если строка не содержит всех полей, оставшиеся столбцы устанавливаются в значения по умолчанию. Если у вас нет разделителя строки, вы должны установить это значение в ''. В этом случае текстовый файл должен содержать все поля для каждой строки.

Формат фиксированной строки также влияет на обработку значений NULL, как описано далее.

Примечание

Формат фиксированного размера не работает, если вы используете многобайтовую кодировку символов.

Обработка значений NULL варьируется в зависимости от параметров FIELDS и LINES:

Для значений FIELDS и LINES по умолчанию NULL записывается как значение поля \N для вывода, а значение поля \N читается как NULL для ввода (при условии, что символ ESCAPED BY равен \).
Если FIELDS ENCLOSED BY не пусто, поле, содержащее литеральное слово NULL в качестве значения, читается как значение NULL. Это отличается от слова NULL, заключенного в символы FIELDS ENCLOSED BY, которое читается как строка 'NULL'.
Если FIELDS ESCAPED BY пусто, NULL записывается как слово NULL.
В формате фиксированной строки (который используется, когда FIELDS TERMINATED BY и FIELDS ENCLOSED BY оба пусты), NULL записывается как пустая строка. Это приводит к тому, что оба значения NULL и пустые строки в таблице становятся неразличимыми при записи в файл, так как оба записываются как пустые строки. Если вам нужно отличить эти два значения при повторном чтении файла, не следует использовать формат фиксированной строки.

Попытка загрузить NULL в столбец NOT NULL приводит к появлению предупреждения или ошибки в соответствии с правилами, описанными в Присвоение значений столбцам.

Некоторые случаи не поддерживаются LOAD DATA:

Строки фиксированного размера (FIELDS TERMINATED BY и FIELDS ENCLOSED BY оба пусты) и столбцы BLOB или TEXT.
Если вы указываете один разделитель, который совпадает или является префиксом другого, LOAD DATA не может правильно интерпретировать входные данные. Например, следующее условие FIELDS вызовет проблемы:
```
FIELDS TERMINATED BY '"' ENCLOSED BY '"'
```
Если FIELDS ESCAPED BY пусто, значение поля, содержащее вхождение FIELDS ENCLOSED BY или LINES TERMINATED BY, за которым следует значение FIELDS TERMINATED BY, приводит к тому, что LOAD DATA останавливает чтение поля или строки слишком рано. Это происходит потому, что LOAD DATA не может правильно определить, где заканчивается значение поля или строки.

Спецификация списка столбцов

Следующий пример загружает все столбцы таблицы persondata:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

По умолчанию, когда список столбцов не указан в конце инструкции LOAD DATA, ожидается, что строки ввода будут содержать поле для каждого столбца таблицы. Если вы хотите загрузить только некоторые столбцы таблицы, укажите список столбцов:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(col_name_or_user_var [, col_name_or_user_var] ...);

Вам также необходимо указать список столбцов, если порядок полей во входном файле отличается от порядка столбцов в таблице. В противном случае MySQL не сможет понять, как сопоставить поля ввода со столбцами таблицы.

Предварительная обработка входных данных

Каждое вхождение col_name_or_user_var в синтаксисе LOAD DATA является либо именем столбца, либо пользовательской переменной. С пользовательскими переменными, клауза SET позволяет выполнять преобразования предварительной обработки их значений перед назначением результата столбцам.

Пользовательские переменные в клаузе SET могут использоваться различными способами. В следующем примере используется первый входной столбец непосредственно для значения t1.column1, а второй входной столбец назначается пользовательской переменной, подвергаемой операции деления, прежде чем использоваться для значения t1.column2:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

Клауза SET может использоваться для предоставления значений, не полученных из входного файла. Следующее утверждение устанавливает column3 на текущую дату и время:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

Вы также можете отбросить входное значение, назначив его пользовательской переменной и не назначив переменную ни одному столбцу таблицы:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

Использование списка столбцов/переменных и клаузы SET подчиняется следующим ограничениям:

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

Назначение значений столбцам

Для обработки строки ввода, LOAD DATA разбивает ее на поля и использует значения в соответствии со списком столбцов/переменных и клаузой SET, если они присутствуют. Затем результирующая строка вставляется в таблицу. Если для таблицы существуют триггеры BEFORE INSERT или AFTER INSERT, они активируются соответственно до или после вставки строки.

Интерпретация значений полей и присваивание столбцам таблицы зависит от этих факторов:

Режим SQL (значение системной переменной sql_mode). Режим может быть нестрогим или строгим различными способами. Например, строгий режим SQL может быть включен, или режим может включать такие значения, как NO_ZERO_DATE или NO_ZERO_IN_DATE.
Наличие или отсутствие модификаторов IGNORE и LOCAL.

Эти факторы комбинируются для создания строгой или нестрогой интерпретации данных LOAD DATA:

Интерпретация данных является строгой, если режим SQL строгий, и ни модификатор IGNORE, ни LOCAL не указаны. Ошибки завершают операцию загрузки.
Интерпретация данных является нестрогой, если режим SQL нестрогий или указан модификатор IGNORE или LOCAL. (В частности, любой из указанных модификаторов переопределяет строгий режим SQL.) Ошибки становятся предупреждениями, и операция загрузки продолжается.

Строгая интерпретация данных использует следующие правила:

Слишком много или слишком мало полей приводит к ошибке.
Присваивание NULL (то есть, \N) нестолбцу NULL приводит к ошибке.
Значение, выходящее за пределы диапазона для типа данных столбца, приводит к ошибке.
Некорректные значения вызывают ошибки. Например, значение, такое как 'x' для числового столбца, приводит к ошибке, а не к преобразованию в 0.

В противоположность этому, нестрогая интерпретация данных использует следующие правила:

Если строка ввода содержит слишком много полей, дополнительные поля игнорируются, и увеличивается количество предупреждений.
Если строка ввода содержит слишком мало полей, столбцам, для которых отсутствуют входные поля, назначаются их значения по умолчанию. Назначение значений по умолчанию описано в разделе 11.6, «Значения по умолчанию типов данных».
Присваивание NULL (то есть, \N) нестолбцу NULL приводит к назначению неявного значения по умолчанию для типа данных столбца. Неявные значения по умолчанию описаны в разделе 11.6, «Значения по умолчанию типов данных».
Некорректные значения вызывают предупреждения, а не ошибки, и преобразуются к “наиболее близкому” допустимому значению для типа данных столбца. Примеры:
- Значение, такое как 'x' для числового столбца, приводит к преобразованию в 0.
- Числовое или временное значение, выходящее за пределы диапазона, обрезается до ближайшей границы диапазона для типа данных столбца.
- Некорректное значение для столбца DATETIME, DATE или TIME вставляется как неявное значение по умолчанию, независимо от настройки режима SQL NO_ZERO_DATE. Неявное значение по умолчанию — соответствующее значение “ноль” для типа ('0000-00-00 00:00:00', '0000-00-00' или '00:00:00'). См. раздел 11.2, «Типы данных даты и времени».
LOAD DATA интерпретирует пустое значение поля иначе, чем пропущенное поле:
- Для строковых типов столбец устанавливается в пустую строку.
- Для числовых типов столбец устанавливается в 0.
- Для типов даты и времени столбец устанавливается в соответствующее значение “ноль” для типа. См. раздел 11.2, «Типы данных даты и времени».
Это те же значения, которые получаются, если вы явно присваиваете пустую строку строковому, числовому или типу даты или времени в операторе INSERT или UPDATE.

Столбцы TIMESTAMP устанавливаются на текущую дату и время только если есть значение NULL для столбца (то есть \N) и столбец не объявлен для разрешения значений NULL, или если значение по умолчанию столбца TIMESTAMP является текущей меткой времени, и оно опущено из списка полей, когда список полей указан.

LOAD DATA рассматривает все входные данные как строки, поэтому вы не можете использовать числовые значения для столбцов ENUM или SET так, как вы можете с операторами INSERT. Все значения ENUM и SET должны быть указаны как строки.

BIT значения не могут быть загружены напрямую с использованием двоичной нотации (например, b'011010'). Чтобы обойти это, используйте клаузу SET для удаления ведущего b' и последующего ' и выполните преобразование из двоичной системы счисления (основание 2) в десятичную (основание 10), чтобы MySQL загрузил значения в столбец BIT должным образом:

$> cat /tmp/bit_test.txt
b'10'
b'1111111'
$> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
       INTO TABLE bit_test (@var1)
       SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-3), 2, 10) AS UNSIGNED);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT BIN(b+0) FROM bit_test;
+----------+
| BIN(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

Для значений BIT в 0b двоичной нотации (например, 0b011010), используйте клаузу SET вместо этого, чтобы удалить ведущие 0b:

SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2, 10) AS UNSIGNED)

Поддержка таблиц с разбиением

LOAD DATA поддерживает явное выделение разделов с помощью предложения PARTITION с перечислением одного или нескольких разделов, подразделов или того и другого, разделённых запятыми. Если при этом какие-либо строки из файла не могут быть вставлены ни в один из указанных в списке разделов или под-разделов, операция завершается с ошибкой Найдена строка, не соответствующая заданному набору разделов. Более подробную информацию и примеры см. в разделе 22.5, «Выбор разделов».

Для таблиц с разбиением, использующих движки хранилища, которые используют блокировки таблиц, такие как MyISAM, LOAD DATA не может отключать блокировки по разделам. Это не относится к таблицам, использующим движки хранилища, которые используют блокировки на уровне строк, такие как InnoDB. Более подробную информацию см. в разделе 22.6.4, «Разбиение и блокировки».

Соображения по конкурентности

С модификатором LOW_PRIORITY выполнение оператора LOAD DATA откладывается до тех пор, пока другие клиенты не перестанут читать из таблицы. Это влияет только на движки хранилища, которые используют только блокировки на уровне таблицы (например, MyISAM, MEMORY и MERGE).

С модификатором CONCURRENT и таблицей MyISAM, которая удовлетворяет условию одновременных вставок (то есть не содержит свободных блоков посередине), другие потоки могут извлекать данные из таблицы, в то время как LOAD DATA выполняется. Этот модификатор немного влияет на производительность LOAD DATA, даже если в данный момент никакой другой поток не использует таблицу.

Информация о результатах оператора

После завершения оператора LOAD DATA он возвращает строку информации в следующем формате:

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Предупреждения возникают в тех же обстоятельствах, что и при вставке значений с помощью оператора INSERT (см. раздел 13.2.5, «Оператор INSERT»), за исключением того, что LOAD DATA также генерирует предупреждения, если в входной строке слишком мало или слишком много полей.

Чтобы получить список первых max_error_count предупреждений, как информацию о проблемах, можно использовать SHOW WARNINGS. См. раздел 13.7.5.40, «Оператор SHOW WARNINGS».

Если вы используете API C, информацию об операторе можно получить, вызвав функцию. См. .

Соображения по репликации

Информация о LOAD DATA в отношении репликации представлена в разделе 16.4.1.18, «Репликация и LOAD DATA».

Разное

В Unix, если вам нужно, чтобы LOAD DATA считывал данные из канала, можно использовать следующий метод (в примере каталог / загружается в таблицу db1.t1):

mkfifo /mysql/data/db1/ls.dat
chmod 666 /mysql/data/db1/ls.dat
find / -ls > /mysql/data/db1/ls.dat &
mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

Здесь необходимо запустить команду, генерирующую данные, которые будут загружаться, и команды mysql либо в разных терминалах, либо запустить процесс генерации данных в фоновом режиме (как показано в предыдущем примере). Если этого не сделать, канал будет блокироваться, пока данные не будут считаны процессом mysql.