Простой импорт/экспорт в CSV для PHP & MySQL


    В ходе разработки сервиса по расчете статистики по управлению запасами для интернет-магазинов возникла задача быстро организовать импорт/экспорт таблиц между разными 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 имеют более богатый синтаксис с дополнительными настройками, так что поле для улучшения этого кода ограничено только необходимостью и фантазией.
    • +4
    • 85,3k
    • 6
    Поделиться публикацией

    Комментарии 6

      –3
      Простовато для хабра.
        0
        Да, автор, накрутите «фишек» и побольше =)
          0
          «Фишек побольше» — по сути, это написать полную PHP-обертку над MySQL командой. У меня пока не было такой задачи.
          0
          Можно считать это моим девизом — «просто о сложном». Если хочется более сложного — в топике есть ссылки на документацию по MySQL.
          0
          Коротко и ясно. Спасибо!

          Может кому пригодится такая мелкая заметка.
          Если попытаться использовать данный метод при разработке на zend framework, можно нарваться на подобное сообщение:
          «Mysqli prepare error: This command is not supported in the prepared statement protocol yet»

          Обходится данное неудобство достаточно просто:
          $this->getConnection()->query($q)
            0
            Насколько мне известно, данная реализация импорта будет работать, только если web-сервер и сервер БД будут находиться на одной машине, а точнее

            SELECT ..  INTO OUTFILE
            


            пишет на машину, на которой стоит мускуль, а не web-сервер.

            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

            Самое читаемое