15.2.9 LOAD DATA Statement — MySQL 8.4

15.2.9. Заявление 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. (См. Раздел 15.2.13.1, «Заявление SELECT ... INTO».) Для записи данных из таблицы в файл используйте SELECT ... INTO OUTFILE. Для чтения файла обратно в таблицу используйте LOAD DATA. Синтаксис положений FIELDS и LINES одинаков для обоих заявлений.

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

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

Операция Non-LOCAL против LOCAL

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

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

LOCAL работает только в том случае, если сервер и ваш клиент настроены для этого. Например, если mysqld был запущен с отключенной системной переменной local_infile, LOCAL приводит к ошибке. См. Раздел 8.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 доступа, чтобы определить местоположение файлов .sdi для оператора IMPORT TABLE.

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

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

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

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

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

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

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

Модификатор 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 после загрузки файла. См. Раздел 10.2.5.1, «Оптимизация операторов INSERT».

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

Для обоих операторов 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 (Control+Z)
`\N`	NULL

Для получения дополнительной информации об обработке последовательностей экранирования \, см. Раздел 11.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, когда модификатор REPLACE опущен.) Ошибки становятся предупреждениями, и операция загрузки продолжается.

Жёсткая интерпретация данных использует эти правила:

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

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

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

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

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

BIT значения не могут быть загружены напрямую с использованием двоичной записи (например, b'011010'). Для обхода этого используйте пункт SET для удаления ведущих b' и последующих ' и выполните преобразование из двоичной системы счисления в десятичную, чтобы 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 с перечислением одного или нескольких разделов, подразделов или того и другого, разделенных запятыми. При использовании этого условия, если какие-либо строки из файла не могут быть вставлены ни в один из указанных в списке разделов или подразделов, операция завершается с ошибкой Найдена строка, не соответствующая заданному набору разделов. Для получения более подробной информации и примеров см. Раздел 26.5, «Выбор разделов».

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

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

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

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

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

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

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

Вы можете использовать SHOW WARNINGS для получения списка первых max_error_count предупреждений как информации о том, что пошло не так. См. Раздел 15.7.7.42, «Оператор SHOW WARNINGS».

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

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

LOAD DATA считается небезопасным для репликации на основе операторов. Если вы используете LOAD DATA с binlog_format=STATEMENT, каждая реплика, на которой должны быть применены изменения, создаёт временный файл, содержащий данные. Этот временный файл не шифруется, даже если шифрование бинарного лога активно на источнике. Если требуется шифрование, используйте реплику на основе строк или смешанный формат бинарного лога, для которых реплики не создают временный файл. Более подробную информацию о взаимодействии LOAD DATA и репликации см. в Разделе 19.5.1.19, «Репликация и 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.