Импорт данных в&nb p;базы MySQL

В предыдущей статье — «Резервирование баз MySQL» — рассказывалось о методах и инструментах создания резервных копий информации из таблиц MySQL. Но не менее часто возникает и другая, обратная задача — загрузить существующую информацию в базу данных или отдельную таблицу. И хотя методы загрузки данных такие же, как и при резервировании, сами данные немного отличаются. Описывая методы загрузки данных, надо учитывать и форматы представления информации. Если при операции резервирования формат данных и их структура известны (они задаются структурой самой таблицы), то загружаемые данные могут быть разными — как содержащими данные о своей структуре, так и нет ("сырые данные").

 

 

Поэтому при выборе методики и инструментов надо учитывать форматы данных, а не только удобство или функциональность.

Данные для импорта не всегда имеют четкую структуруСамый простой и эффективный способ — прямая загрузка файлов базы данных и таблиц — в независимом от платформы формате MyISAM. В таком случае загрузка данных тривиальна — следует скопировать файлы любым способом в каталог данных MySQL и, возможно, заново создать индексы. Недостаток такого подхода — необходимо сразу загружать всю таблицу; а если у вас в базе уже есть точно такая же таблица, и в ней есть данные? Конечно, выход есть — одну таблицу переименовываем, а потом через менеджер БД (например, phpMyAdmin) или самостоятельно, посредством простого php-скрипта, переносим данные в одну таблицу. Метод загрузки файлов таблиц самый быстрый, но возникающие проблемы дублирования данных придется решать вручную.

Данные для загрузки также могут быть представлены в формате SQL-команд INSERT INTO. Например, именно в таком формате получаются данные, если копировать таблицу средствами phpMyAdmin, как это описано в статье «Резервирование баз MySQL». В зависимости от опций копирования, в файле данных может присутствовать код для создания таблицы в случае отсутствия ее на сервере. Таким образом, операции копирования и загрузки данных с копии при использовании phpMyAdmin совершенно тривиальны — можно оставить все настройки по умолчанию, как при копировании данных, и сразу загружать эти данные для восстановления — скрипт сам разберется, что и куда загружать, и даже тип файла (архив или простой текст) сам подберет и обработает.

LOAD DATA эффективнее, чем INSERTВ самом языке SQL также есть команды загрузки данных. Традиционные конструкции INSERT INTO предназначены для обработки небольших порций данных, то есть «в обычной жизни» сайта команды вставки данных перемежевываются с командами выборки и другими SQL-запросами, что позволяет серверу эффективно оптимизировать их обработку. Для загрузки сразу больших объемов информации существует другая конструкция — LOAD DATA.

LOAD DATA LOCAL INFILE "insert_data.sql"

Ключевое слово LOCAL уведомляет сервер, что нужный файл данных находится на машине клиента, и его сначала нужно передать по сети на сервер. Этот режим надо использовать осторожно, так как файлы могут иметь большие размеры, а при обрыве связи или ошибке сервера все придется начинать сначала. Рекомендуется сначала загрузить файл на сервер (например, через FTP, в директорию /home/data.sql) и потом вызывать команду загрузки без опции LOCAL, только с указанием пути к файлу — LOAD DATA INFILE "/home/data.sql". Путь можно задавать как абсолютный, так и относительный. Тут сервер сам решает, где искать файл — если задано только имя файла, он должен находиться в директории, где находится БД, если же путь содержит несколько частей, он воспринимается как путь относительно каталога БД. Ключевые слова IGNORE и REPLACE указывают, как поступать при обнаружении дублирующих значений в полях с уникальными значениями. Эта опция важна при загрузке данных в таблицу, где уже есть информация. Должно быть определено либо одно, либо другое слово, иначе при встрече такой записи процесс загрузки остановится, и все дальнейшие данные не будут помещены в таблицу. А потом вручную найти место, где оборвалась загрузка данных, и продолжить с него будет очень трудно.

Отдельно надо остановиться на определении полей данных, вернее, их разделителей. Цифровые поля особой сложности не представляют, проблемы возникают при различных кодировках в текстовых полях — типы VARCHAR, CHAR и TEXT (и все производные типы — TINYTEXT, MEDIUMTEXT, LONGTEXT). Поэтому введена опция FIELDS, в которой можно задать следующие символы:

TERMINATED BY «xx» — определяет символ, разграничивающий значения в строке;
[OPTIONALLY] ENCLOSED BY «xx» — определяет символ кавычек для ограничения значения поля. Опция OPTIONALLY применяется для указания, что в кавычках заключены только поля CHAR и VARCHAR (полностью аналогично синтаксису в команде SELECT * INTO OUTFILE, описанной в статье о резервировании данных);
ESCAPED BY «xx» — определяет символ, который указывает на то, что следующий символ в строке является специальным.

Еще одна опция — LINES TERMINATED BY «xx» определяет символ окончания строки (это необходимо при переносе данных БД с сервера UNIX на Windows или наоборот). Например, при переносе файла, созданного в Windows, команда будет иметь следующий вид:

LOAD DATA INFILE "insert_data.sql" INTO TABLE datatbl LINES TERMINATED BY "\r\n"

Если значения разделены запятыми, а поля заключены в двойные кавычки, то оператор загрузки будет иметь такой вид (классический вариант при переносе с домашней Windows-машины на рабочий Unix-сервер):

LOAD DATA INFILE "insert_data.sql" INTO TABLE datatbl FIELD TERMINATED BY «,» ENCLOSED BY "\"" LINES TERMINATED BY "\r\n"

В дистрибутиве MySQL присутствует также утилита командной строки mysqlimport. Она является интерфейсом командной строки к оператору LOAD DATA, поэтому можно применять все описанные выше опции. Следует отметить, что файлы, содержащие данные таблиц, должны иметь имена, идентичные именам таблиц (расширение может быть любым, оно игнорируется), так как утилита воспринимает заданные названия таблиц как название файлов. Это значит, что для таблицы basetbl файл для импорта должен называться basetbl.xxx (с любым расширением).

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

--delete. Очищает таблицы перед вставкой данных.
--force. Заставляет программу продолжать работу, несмотря на ошибки.
--local. По умолчанию утилита считает, что файлы для импорта находятся на сервере, а эта опция позволяет работать с локальными файлами (аналогично ключевому слову LOCAL).
--lock-tables. Блокирует таблицы на период загрузки данных. Рекомендуется всегда использовать эту опцию во избежание повреждения данных.
--replace. Заменяет дубликаты строк.

Такой подход для загрузки данных очень мощный. В отличие от первых двух вариантов, он позволяет загружать «сырые» данные, полученные из любого приложения, поддерживающего формат данных CSV (поля разделяются комой или другим выбранным символом, в одной строке файла одна строка таблицы). Это позволяет легко переносить данные из других баз данных (например, MS Access) или программ электронных таблиц (MS Excel; практически все программы поддерживают этот формат). Единственной сложностью является предварительное уточнение формата и создание таблицы с необходимыми полями.

MySQL Import — лучшая программ для импорта из MS Excel и AccessИмпорт данных с использованием специализированных программ является самым мощным средством. Такие программы могут применяться для конвертации таблиц из разных баз данных, восстановления информации из резервных копий или просто помогать переносить накопленные на одном сервере данные на другой. В этой области традиционно сильным игроком является компания EMS с ее утилитой MySQL Import. Программа предназначена для загрузки информации, представленной в формате MS Excel, MS Access, CSV, TXT, а также DBF в таблицы MySQL, причем одновременно можно добавлять информацию из разных файлов. Сразу после выбора файла данных выбирается таблица, и можно просмотреть сами данные (верно ли определены строки и столбцы), настроить опции импорта (с какой строки начинать, сколько всего строк обработать). Также можно гибко настроить представление данных — формат дат, символы-разделители полей, поля BOOLEAN и обработку текстовых полей.

Если вам надо загрузить информацию из ранее сделанной резервной копии, то лучше всего воспользоваться менеджером phpMyAdmin (скорее всего, именно его вы использовали для создания резервной копии). Если данные имеют простую структуру и небольшой объем, то можно воспользоваться или специальными командами SQL, или утилитой командной строки mysqlimport. Ну, а если данные «сырые», и вы не уверены в том, что программа сама сможет корректно определить формат дынных, или же вам надо импортировать данные из других программ, используйте специализированный инструмент — EMS MySQL Import. Хотя утилита имеет некоторые ограничения — в частности, небольшой список поддерживаемых форматов (это странно, так как ее «партнер» EMS MySQL Export поддерживает гораздо больше форматов, и использовать эти инструменты в паре друг с другом очень выгодно), однако со своей основной задачей — импортом данных из Excel/Access/DBF — она справляется на отлично!

Ссылки по теме

• Резервирование баз MySQL
• EMS MySQL Import
• phpMyAdmin

Статья получена: hostinfo.ru

загрузка...