В ходе разработки сервиса по расчете статистики по управлению запасами для интернет-магазинов возникла задача быстро организовать импорт/экспорт таблиц между разными 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);
	}

Что имеем в итоге?
  1. Короткие и очень быстрые функции, за счет того, что выполняются одним MySQL запросом.
  2. Довольно гибкая реализация — можно легко управлять множеством параметров, в том числе и списком полей
    • Для экспорта: путем изменения списка полей в массиве полей 
      $afields
      или использования подзапроса вместо имени таблицы (тогда в массиве будут указаны поля этого подзапроса) — например, 
      $atable
      будет выглядеть так
      (select field1, field1 from table2) t
    • Для импорта: путем использования пользовательской переменной для пропуска ненужных полей — например, 
      array("column1", "@dummy", "column2", "@dummy", "column3")
      пропустит второе и четвертое поле в CSV-файле.

Таким образом, вопрос простоты и быстроты разработки решен. А когда появится вопрос скорости работы и эффективности — можно будет заняться и оптимизацией.

PS. На самом эти команды MySQL имеют более богатый синтаксис с дополнительными настройками, так что поле для улучшения этого кода ограничено только необходимостью и фантазией.