Создание 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);
Не забываем поддерживать проект кликая на рекламу гугля))
Всем удачи!
Статья просмотренна 74425 раз, зашло посетителей 25602
Круто! Вот этого мне не хватало, постоянно через phpMyAdmin делал 🙂
шикарно! спс!
гы, сам себе пишу. На работе как раз надо было эту тему сделать, знаю где искать))
Честно говоря, если бы делал, то просто генерил бы текстовый файлик с помощью PHP и в цикле закидывал бы туда данные. И кучу проверок и обработок делал бы. А оно однако намного проще всё. Огромное спасибо.
Рад, что помог. Я и сам не знал, пока не столкнулся с проблемой, зато сейчас один запрос и все данные которые нужны!
Спасибо за статью. Я тоже как-то все по старинке делал через PHP. О таком способе знал, но почему-то не использовал. Надо будет попробывать.
Спасибо за каммент.
Единственное, что тут не очень мне нравится, но это все безопасность, что нельзя переписывать файл созданный SELECT * INTO OUTFILE '/tmp/test1.csv' и если нет консоли, то не всегда можно сделать требуемое, а так конечно эта фича меня очень часто выручала.