В ходе разработки сервиса по расчете статистики по управлению запасами для интернет-магазинов возникла задача быстро организовать импорт/экспорт таблиц между разными 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
- Для импорта: путем использования пользовательской переменной для пропуска ненужных полей — например,
пропустит второе и четвертое поле в CSV-файле.array("column1", "@dummy", "column2", "@dummy", "column3")
- Для экспорта: путем изменения списка полей в массиве полей
Таким образом, вопрос простоты и быстроты разработки решен. А когда появится вопрос скорости работы и эффективности — можно будет заняться и оптимизацией.
PS. На самом эти команды MySQL имеют более богатый синтаксис с дополнительными настройками, так что поле для улучшения этого кода ограничено только необходимостью и фантазией.