LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES TERMINATED BY '\n'] [IGNORE number LINES] [(col_name,...)]
Команда LOAD DATA INFILE
читает строки из текстового файла и вставляет
их в таблицу с очень высокой скоростью. Если задано ключевое слово LOCAL
,
то файл читается с клиентского хоста. Если же LOCAL
не указывается, то
файл должен находиться на сервере. (Опция LOCAL
доступна в версии MySQL
3.22.6 и более поздних.)
Если текстовые файлы, которые нужно прочитать, находятся на сервере, то из
соображений безопасности эти файлы должны либо размещаться в директории
базы данных, либо быть доступными для чтения всем пользователям. Кроме
того, для применения команды LOAD DATA INFILE
к серверным файлам
необходимо обладать привилегиями FILE
для серверного хоста.
See Раздел 4.2.7, «Привилегии, предоставляемые MySQL».
В версиях MySQL 3.23.49 и MySQL 4.0.2 команда LOCAL
не будет работать в
случаях, если демон mysqld
запущен с параметром --local-infile=0
или если
для клиента не включена возможность поддержки LOCAL
. See Раздел 4.2.4, «Вопросы безопасности, относящиеся к команде LOAD DATA LOCAL».
Если указывается ключевое слово LOW_PRIORITY
, то выполнение данной команды
LOAD DATA
будет задержано до тех пор, пока другие клиенты не завершат
чтение этой таблицы.
Если указывается ключевое слово CONCURRENT
при работе с таблицами MyISAM
,
то другие потоки могут извлекать данные из таблицы во время выполнения
команды LOAD DATA
. Использование этой возможности, конечно, будет немного
влиять на производительность выполнения LOAD DATA
, даже если никакой
другой поток не использует данную таблицу в это же время.
При применении опции LOCAL
выполнение может происходить несколько
медленнее в сравнении с предоставлением серверу доступа к файлам напрямую,
поскольку содержимое файла должно переместиться с клиентского хоста на
сервер. С другой стороны, в этом случае нет необходимости в привилегиях
FILE
для загрузки локальных файлов.
При использовании версий MySQL до 3.23.24 при помощи команды LOAD DATA INFILE
нельзя выполнять чтение из FIFO
. Если необходимо читать из FIFO
(например, стандартный вывод gunzip
), следует использовать LOAD DATA LOCAL INFILE
.
Можно также загружать файлы данных, используя утилиту mysqlimport
. Эта
утилита выполняет загрузку файлов путем посылки на сервер команд LOAD DATA INFILE
. Опция --local
заставляет mysqlimport
читать файлы данных с
клиентского хоста. Можно указать параметр --compress
, чтобы получить
лучшую производительность при работе через медленные сети, если и клиент,
и сервер поддерживают протокол сжатия данных.
В случаях, когда файлы находятся на сервере, последний действует по следующим правилам:
Если задан абсолютный (полный) путь к файлу, то сервер использует этот путь без изменений.
Если задан относительный путь к файлу с указанием одного или более начальных каталогов, то поиск файла будет осуществляться относительно указанных каталогов в каталоге данных сервера (
datadir
).Если дается путь к файлу без указания начальных каталогов, то сервер ищет этот файл в директории используемой базы данных.
Отсюда следует, что файл, заданный как ./myfile.txt
, читается из
серверного каталога данных, в то время как файл, заданный как myfile.txt
,
читается из каталога используемой базы данных. Например, следующая команда
LOAD DATA
читает файл data.txt
в каталоге базы данных для db1
, поскольку
db1
является текущей базой данных, даже если эта команда явно содержит
указание загрузить файл в таблицу базы данных db2
:
mysql> USE db1; mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;
Ключевые слова REPLACE
и IGNORE
управляют обработкой входных записей,
которые дублируют существующие записи с теми же величинами уникальных
ключей. Если указать REPLACE
, то новые строки заменят существующие с таким
же уникальным ключом. Если указать IGNORE
, то входные строки, имеющие тот
же уникальный ключ, что и существующие, будут пропускаться. Если не указан
ни один из параметров, то при обнаружении дублирующегося значения ключа
возникает ошибка и оставшаяся часть текстового файла игнорируется.
Если данные загружаются из локального файла с использованием ключевого
слова LOCAL
, то сервер не сможет прервать передачу данных посреди этой
операции, поэтому по умолчанию выполнение команды происходит так же, как и
в случае, когда указывается IGNORE
.
При использовании LOAD DATA INFILE
на пустых таблицах MyISAM
все
неуникальные индексы создаются в отдельном пакете (как в REPAIR
). Обычно
это значительно ускоряет работу LOAD DATA INFILE
в случае большого
количества индексов.
Команда LOAD DATA INFILE
является дополнительной к SELECT ... INTO OUTFILE
. See Раздел 6.4.1, «Синтаксис оператора SELECT
». Чтобы записать данные из базы данных в
файл, используется SELECT ... INTO OUTFILE
. Чтобы прочитать данные
обратно в базу данных, используется LOAD DATA INFILE
. Синтаксис FIELDS
и
LINES
одинаков в обеих командах. Обе части являются необязательными, но
если указаны оба, то FIELDS
должно предшествовать LINES
.
Если указывается FIELDS
, то каждое из его подвыражений (TERMINATED BY
,
[OPTIONALLY] ENCLOSED BY
, и ESCAPED BY
) также является
необязательным, однако необходимо указать по меньшей мере одно из них.
Если утверждение FIELDS
не определено, то по умолчанию его параметры будут
принимать следующие значения:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
Если утверждение LINES
не определено, то по умолчанию оно имеет следующую
структуру:
LINES TERMINATED BY '\n'
Иными словами, при установках по умолчанию команда LOAD DATA INFILE
при
чтении входных данных будет работать следующим образом:
Искать концы строк в виде символов '
\n
'Разбивать строки на поля по символам табуляции.
Не ожидать, что поля могут быть заключены в символы цитирования.
Интерпретировать встречающиеся символы табуляции, новой строки или '
\
', предваренные '\
', как литералы, являющиеся частью значения поля.
И, наоборот, если действуют установки по умолчанию при записи выходных
данных, команда SELECT ... INTO OUTFILE
будет работать следующим образом:
Вставлять символы табуляции между полями.
-
Не заключать поля в символы цитирования.
Использовать символы '
\
' для экранирования экземпляров символов табуляции, новой строки или '\
', которые появляются среди величин поля. Вставлять символы новой строки в конце каждой записи.
Следует учитывать, что в записи FIELDS ESCAPED BY '\'
необходимо
указывать два обратных слеша для величины, которая должна читаться как
один обратный слеш.
Опцию IGNORE number LINES
можно применять для игнорирования заголовка
имен столбцов в начале файла:
mysql> LOAD DATA INFILE "/tmp/file_name" 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 ...;
Чтобы прочитать этот разделенный запятыми файл обратно в базу данных, корректная команда должна иметь вид:
mysql> LOAD DATA INFILE 'data.txt' 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
можно также использовать для чтения файлов,
полученных из внешних источников. Например, поля в файле формата базе
данных dBASE будут разделены запятыми и заключены в двойные кавычки. Если
строки в данном файле заканчиваются символами новой строки, то для записи
файла можно использовать приведенную ниже команду, в которой
проиллюстрировано задание опций, обрабатывающих поля и строки:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Любая из опций, обрабатывающих поля и строки, может задавать пустую строку
(''). Если строка не пустая, то величины опций FIELDS [OPTIONALLY] ENCLOSED BY
и FIELDS ESCAPED BY
должны содержать один символ. Величины
опций FIELDS TERMINATED BY
и LINES TERMINATED BY
могут содержать более
чем один символ. Например, чтобы записать строки, заканчивающиеся парами
``возврат каретки - перевод строки'' (как в текстовых файлах MS DOS или
Windows), необходимо задать следующее выражение:
LINES TERMINATED BY '\r\n'
.
Например, чтобы прочитать файл jokes
, в котором строки разделены
символами %%
, в таблицу SQL, необходимо сделать следующее:
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
служит для управления полями,
заключенными в заданные символы. Если параметр OPTIONALLY
опущен, то в
выводе (SELECT ... INTO OUTFILE)
все поля будут заключены в символы,
заданные в 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
и VARCHAR
:
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 INFILE
не сможет правильно прочитать. Например, если символ экранирования
является пустой строкой, то вывод, представленный выше, окажется таким,
как показано ниже. Обратите внимание: второе поле в четвертой строке
содержит запятую, следующую за кавычкой, которая (ошибочно) появляется,
чтобы ограничить данное поле:
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
, встречающиеся внутри поля,
интерпретируются как одиночные символы 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
не пустой, он
используется в качестве префикса для следующих символов в выводе:
Символ
FIELDS ESCAPED BY
Символ
FIELDS [OPTIONALLY] ENCLOSED BY
Первый символ величин
FIELDS TERMINATED BY и LINES TERMINATED BY
Символ ASCII
0
(в действительности после экранирующего символа пишется ASCII '0
', а не байт с нулевой величиной)
Если символ FIELDS ESCAPED BY
пустой, то никакие символы не
экранируются. На самом деле указывать пустой экранирующий символ нет
смысла, особенно если величины полей в обрабатываемых данных содержат
какие-либо из символов, указанных в приведенном выше списке.
Если символ FIELDS ESCAPED BY
не пуст, то в случае входных данных
вхождения такого символа удаляются и следующий за таким вхождением символ
принимается буквально как часть величины поля. Исключениями являются
экранированные '0
' или 'N
' (например, \0
или \N
, если экранирующим
символом является '\
'). Эти последовательности интерпретируются как ASCII
0
(байт с нулевой величиной) и NULL
. См. ниже правила обработки величины
NULL
.
Чтобы получить более полную информацию о синтаксисе экранирующего символа
'\
' см. раздел Раздел 6.1.1, «Литералы: представление строк и чисел».
В ряде случаев опции обработки полей и строк взаимодействуют:
Если
LINES TERMINATED BY
является пустой строкой иFIELDS TERMINATED BY
является не пустой строкой, то строки также заканчиваются символамиFIELDS TERMINATED BY
.Если обе величины
FIELDS TERMINATED BY
иFIELDS ENCLOSED BY
являются пустыми (''), то применяется формат с фиксированной строкой (без разделителей). В формате с фиксированной строкой не предусмотрены никакие разделители между полями. Вместо этого при чтении и записи величин столбцов используется ширина ``вывода'' столбцов. Например, если столбец объявлен какINT(7)
, значения для этого столбца записываются с использованием полей шириной7
символов. Входные значения для этого столбца получаются чтением7
символов. Формат с фиксированной строкой влияет также на обработку величин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
и пустая строка в данной таблице будут неразличимы при записи в файл, поскольку они обе записываются как пустые строки. Если необходимо, чтобы эти величины были различными при обратном чтении файла, то не следует использовать формат с фиксированной строкой.
Некоторые случаи, не поддерживаемые оператором LOAD DATA INFILE
:
Строки с фиксированным размером (обе опции
FIELDS TERMINATED BY
иFIELDS ENCLOSED BY
пустые) и столбцы типаBLOB
илиTEXT
.-
Если указывается разделитель, совпадающий с другим или являющийся префиксом другого, то
LOAD DATA INFILE
не сможет интерпретировать ввод правильно. Например, следующее утверждениеFIELDS
вызовет проблемы:FIELDS TERMINATED BY '"' ENCLOSED BY '"'
Если опция
FIELDS ESCAPED BY
пустая, то содержащееся в значении поля вхождение символаFIELDS ENCLOSED BY
илиLINES TERMINATED BY
, за которым следует символFIELDS TERMINATED BY
, приведет к преждевременному завершению чтения поля или строки командойLOAD DATA INFILE
. Это происходит вследствие того, чтоLOAD DATA INFILE
не может правильно определить, где заканчивается поле или строка.
Следующий пример загружает все столбцы таблицы persondata
:
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
Список полей не указывается, следовательно, команда LOAD DATA INFILE
ожидает входные строки для заполнения каждого столбца таблицы. При этом
используются значения FIELDS
и LINES
по умолчанию.
Если требуется загрузить только некоторые из столбцов таблицы, необходимо задать список столбцов:
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
Список полей необходимо задавать и в случаях, если порядок следования полей во входном файле отличается от порядка столбцов в данной таблице. В противном случае MySQL не сможет установить соответствие вводимых полей и столбцов таблицы.
Если строка имеет слишком мало полей, то столбцы, для которых отсутствуют
поля во входном файле, устанавливаются в свои значения по умолчанию.
Назначение величин по умолчанию описывается в разделе Раздел 6.5.3, «Синтаксис оператора CREATE TABLE
».
Значение пустого поля интерпретируется иначе, чем отсутствие значения:
Для строковых типов столбец устанавливается в пустую строку.
Для числовых типов столбец устанавливается в
0
.Для типов даты и времени столбец устанавливается в соответствующее этому типу значение ``ноль''. See Раздел 6.2.2, «Типы данных даты и времени».
Отметим, что это те же самые величины, которые окажутся в столбце в
результате явного назначения пустой строки столбцам строкового, числового
типов, либо типов даты или времени в команде INSERT
или UPDATE
.
Столбцы типа TIMESTAMP
устанавливаются только в текущую дату или время в
случаях, если для столбца назначено значение NULL
или (только для первого
столбца TIMESTAMP
) если столбец TIMESTAMP
находится вне списка полей, если
такой список задан.
Если входная строка имеет слишком много полей, то лишние поля игнорируются и количество предупреждений увеличится.
Команда LOAD DATA INFILE
интерпретирует все входные данные как строки,
поэтому нельзя указывать числовые величины для столбцов ENUM
или SET
так
же, как для команд INSERT
. Все величины ENUM
и SET
должны быть заданы как
строки!
При использовании C API можно получить информацию о запросе, вызвав
функцию API mysql_info()
по окончании запроса LOAD DATA INFILE
. Ниже
показан формат строки информации для этого случая:
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
Предостережения выдаются при тех же обстоятельствах, что и при записи
величин командой INSERT
(see Раздел 6.4.3, «Синтаксис оператора INSERT
»), за исключением того,
что команда LOAD DATA INFILE
дополнительно генерирует предупреждения,
когда во входной строке слишком мало или слишком много полей.
Предостережения нигде не хранятся; количество предупреждений может
использоваться только для того, чтобы проверить, нормально ли выполнились
указанные действия. Если необходимо точно знать причины предупреждений, то
следует выполнить команду SELECT ... INTO OUTFILE
в другой файл и
сравнить результат с первоначальным входным файлом - это единственный
способ получить такую информацию.
Если необходимо выполнить LOAD DATA
для чтения из канала, можно применить
следующий трюк:
mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x
При использовании версии MySQL старше, чем 3.23.25, вышеприведенное можно
сделать только с LOAD DATA LOCAL INFILE
.
Чтобы получить более подробную информацию об эффективности INSERT
в
сравнении с LOAD DATA INFILE
и увеличении скорости LOAD DATA INFILE
,
см. раздел Раздел 5.2.9, «Скорость выполнения запросов INSERT
».