За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое

Создание csv файла средствами mysql

Февраль13

csv mysql средствами

Есть задача — получить данные из таблицы или результат запроса и сохранить в виде CSV файла. Т.е. получить Файл CSV MySQL средствами.

Содержимое запроса:

<pre>mysql> SELECT* FROM user;
+----+-------+----------------------------------+------------+
| id | login | pass                             | date       |
+----+-------+----------------------------------+------------+
|  1 | maxx  | 8ffc4674e44d1dffafe02b58603a9621 | 2009-01-09 |
|  5 | admin | 651e6a3959b7c09ed603071db34b238a | 2009-01-11 |
|  6 | tzmk  | 651e6a3959b7c09ed603071db34b238a | 2009-01-19 |
+----+-------+----------------------------------+------------+
3 rows in set (0.00 sec)

Оператор SELECT позволяет сохранить результат в файл. Скажу сразу, файл не должен существовать, БД его не переписывает, дабы случайно не переписать нужные системные файлы.

Вот полный запрос для данных действий:

SELECT * INTO OUTFILE 'd:\\test.csv'
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '' TERMINATED BY '\r\n'
FROM user

Конструкция INTO OUTFILE ‘d:\\test.csv’ говорит о том, результат запроса будет помещен в файл по указанному пути, путь абсолютный.

Конструкция FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘»‘ ESCAPED BY ‘\\’ говорит о том, что каждое поля в файле будут разделены знаком ;, также что каждое поле будет обернуто в знак «, если внутри поле будет соот знак, то БД позаботится об экранирования (ESCAPED BY) таких знаков (правила построения CSV файлов).

Конструкция LINES STARTING BY » TERMINATED BY ‘\r\n’ говорит о том, что каждая строка начинается с указаного занчения в LINES STARTING BY, а заканчивается строка значением в TERMINATED BY, в данном примере, я ничего не присваиваю началу строки, просто написал чтобы был виден общий синтаксис. Т.к. я юзаю Windows, которому в качестве переноса строки необходимо указать \r\n, поэтому я это значение и поставил в TERMINATED BY, если Вы юзаете *nix подобные системы, то там значение конца строки \n

В итоге получится файл с таким содержимым:

"1";"maxx";"8ffc4674e44d1dffafe02b58603a9621";"2009-01-09"
"5";"admin";"651e6a3959b7c09ed603071db34b238a";"2009-01-11"
"6";"tzmk";"651e6a3959b7c09ed603071db34b238a";"2009-01-19"

Но этого для полноценного CSV файла мало. Необходимо указать название столбцов, это можно сделать так:

SELECT 'id', 'login', 'pass','date'
UNION
(
SELECT * INTO OUTFILE 'd:\\test1.csv'
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '' TERMINATED BY '\r\n'
FROM user
ORDER BY id ASC
)

тогда результирующий файл будет такого содержания:

"id";"login";"pass";"date"
"1";"maxx";"8ffc4674e44d1dffafe02b58603a9621";"2009-01-09"
"5";"admin";"651e6a3959b7c09ed603071db34b238a";"2009-01-11"
"6";"tzmk";"651e6a3959b7c09ed603071db34b238a";"2009-01-19"

Обратите внимание!!! Если Вам необходимо результирующие данные как-то отсортировать, то сортируемый запрос оберните в скобки, иначе MySQL сначала совместит 2 запроса, а потом сделает общую сортировку, вот цитата из документации:

To apply ORDER BY or LIMIT  to an individual SELECT,
place the clause inside the parentheses that enclose the:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Не забываем поддерживать проект кликая на рекламу гугля))

Всем удачи!

Статья просмотренна 46302 раз, зашло посетителей 16375

рубрика: MySQL

7 комментариев в “Создание csv файла средствами mysql”

  1. Avatar
    Snowcore пишет:

    Круто! Вот этого мне не хватало, постоянно через phpMyAdmin делал 🙂


  2. Avatar
    Евгений пишет:

    шикарно! спс!


  3. Avatar
    maxnag пишет:

    гы, сам себе пишу. На работе как раз надо было эту тему сделать, знаю где искать))


  4. Avatar
    Never Lex пишет:

    Честно говоря, если бы делал, то просто генерил бы текстовый файлик с помощью PHP и в цикле закидывал бы туда данные. И кучу проверок и обработок делал бы. А оно однако намного проще всё. Огромное спасибо.


  5. Avatar
    maxnag пишет:

    Рад, что помог. Я и сам не знал, пока не столкнулся с проблемой, зато сейчас один запрос и все данные которые нужны!


  6. Avatar
    Иван пишет:

    Спасибо за статью. Я тоже как-то все по старинке делал через PHP. О таком способе знал, но почему-то не использовал. Надо будет попробывать.


  7. Avatar
    maxnag пишет:

    Спасибо за каммент.
    Единственное, что тут не очень мне нравится, но это все безопасность, что нельзя переписывать файл созданный SELECT * INTO OUTFILE '/tmp/test1.csv' и если нет консоли, то не всегда можно сделать требуемое, а так конечно эта фича меня очень часто выручала.


не публикуется

пример

Оставить комментарий или два:

  

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Февраль 2010
Пн Вт Ср Чт Пт Сб Вс
« Янв   Мар »
1234567
891011121314
15161718192021
22232425262728

Сейчас на сайте