Синтаксис LOAD DATA INFILE

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'имя_файла.txt' [REPLACE | IGNORE] INTO TABLE имя_таблицы[FIELDS
[TERMINATED BY 't']
[[OPTIONALLY] ENCLOSED BY "]
[ESCAPED BY f\f] ] [LINES
[STARTING BY M]
[TERMINATED BY fn f ] ]
[IGNORE количество LINES] [{имя_столбца,...)]

Оператор LOAD DATA INFILE читает строки из текстового файла и загружает их в таб­лицу на очень высокой скорости.
Вы можете также загружать файлы данных с помощью утилиты mysql import. Она работает, посылая на сервер оператор LOAD data INFILE. Опция —-local заставляет ути­литу mysqlimport читать файл данных с клиентского хоста. Вы можете указать опцию —compress для повышения производительности в медленных сетях, если клиент и сер­вер поддерживают сжатый протокол.
Если указано ключевое слово LOW_PRIORITY, выполнение оператора LOAD DATA откла­дывается до тех пор, пока все остальные клиенты не завершат чтение.
Если указано ключевое слово CONCURRENT с таблицей MyISAM, которая удовлетворяет условию параллельных вставок (то есть не имеет свободных блоков в середине файла), то другие потоки смогут извлекать данные из таблицы одновременно с выполнением LOAD DATA. Применение этой опции немного сказывается на производительности LOAD DATA, даже если ни один другой поток с этой таблицей не работает.
Если указано ключевое слово LOCAL, оно касается клиентской части соединения.

1. Если слово LOCAL указано, файл читается клиентской программой на хосте клиента и отправляется на сервер.
2. Если слово LOCAL не указано, загружаемый файл должен находиться на хосте сервера, и читается сервером непосредственно.

LOCAL доступно в MySQL 3.22.6 и более поздних версиях.
Из соображений безопасности при чтении текстовых файлов, расположенных на сер­вере, файлы должны либо находиться в каталоге данных, либо быть доступными всем по чтению. Кроме того, чтобы использовать LOAD DATA с серверными файлами, вы должны иметь привилегию FILE.
Загрузка с опцией LOCAL идет несколько медленнее, чем когда вы даете серверу воз­можность непосредственного доступа к загружаемым файлам, потому что в этом случае содержимое файлов передается по сети через клиент-сер верное соединение. С другой стороны, в этом случае вам не нужны привилегии FILE.
Начиная с версий MySQL 3.23.49 и MySQL 4.0.2 (4.0.13 для Windows), LOCAL работа­ет, только если и клиент, и сервер разрешают это. Например, если mysqld запущен с оп­цией —local-inf ile=0, то LOCAL работать не будет.

Если вам нужно с помощью LOAD DATA читать из программного канала, вы можете применить следующую технику:
mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x1 INTO TABLE x" x
Если вы работаете с версией MySQL, предшествующей 3.23.25, то эту технику мож­но применять только с LOAD DATA LOCAL INFILE.
Если у вас версия MySQL, предшествующая 3.23.24, то вы не сможете читать с по­мощью оператора LOAD DATA INFILE из FIFO. Если вам нужно читать из FIFO (напри­мер, из выходного потока gunzip), применяйте вместо этого LOAD DATA LOCAL INFILE.
При поиске файла в своей файловой системе сервер руководствуется следующими правилами:

1. Если задан абсолютный путь, сервер его использует, как есть.
2. Если задан относительный путь с одним или более ведущими компонентами, сервер ищет файлы относительно своего каталога данных.
3. Если задано имя файла без ведущих компонентов пути, сервер ищет файл в каталоге данных базы данных по умолчанию.

Отметим, что из этих правил следует, что файл с именем ./myfile.txt читается из каталога данных сервера, в то время как файл с именем myfile,txt читается из каталога данных базы данных по умолчанию. Например, следующий оператор LOAD DATA INFILE читает файл data.txt из каталога данных базы dbl, потому что dbl -текущая база дан­ных, несмотря на то, что оператор загружает данные в базу данных db2:
mysql> USE dbl;
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
Ключевые слова REPLACE и IGNORE управляют работой с входными строками, которые дублируют существующие по значению уникальных ключей.
Если указано REPLACE, входные строки заменяют существующие строки (другими словами, строки, которые имеют те же значения первичных или уникальных ключей, как и существующие в таблице строки). См. раздел Синтаксис REPLACE
Если указано IGNORE, то входные строки, которые дублируют существующие строки с теми же значениями первичных или уникальных ключей, пропускаются. Если не указана ни одна, ни другая опции, то поведение зависит от того, указано ли ключевое слово local. При отсутствии LOCAL, в случае обнаружения дублирования ключа генерируется ошибка, а остаток текстового файла игнорируется. При наличии LOCAL, поведение по умолчанию будет таким же, как если бы было указано IGNORE. Это объясняется тем, что сервер не в состоянии остановить передачу файла в процессе выполнения этой операции.
Если вы хотите игнорировать ограничения внешних ключей в процессе операции за­грузки данных, вы можете выполнить оператор SET FOREIGN_KEY_CHECKS=0 перед запус­ком LOAD DATA.
Если вы запускаете LOAD DATA для пустой таблицы MyISAM, все неуникальные индек­сы создаются в отдельном задании (как для REPAIR TABLE). Обычно это приводит к тому, что при наличии многих индексов LOAD DATA выполняется гораздо быстрее. Как правило, это работает очень быстро, но в некоторых особых случаях вы можете создать индексы даже еще быстрее, выключив их через ALTER TABLE.. .DISABLE KEYS перед загрузкой

1. Искать разделители строк в начале строк.
2. Не пропускать никаких префиксов строки.
3. Разбивать строку на поля по знакам табуляции.
4. Не ожидать, что поля будут заключены в кавычки.
5. Интерпретировать появление знака табуляции, перевода строки или символа '\ которым предшествует с\ как литеральные символы, являющиеся частью значения поля.

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

1. Пишет знаки табуляции между полями.
2. Не окружает значения полей кавычками.

• Использует *' для выделения знаков табуляции, перевода строк или '\ встре­чающихся внутри значений полей.
  
• Пишет символ перевода строки в конце строк.
  

Опция IGNORE количество LINES служит для игнорирования заданного количества строк в начале файла. Например, вы можете воспользоваться IGNORE I LINES, чтобы пропустить начальную строку, содержащую имена столбцов:
mysql> LOAD DATA INFILE '/tmp/test.txt' -> INTO TABLE test IGNORE 1 LINES;
Когда вы применяете SELECT... INTO OUTFILE в связке с LOAD DATA INFILE для записи данных из базы в файл и последующего его чтения и загрузки обратно в базу, опции управления строками и полями для обоих операторов должны совпадать. В противном случае LOAD DATA INFILE не сможет правильно интерпретировать содержимое текстово­го файла. Предположим, что вы с помощью SELECT.. .INTO OUTFILE вывели данные в текстовый файл, разделяя поля запятыми:
mysql> SELECT * INTO OUTFILE 'data.txt' -> FIELDS TERMINATED BY',' -> FROM table2;
Чтобы прочитать разделенный запятыми файл обратно, правильно будет поступить так:
mysql> LOAD DATA INFILE 'data.txt1 INTO TABLE table2 -> FIELDS TERMINATED BY
Если вместо этого вы попытаетесь прочитать его оператором, приведенным ниже, это не сработает, потому что LOAD DATA INFILE будет искать символы табуляции между значениями полей:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY 't';
Наиболее вероятным результатом будет интерпретация входной строки как единст­венного поля.
LOAD DATA INFILE также может использоваться для чтения файлов из внешних ис­точников. Например, некоторый файл может иметь поля, разделенные запятыми и за­ключенные в двойные кавычки. Если строки в файле разделены символом новой строки, приведенный ниже пример иллюстрирует, какие должны быть установлены опции раз­делителей строк и столбцов для загрузки файла:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE имя_таблицы-> FIELDS TERMINATED BY 1,1 ENCLOSED BY "" -> LINES TERMINATED BY 'n';
Любым опциям, задающим ограничители строк и столбцов, можно указывать в каче­стве аргументов пустые строки ("). Если же аргументы - не пустые строки, то значения ДЛЯ FIELDS [OPTIONALLY] ENCLOSED BY И FIELDS ESCAPED BY ДОЛЖНЫ быть ОДНОСИМ­ВОЛЬНЫМИ. Аргументы ОПЦИЙ FIELDS TERMINATED BY, LINES STARTING BY И LINES TERMINATED BY могут иметь длину более одного символа. Например, чтобы писать стро­ки, разделенные символами возврат каретки/перевод строки, либо чтобы читать файлы, содержащие такие строки, указывайте конструкцию LINES TERMINATED BY 'rn'.
Чтобы прочитать файл, разделенный по строкам символами %%, можно поступить следующим образом:
mysql> CREATE TABLE jokes
-> (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> joke TEXT NOT NULL);

mysql> LOAD DATA INFILE '/tmp/jokes,txf INTO TABLE jokes -> FIELDS TERMINATED BY '' -> LINES TERMINATED BY '\n%%\n' (joke);
FIELDS [OPTIONALLY] ENCLOSED BY управляет ограничителями (кавычками) полей. При выводе (SELECT... INTO OUTFILE), если пропустить слово OPTIONALLY, все поля бу­дут окружены символом, указанным в ENCLOSED BY. Пример такого вывода (с использо­ванием запятой в качестве разделителя полей) показан ниже:
"1","а string","100.20"
"2","a string containing a , comma","102.20"
"3","а string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"
Если вы указываете OPTIONALLY, то символ ENCLOSED BY применяется только для за­ключения в кавычки полей типа CHAR и VARCHAR:
1,"а string",100.20
2,"a string containing a , comma",102.20
3,"а string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20
Обратите внимание, что вхождения символа, указанного в ENCLOSED BY, внутри зна­чения поля предваряется символом, заданным в ESCAPED BY. Кроме того, если указать пустое значение для ESCAPED BY, возможно, что будет сгенерирован файл, который LOAD DATA INFILE не сумеет правильно загрузить.
Например, если символ отмены оставить пустым, приведенный выше вывод будет выглядеть так, как показано ниже. Несложно заметить, что второе поле в четвертой строке содержит запятую, следующую за кавычкой, которая (ошибочно) будет выгля­деть как разделитель полей.
1,"а string",100.20
2,"a string containing a , comma",102.20
3,"а string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20
При вводе символ ENCLOSED BY, если он есть, удаляется из конца значения полей. (Это верно вне зависимости от того, указано или нет слово 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 не пустой, он используется в качестве префикса для следую­щих символов в выводе:

1. Символа FIELDS ESCAPED BY.
2. Символа FIELDS [OPTIONALLY] ENCLOSED BY.
3. Первого СИМВОЛа последовательностей FIELDS TERMINATED BY И LINES TERMINATED BY.
4. ASCII 0 (который пишется вслед за символом отмены как ASCII '0', а не нулевой байт).

Если символ FIELDS ESCAPED BY пуст, никакие символы не предваряются символами отмены, и NULL выводится как NULL, а не \N. Вероятно, это не очень хорошая мысль -оставлять пустым аргумент FIELDS ESCAPED BY, особенно, если значения полей ваших данных содержат любой из упомянутых символов.
При вводе, если FIELDS ESCAPED BY не пуст, то при появлении этого символа в строке значения он удаляется, а следующий за ним символ читается литерально, как часть зна­чения поля. Исключениями являются последовательности '0' или 'N' (SYS-PAGE-CONTENT или \N, если символом отмены выбран '\'). Эти последовательности интерпретируются, соответст­венно, как ASCII NUL (нулевой байт) и NULL. Правила обращения с NULL описаны ниже в настоящем разделе.
Более подробную информацию о синтаксисе отмены '\' можно найти в разделе Литеральные значения
В некоторых случаях опции, управляющие полями и строками, взаимодействуют ме­жду собой:

1. Если указана пустая строка для LINES TERMINATED BY, a FIELDS TERMINATED BY непуст, то разделителем строк также служит LINES TERMINATED BY.
2. ЕСЛИ пусты И FIELDS TERMINATED BY И FIELDS ENCLOSED BY, ИСПОЛЬЗуется формат фиксированной строки (без разделителей). В этом формате не применяется никаких разделителей между полями (но можно иметь разделитель строк). Вместо этого значения столбцов пишутся и читаются с использованием ширины отображения столбцов. Например, если столбец объявлен как INT (7), значения столбца записываются в семисимвольное поле. При вводе значения столбца извлекаются чтением семи символов.

LINES TERMINATED BY по-прежнему используется для разделения строк. Если стро­ка не содержит всех полей, остальным столбцам присваиваются их значения по умолчанию. Если у вас нет терминатора строки, его значение нужно установить в 1'. В этом случае текстовый файл должен содержать все поля в каждой строке. Формат с фиксированной длиной строки также касается работы со значениями NULL, как описано ниже. Следует отметить, что формат фиксированной длины не работает, если используется многобайтный набор символов (например, Unicode).
Обработка значений NULL варьируется в зависимости от применяемых опций FIELDS и LINES:

1. При значениях FIELDS и LINES по умолчанию NULL пишется как значение поля ввиде \N для вывода и это же значение \N читается как NULL при вводе (предполагая, что символ ESCAPED BY установлен в '\')-
2. Если FIELDS ENCLOSED BY не пустой, то поле, содержащее литеральное слово NULL, читается как значение NULL. Это отличается от случая, когда слово NULL ограничено символами FIELDS ENCLOSED BY, когда значение читается, как строка'NULL'.
3. Если FIELDS ESCAPED BY пустое, NULL пишется как слово NULL.

• При формате с фиксированной длиной строки (что случается, когда и FIELDS TERMINATED BY, и FIELDS ENCLOSED BY пустые) NULL записывается как пустая строка. Отметим, что это приводит к тому, что значения NULL и пустые строки в таблице становятся неразличимы при записи в файл, поскольку в обоих случаях пишутся пустые строки. Если вам необходимо делать различие между ними, избегайте использования формата с фиксированной длиной строки.
  Ниже представлены некоторые случаи, не поддерживаемые LOAD DATA INFILE:
  1. Строки фиксированной ДЛИНЫ (FIELDS TERMINATED BY И FIELDS ENCLOSED BY nycтые) при наличии столбцов тира TEXT или BLOB.
  2. Если вы указываете разделитель, который совпадает с префиксом другого, LOAD DATA INFILE не может правильно интерпретировать входной поток. Например, следующий вариант приведет к проблемам:

  FIELDS TERMINATED BY "" ENCLOSED BY '"
  

  • Если FIELDS ESCAPED BY пуст, значения полей, которые включают в себя символы FIELDS ENCLOSED BY ИЛИ LINES TERMINATED BY С последующим СИМВОЛОМ LINES TERMINATED BY, заставят LOAD DATA INFILE слишком рано прекратить чтение файла или строки. Это произойдет потому, что LOAD DATA INFILE не может правильно определить, где завершается значение поля или строки. Следующий пример загружает все столбцы таблицы persondata: mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
    По умолчанию, если в конце оператора LOAD DATA INFILE не приведен список столб­цов, ожидается, что во входящей строке содержатся поля для каждого столбца таблицы. Если вы хотите загрузить только некоторые из столбцов таблицы, указывайте список столбцов:
    mysql> LOAD DATA INFILE 'persondata.txt1
    -> INTO TABLE persondata (coll,col2,...);
    Вы также должны указывать список столбцов, если порядок полей во входном файле отличается от порядка столбцов в таблице. В противном случае MySQL не сможет уста­новить соответствие между входными полями и столбцами таблиц.
    Если входной файл имеет слишком мало полей в строках, то недостающим столбцам будут присвоены значения по умолчанию. Присвоение значений по умолчанию описано в разделе Синтаксис CREATE TABLE
    Пустые значения полей интерпретируются иначе, чем пропущенные:
    1. Для строковых типов - столбцу присваивается пустая строка.
    2. Для числовых типов - столбцу присваивается 0.
    3. Для типов даты и времени - столбец устанавливается в соответствующее типу
       "нулевое" значение. См. раздел Типы даты и времени

    Это те же значения, что получаются в результате явного присвоения пустой строки столбцам этих типов в операторах INSERT или UPDATE.
    Значения столбцов типа TIMESTAMP устанавливаются в текущую дату и время, только если им присваивается значение NULL (то есть, \N), либо если столбец этого типа пропу­щен в списке полей, если список полей приведен.

    LOAD DATA INFILE рассматривает весь ввод, как строковый, поэтому вы не можете использовать числовые значения для столбцов типа ENUM или SET, как это допускается в операторах INSERT. Все значения ENUM или SET должны указываться как строки!
    Когда оператор LOAD DATA INFILE завершает работу, он возвращает информацион­ную строку в следующем формате:
    Records: I Deleted: 0 Skipped: 0 Warnings: О
    Если вы работаете с программным интерфейсом С API, то можете получить инфор­мацию об этом операторе, обратившись к функции mysql_info().
    Предупреждения, которые появляются при некоторых условиях, такие же, как при вставке значений оператором INSERT (см. раздел 6.1.4), за исключением того, что LOAD DATA INFILE кроме них генерирует предупреждения о том, что во входном файле слиш­ком мало или слишком много полей. Предупреждения нигде не сохраняются, количество предупреждений может быть использовано только как признак того, что все прошло ус­пешно.
    Начиная с MySQL 4.1.1, вы можете использовать SHOW WARNINGS для получения спи­ска первых max_error_count предупреждений в качестве информации о том, что при загрузке прошло не так, как надо. См. раздел Синтаксис SHOW WARNINGS
    До MySQL 4.1.1 только количество предупреждений было признаком того, что за­грузка прошла не гладко. Если вы получаете предупреждение и хотите знать точно, почему оно появились, единственный путь сделать это - с помощью SELECT.. .INTO OUTFILE выгрузить дамп таблицы в другой файл и сравнить его с оригинальным вход­ным файлом.