15.2.9 LOAD DATA Statement — MySQL 9.2

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 (значения, разделённые запятыми), где строки имеют поля, разделённые запятыми, заключёнными в двойные кавычки, с начальной строкой имён столбцов. Если строки в таком файле завершаются парами возврата каретки/новой строки, показанный здесь оператор иллюстрирует параметры обработки полей и строк, которые вы бы использовали для загрузки файла:

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)

END_OF_DOCUMENT_MARKER

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

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

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

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

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