В ходе разработки сервиса по расчете статистики по управлению запасами для интернет-магазинов возникла задача быстро организовать импорт/экспорт таблиц между разными MySQL серверами. Поскольку надо было сделать просто и прозрачно — оптимизация будет впереди — решил воспользоваться авторскими рекомендация из документации по MySQL 5.0. В качестве формата обмена данными решил принять CSV именно по причине простоты реализации.
В итоге, получилось две функции
Экспорт таблицы (Описание синтаксиса MySQL)
function export_csv( $table, // Имя таблицы для экспорта $afields, // Массив строк - имен полей таблицы $filename, // Имя CSV файла для сохранения информации // (путь от корня web-сервера) $delim=',', // Разделитель полей в CSV файле $enclosed='"', // Кавычки для содержимого полей $escaped='\\', // Ставится перед специальными символами $lineend='\\r\\n'){ // Чем заканчивать строку в файле CSV $q_export = "SELECT ".implode(',', $afields). " INTO OUTFILE '".$_SERVER['DOCUMENT_ROOT'].$filename."' ". "FIELDS TERMINATED BY '".$delim."' ENCLOSED BY '".$enclosed."' ". " ESCAPED BY '".$escaped."' ". "LINES TERMINATED BY '".$lineend."' ". "FROM ".$table ; // Если файл существует, при экспорте будет выдана ошибка if(file_exists($_SERVER['DOCUMENT_ROOT'].$filename)) unlink($_SERVER['DOCUMENT_ROOT'].$filename); return mysql_query($q_export); }
Комментарии
Файл можно создать на том же хосте, где расположен MySQL. Если ОС настроена с возможностью на сетевой диск с общим доступом, можно писать и на другой сервер.
Если поле в таблице равно NULL, в CSV файле будет выведено \N.
Для записи файла на локальный диск на сервере пользователю требуются права FILE не на уровне БД, а глобально на уровне сервера MySQL. Можно установить через PHPMyAdmin или запросом
GRANT FILE ON * . * TO 'username'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Для записи файла в каталог, на каталог должны быть даны права на запись для пользователя mysql или стоять атрибуты 757 или 777 (разрешена запись для прочих пользователей)
Импорт таблицы (Описание синтаксиса MySQL)
function import_csv( $table, // Имя таблицы для импорта $afields, // Массив строк - имен полей таблицы $filename, // Имя CSV файла, откуда берется информация // (путь от корня web-сервера) $delim=',', // Разделитель полей в CSV файле $enclosed='"', // Кавычки для содержимого полей $escaped='\\', // Ставится перед специальными символами $lineend='\\r\\n', // Чем заканчивается строка в файле CSV $hasheader=FALSE){ // Пропускать ли заголовок CSV if($hasheader) $ignore = "IGNORE 1 LINES "; else $ignore = ""; $q_import = "LOAD DATA INFILE '". $_SERVER['DOCUMENT_ROOT'].$filename."' INTO TABLE ".$table." ". "FIELDS TERMINATED BY '".$delim."' ENCLOSED BY '".$enclosed."' ". " ESCAPED BY '".$escaped."' ". "LINES TERMINATED BY '".$lineend."' ". $ignore. "(".implode(',', $afields).")" ; return mysql_query($q_import); }
Что имеем в итоге?
Короткие и очень быстрые функции, за счет того, что выполняются одним MySQL запросом.
Довольно гибкая реализация — можно легко управлять множеством параметров, в том числе и списком полей
Для экспорта: путем изменения списка полей в массиве полей
$afields
или использования подзапроса вместо имени таблицы (тогда в массиве будут указаны поля этого подзапроса) — например,
$atable
будет выглядеть так
(select field1, field1 from table2) t
Для импорта: путем использования пользовательской переменной для пропуска ненужных полей — например,
array("column1", "@dummy", "column2", "@dummy", "column3")
пропустит второе и четвертое поле в CSV-файле.
Таким образом, вопрос простоты и быстроты разработки решен. А когда появится вопрос скорости работы и эффективности — можно будет заняться и оптимизацией.
PS. На самом эти команды MySQL имеют более богатый синтаксис с дополнительными настройками, так что поле для улучшения этого кода ограничено только необходимостью и фантазией.
+4
19957
65
–3
mikhailian, 15 марта 2011 в 11:24 #
Простовато для хабра.
0
Richard_Ferlow, 15 марта 2011 в 11:26 #
↵
Да, автор, накрутите «фишек» и побольше =)
0
Juggler, 15 марта 2011 в 11:34 #
↵
«Фишек побольше» — по сути, это написать полную PHP-обертку над MySQL командой. У меня пока не было такой задачи.
0
Juggler, 15 марта 2011 в 11:32 #
↵
Можно считать это моим девизом — «просто о сложном». Если хочется более сложного — в топике есть ссылки на документацию по MySQL.
0
nomeNNescio, 19 октября 2011 в 16:45 #
Коротко и ясно. Спасибо!
Может кому пригодится такая мелкая заметка.
Если попытаться использовать данный метод при разработке на zend framework, можно нарваться на подобное сообщение:
«Mysqli prepare error: This command is not supported in the prepared statement protocol yet»
Обходится данное неудобство достаточно просто:
$this->getConnection()->query($q)
0
fc_arny, 3 ноября 2011 в 16:33 #
Насколько мне известно, данная реализация импорта будет работать, только если web-сервер и сервер БД будут находиться на одной машине, а точнее
SELECT .. INTO OUTFILE
пишет на машину, на которой стоит мускуль, а не web-сервер.