Импорт csv в mysql через консоль
I'm trying to import a very large .csv file (~4gb) into mysql. I was considering using phpmyadmin, but then you have a max upload size of 2mb. Someone told me that I have to use the command line.
What would be the command to set the first row in the .csv table as the column names in the mysql table? This option is available through phpmyadmin, so their must be a mysql command line version too, right?. Please help me. Thank you.
Особенности загрузки данных в MySQL базу из дампа
Если хотите, чтобы процесс импорта большой базы MySQL проходил быстрее, то необходимо создавать дамп БД с использованием специальных опций команды mysqldump, о которых я писал в своей предыдущей статье об экспорте БД MySQL, ссылку на которую размещал в тексте ранее.
У самих команд импорта баз данных MySQL таких опций, к сожалению, нет.
Единственное, для увеличения скорости при загрузке большого дампа БД можете использовать следующую фишку.
1. Открываем файл дампа (желательно в файловых менеджерах, т.к. обычные редакторы могут просто загнуться от больших файлов).
2. Прописываем в начале файла следующие строки:
Обратите внимание! Может быть они уже есть или закомментированы (многие программы, с помощью которых делают дампы, могут добавлять их автоматически)
3. В конце файла прописываем обратные действия:
Кстати, данные команды помогут не только ускорить процесс импорта, но и сделать его возможным.
Дело в том, что, если вы хоть раз заглядывали в файл дампа для импорта БД MySQL, то могли заметить, что операция задания структуры загружаемых таблиц выглядит так:
Т.е. выполняется поиск в БД таблицы с таким же именем, как и у импортируемой, и если таковая найдена, то она удаляется и создаётся заново.
И если вдруг существующая таблица будет связана внешними ключами с другими, то вся загрузка провалится.
Поэтому отключение проверки существования внешних ключей и прочих – это ещё и отличная гарантия успешного выполнения процесса импорта базы данных MySQL.
Step 1: Access MySQL Shell
Access your terminal window and log into MySQL using the following command:
Replace username with your actual username. The system prompts you to enter the password for your MySQL user. Entering the correct password gives you access to the MySQL client.
Импорт файла клиента для замещения на сервере базы данных MySQL
Можно импортировать данные от клиента (локального компьютера) на удаленный сервер базы данных MySQL, с помощью оператора LOAD DATA INFILE .
При использовании опции LOCAL в LOAD DATA INFILE клиентская программа считывает файл на стороне клиента и отправляет его на сервер MySQL. Файл будет загружен во временную папку базы данных сервера операционной системы, например, C: Windows Temp для ОС Windows или / TMP для Linux .
Эта папка не настраивается и не задается MySQL.
Давайте рассмотрим следующий пример:
Разница заключается только в опции LOCAL оператора. Если вы загружаете большой CSV файл, вы увидите, что с опцией LOCAL загрузка осуществляется немного медленнее, потому что требуется определенное время, чтобы передать файл серверу базы данных.
Учетная запись пользователя, под которым мы подключается к серверу MySQL для импорта данных, может не иметь привилегию FILE , если используется опция LOCAL .
При импорте файла от клиента на удаленный сервер базы данных с помощью LOAD DATA LOCAL могут возникнуть некоторые проблемы с безопасностью , о которых вы должны знать, чтобы избежать потенциальных рисков.
Создание бэкапов и вывод данных из MySQL базы с помощью запросов
О том, как сделать дамп базы данных MySQL — одной и нескольких, а также отдельных их таблиц, мы поговорили. Но иногда на практике бывают случаи, когда нужно экспортировать набор данных, не ограниченный одной таблицей. Либо нужно выбрать лишь некоторые данные из таблицы.
Особенно часто с таким сталкиваются разработчики корпоративных проектов, когда менеджеры просят их предоставить всякие статистические данные. Либо когда нужно сделать бэкап определённой части таблицы для её быстрого восстановления.
Для бэкапа нам понадобится всё та же утилита mysqldump, которую нужно будет вызвать в таком виде:
В результате мы получим файл с SQL командами для создания таблицы со всей его структурой, которая после создания будет наполняться информацией, выбираемой с помощью уточняющего запроса.
Если нам будет нужно просто получить данные, хранящиеся в одной или нескольких таблицах, то нам потребуется модификация команды, используемой в предыдущем случае при выборке всех данных таблицы, только с некоторыми уточнениями:
Как вы понимаете, помимо различных уточнений, указываемых в запросе с помощью директивы WHERE, можно использовать и прочие SQL конструкции: JOIN, UNION и т.д.
Статистику собрать получится какую угодно 🙂
То же самое действие возможно произвести также при работе в командной строке MySQL с помощью следующей команды:
Данная команда как раз и предназначена для создания файлов с результатами выборки. Причём, результаты можно не только экспортировать в файлы, но и записывать в переменные, а также выводимые данные можно форматировать различными способами.
Далее речь как раз пойдёт о корректном выводе данных MySQL в xls и csv форматы с помощью данной команды. А с musqldump в рамках данной статьи мы прощаемся.
В завершение своего краткого экскурса по mysqldump хочу привести вариант вызова команды со списком параметров для создания оптимизированного дампа базы MySQL и таблиц, восстановление БД и отдельных таблиц из которого будет занимать меньше времени, чем при обычном вызове:
Ради эксперимента я использовал данный вариант для того, чтобы сделать дамп базы MySQL размером в 143 Мб. Последующее восстановление заняло 59 секунд времени против 1 минуты и 3 секунд, когда БД восстанавливалась из дампа, сделанного вызовом mysqldump без специальных параметров.
Согласен, что это мелочь. Но это только в случае данного объёма данных. Если использовать данную методику при создании дампа размером более 1Гб, то разница будет более существенной.
Если вы столкнётесь с такой ситуацией, то не забудьте ещё предварительно запаковать дамп базы MySQL в архив. Лучше всего tar.gz. Тогда восстановление займёт ещё меньше времени.
Import CSV File with phpMyAdmin
If your server or database uses phpMyAdmin, you can use the graphical interface to import a CSV file.
1. Access cPanel and launch phpMyAdmin.
2. Use the left pane to select the database and table you are importing the CSV file into.
3. Use the top menu to select Import.
4. Click Choose File and browse to the CSV file location.
5. Use the Format drop-down menu to select CSV and the Format-specific options to define options for the individual columns.
6. Select Go to initiate the CSV import.
You now know how to import CSV files into MySQL, both from the command line or by using phpMyAdmin. The methods outlined in this tutorial enable you to move data between systems and different database applications.
Особенности импорта csv в MySQL БД и других файлов
При загрузке данных в MySQL БД из текстовых файлов может также потребоваться отключение внешних ключей.
Причём, в отличие от предыдущей ситуации, в данном случае прописать директивы в файл не получится, т.к. SQL команды в нём не будут восприниматься и выполняться.
В предыдущей статье об экспорте базы MySQL я уже упоминал о том, как это сделать с помощью следующей операции в командной строке MySQL:
Однако, там я не упомянул, что системная переменная MySQL FOREIGN_KEY_CHECKS имеет два значение: глобальное и сессионное (для текущей сессии).
Глобальное значение переменных MySQL действует при выполнении любых действий на сервере MySQL вплоть до его перезапуска. Тогда значение переменных будут сброшены и им будет присвоено значения по умолчанию.
Сессионное значение системной переменной MySQL устанавливается только на время сеанса работы пользователя с сервером MySQL. Сеанс или сессия начинается при подключении клиента к серверу, при котором ему присваивается уникальный connection id, и заканчивается при отключении от сервера, которое может произойти в любой момент (например, по таймауту).
Почему я об этом решил вспомнить?
Потому что при выполнении команд загрузки файла в MySQL БД через консоль сервера, без захода в MySQL Shell, я обнаружил, что отключение проверки внешних ключей приведённым ранее способом не работает.
А возникало оно по той причине, что приведённой командой отключалась проверка существования внешних ключей в рамках сессии, а не глобально, которая, помимо указанного способа, может быть выполнена ещё и следующим образом:
В приведённых командах переменная явно помечается как сессионная.
А, поскольку, я выполнял загрузку csv файла в MySQL таблицу через консоль сервера, без прямого подключения к серверу MySQL, то сеанс и не был создан, в рамках которого работало бы моё сессионное значение переменной.
В итоге я установил глобальное значение FOREIGN_KEY_CHECKS, и импорт успешно выполнился.
Сделать это можно одним из перечисленных способов:
После изменения значений для проверки того, что изменения вступили в силу, не лишним будет просмотреть значения переменной. Для вывода сессионного и глобального значений одновременно пользуйтесь следующей командой:
На этом сегодняшняя статья, посвящённая импорту базы данных MySQL, подошла к концу. Делитесь своими впечатлениями и собственными наработками в комментариях. Думаю, что многим будет интересен ваш опыт.
Если публикация вам понравилась — вы всегда можете поделиться ею со своими друзьями в социальных сетях и сказать спасибо автору с помощью формы под статьёй.
До новых встреч! 🙂
Приветствую вас, друзья! 🙂
Сегодня я решил продолжить разговор о работе с MySQL в консоли и уделить внимание процедуре экспорта базы данных MySQL.
В статье я расскажу о том, как сделать дамп базы MySQL, а также производить выгрузку данных из MySQL в Excel файл и csv формат.
Мы рассмотрим различные варианты выборки информации из базы данных сайта: создание дампа одной и нескольких БД, экспорте данных из отдельных таблиц и результатов произвольных SELECT запросов.
А также поговорим о том, как сделать вывод данных из MySQL базы в консоли сервера и командной строке MySQL.
В данной статье я не буду рассказывать о том, как экспортировать данные с помощью phpMyAdmin и других визуальных инструментов.
Во-первых, потому что по данной теме уже достаточно материала в сети. Причём качественного материала, который я не горю желанием копипастить.
А, во-вторых, я уже вкратце сам рассматривал процесс вывода информации из MySQL базы в SQL файл в одной из своих статей, где рассказывал об установке WordPress на хостинг.
Так что, если вы не являетесь профессиональным разработчиком или системным администратором, которым может быть полезна информация о работе с консолью, и вы пришли лишь за инструкцией по экспорту базы в phpMyAdmin, то можете ограничиться чтением информации по ссылке выше.
Хочу, чтобы вы поняли меня правильно: я ни в коем случае не хочу вас обидеть, а просто желаю, чтобы вы потратили своё время с максимальной пользой для дела и получили то, что искали.
На этом вступительная часть окончена и мы переходим к обзору консольных команд для создания дампа базы MySQL, которые я решил рассортировать по объёму сохраняемых данных: начиная от экспорта всей БД, заканчивая отдельными таблицами и результатами произвольных запросов.
Создание дампа базы MySQL через консоль
Хочу в самом начале сделать небольшое уточнение.
Дамп базы — это файл с набором SQL команд, который при запуске позволяет создавать базы данных и таблицы, а также наполнять их информацией. Дамп нужен тем, кто хочет скачать базу данных MySQL с целью её копирования на другой сервер или в рамках существующего.
Также, если кто-то не в курсе, бэкап базы данных MySQL — это, по сути, её дамп, сделанный в определённый период времени, который позволяет восстановить структуру и данные базы при необходимости.
Экспорт данных — это просто извлечение информации из таблиц в текстовом виде для дальнейшей работы с текстовом или графическом редакторах.
Следовательно, команды для данных действий будут немного разные.
Для создания дампа БД у MySQL есть встроенная утилита под названием mysqldump, которую нужно использовать за пределами командной строки MySQL в консоли сервера или другой оболочке.
Итак, для самого простого и распространённого варианта — экспорта данных конкретной БД в консоли MySQL для переноса её на другой сервер или внутреннего копирования нужно выполнить следующую команду:
Данная утилита может создавать дампы базы MySQL только в виде файлов с SQL командами, поэтому какое бы вы расширение для своего файла не выбрали — в любом случае его содержимое будет одинаковым. И не забудьте перед экспортом информации из MySQL проверить права на запись у директории, в которой он будет располагаться, чтобы создание файла было возможно.
Если вдруг вам нужно будет сделать дамп со всеми базами данных на сервере, то используйте следующий вариант команды:
Для создания дампа лишь для нескольких конкретных баз данных вам понадобится вызов mysqldump со следующими параметрами:
В итоге, в каждом случае вы получите дамп базы MySQL, содержащий команды для создания структуры содержащихся таблиц (поля, их типы, индексы, ключи и т.д.), а также операции наполнения их данными.
Данный вариант годится только для восстановления и копирования баз данных целиком.
О том, как делать бэкапы определённых таблиц MySQL и получать их данные в читаемом виде, речь пойдёт дальше.
Как восстановить MySQL базу из дампа через консоль?
Итак, для того, чтобы развернуть дамп MySQL из консоли есть два пути:
- с помощью команды в командной строке MySQL;
- в самой консоли сервера.
Начнём по порядку.
Итак, чтобы импортировать дамп базы данных MySQL в существующее хранилище через командную строку MySQL, нам сначала нужно её запустить и выбрать нужную базу данных, в которую мы будем заливать наш дамп.
Выполнение данных действий подробно описано в статье по ссылке выше, поэтому если вам нужно их описание – возьмите оттуда, т.к. дублировать их по второму кругу я не хочу.
После того, как вы сделаете указанное, вводим в MySQL Shell следующую команду:
Без предварительного переключения на нужную базу данных после соединения с сервером MySQL в консоли дамп можно импортировать следующей командой:
Вот и всё. Главное — дождаться окончания импорта, если файл очень большой. Об окончании заливки дампа можно судить по тому, когда консоль сервера будет снова доступна.
Собственно говоря, в этом и заключается недостаток данного способа по сравнению с предыдущим, т.к. в первом есть возможность наблюдать за операциями, производимыми с БД во время импорта, а во втором — нет.
Если файл дампа будет запакован в архив, то при загрузке нужно будет его попутно распаковывать.
В Linux это можно сделать следующим образом:
В Windows стандартной утилиты для распаковки архива в консоли нет, поэтому её нужно будет установить дополнительно.
Как видите, импорт дампа MySQL через консоль – операция весьма простая, которая выполняется одной командой. Так что для выполнения данной процедуры не обязательно быть разработчиком.
Если вдруг вы не знаете, как запустить консоль сервера, то можете найти эту информацию в статье о командной строке MySQL, ссылку на которую я уже располагал ранее.
Кстати, описанными способами возможен также и импорт таблицы MySQL, а не целой базы. В этом случае в заливаемом вами дампе должны присутствовать операции её создания и наполнения данными.
Экспорт данных из MySQL в Excel и csv файлы
Я не зря объединил информацию о выводе информации из MySQL в эти два формата в одном блоке, т.к. они очень похожи, используются примерно одинаково (для структурирования информации в виде таблиц) и команды для экспорта будут вызываться одни и те же.
Как известно, единственным существенным различием между данными форматами является то, что расширение xls и xlsx имеют файлы, создаваемые в программе Microsoft Office Excel, которая работает только под Windows, а csv файлы являются более универсальными и операции с ними возможны во многих редакторах.
Это не значит, что xls нигде, кроме Microsoft Office Excel, не откроется. Тот же OpenOffice подтверждает обратное.
Но для такой возможности данная поддержка должна присутствовать в программном продукте. csv же файлы доступны для чтения даже в обыкновенном текстовом редакторе типа «Блокнот», только такая форма будет не совсем читабельной.
Начну с того, что экспортировать в xls или csv можно только результаты SQL запросов, работать с которыми мы с вами научились ранее, т.к. всю базу данных в один файл будет невозможно вывести за одну операцию.
Во-первых, это не оптимально, т.к. такой файл вряд ли откроется при больших объёмах хранящейся в БД информации. А, во-вторых, непонятно, как разбивать внутри файла информацию по таблицам и полям.
Нет, сделать это, конечно, возможно, но вряд ли одной командой и вообще вряд ли кто-то будет таким заниматься в консоли. Думаю, что для данных целей понадобится специальное ПО или, как минимум, скрипт.
Если вы вдруг знаете, как можно экспортировать информацию из всей MySQL БД сразу в один или несколько xls файлов в консоли, то напишите об этом в комментариях. Думаю, почитать об этом будет полезно многим.
Итак, если мы говорим о том, как сделать экспорт данных из MySQL в xls и csv, то сделать это можно прямо в консоли сервера через утилиту mysql либо в командной строке MySQL, работой с которой я знакомил вас в предыдущей своей статье.
Начнём по порядку.
Экспортировать данные из MySQL базы данных в csv и xls форматы прямо в консоли сервера можно следующими командами.
На Linux системах:
В принципе, при крайней необходимости можете сделать данной командой и экспорт данных MySQL в Excel файл. Но я, если честно, на практике данным не занимался и что выйдет в итоге — без понятия, т.к. работаю сейчас под Windows. Если будете пользоваться данной командой под Linux — напишите в комментариях, пожалуйста, о результатах вашей работы. Думаю, информация будет интересна всем.
На Windows:
Экспорт данных из MySQL таблиц в csv приведённой выше командой в данном случае, к сожалению, не удастся, т.к. у Windows, в отличие от Linux, нет встроенной консольной команды для работы с потоками, какой является sed в Linux.
Установить её, конечно, можно, но слишком много хлопот. Ещё, как вариант, можете использовать CygWin — эмулятор консоли Linux для Windows систем.
Хорошо, если она у вас уже установлена. В противном случае экспорт данных из БД MySQL выбранным способом принесёт нам слишком много хлопот.
Зато извлечение информации в xls файл прост как 5 копеек 🙂 Запустить его очень просто следующим способом, который я опробовал лично:
Но при подтверждении действия документ открылся без трудностей — вся информация была разбита по ячейкам в том виде, в каком она хранилась в самой таблице.
Не знаю, возможно, при произведении каких-либо специфических действий в Microsoft Office Excel и возникнут проблемы в дальнейшем, я так глубоко не копался. При обычном просмотре данных, по крайней мере, я ничего необычного не встретил.
Если у вас в процессе использования экспортированного из MySQL xls файла возникнут какие-то проблемы либо в данной программе, либо в других, то дайте мне знать, пожалуйста, в комментариях.
Описанным выше путём можно экспортировать содержимое БД MySQL и в csv файл, в принципе. Но тогда данные из разных полей таблицы будут записаны скопом, без разделителей, что может плохо отображаться в различных программах для работы с таблицами, в которых обычно с csv файлами и работают.
OpenOffice, кстати, всё равно 🙂 Он автоматически разграничил информацию, полученную способом, которым мы экспортировали содержимое базы MySQL в xls. Не знаю, как он это делает — но рекомендую пользоваться 🙂
Ну, а тот же самый Microsoft Office Excel отобразил всю информацию, соответствующую одной записи в таблице, записав её в одну ячейку без каких-либо разделителей. Думаю, что подобным образом поступят и другие редакторы таблиц.
Поэтому при экспорте базы данных MySQL в csv файлы нужно делать это, разделяя информацию специальными символами, воспринимаемыми редакторами.
И тут я плавно подошёл ко второму способу экспорта MySQL данных в csv и xls, который заключается в использовании командной строки MySQL.
Итак, для того, чтобы экспортировать данные MySQL в csv файл данным способом, нам нужна следующая команда:
В результате её выполнения вы получите csv файл по пути, прописанному вами при вызове, который будет корректно открываться в большинстве современных табличных редакторов. На всякий случай напоминаю, что запускать данную команду нужно только после соединения с базой данных MySQL.
Данная команда также отлично подходит и для экспорта данных MySQL в xls файл для корректного отображения в Microsoft Office Excel. Только в этом случае разделители нам не нужны, т.к. они будут мешать в разбиении информации по ячейкам:
Однако, на практике не всё так просто, как я описал. Во время выполнения команды вы можете столкнутся со следующей ошибкой в консоли, препятствующей выполнению экспорта:
Она вызвана тем, что ваш MySQL сервер был запущен с опцией —secure-file-priv. Лично я столкнулся с данной проблемой из-за того, что для работы в консоли пользуюсь дистрибутивом MySQL, входящим в комплект WAMP OpenServer, который, в свою очередь запускает MySQL сервер данным образом.
Здесь есть два способа решения проблемы:
- Изменить параметры запуска MySQL сервера
- Изменить путь к конечному файлу экспорта MySQL
Первый способ мне показался слишком сложным, т.к. пришлось бы копаться в конфигурации OpenServer, которая не мною была написана со всеми вытекающими из этого обстоятельствами 🙂 Поэтому я решил пойти вторым путём. Если вы столкнулись с подобной проблемой — повторяйте за мной.
Сперва нужно зайти в командную строку MySQL и выполнить одну из следующих команд:
Результатом выполнения обеих будет значение глобальной переменной MySQL secure_file_priv, которая содержит путь к каталогу, через который могут осуществляться операции экспорта и импорта данных MySQL (в будущем ссылка на статью об импорте данных).
Т.е. при использовании команд LOAD DATA и SELECT … INTO OUTFILE экспортируемые и импортируемые файлы могут располагаться только внутри данного каталога.
В моём случае у данной переменной было вообще установлено значение NULL, т.к. я, как уже говорил, пользуюсь для работы в консоли утилитами MySQL из дистрибутива, входящего в OpenServer. Данное значение указывало на то, что операции экспорта данных MySQL и импорта с помощью указанных команд были вообще закрыты.
Как потом выяснилось, это распространённая ситуация в случае использования коробочных WAMP и MAMP серверов.
К сожалению, привычными методами изменения значений глобальных переменных MySQL в моём случае воспользоваться не удалось:
В результате я увидел в консоли лишь следующую ошибку:
В итоге, чтобы изменить значение переменной secure_file_priv и открыть операции экспорта и импорта, мне потребовалось зайти в файл конфигурации MySQL mysql.ini, который расположен в корневой директории дистрибутива MySQL, или к нему можно получить доступ иным способом, если MySQL входит в комплект вашего WAMP/LAMP/MAMP сборки сервера.
Вам, кстати, если захотите изменить путь к буферному каталогу обмена файлами, нужно будет сделать то же самое.
В моём случае в конфиге данная переменная уже существовала, только в закомментированном виде:
Если у вас её не будет, то пропишите её с нуля в секции [mysqld] (по крайней мере, у меня она располагалась там).
Я её раскомментил и решил использовать в том виде, в каком она была прописана. Т.е. при экспорте данных из MySQL и их импорте обратно файлы у меня теперь будут храниться в каталоге c:\openserver\userdata\temp\.
После изменения конфига (любого, кстати), не забывайте перезапускать ваш сервер либо отдельную службу, настройки которой вы правили, если есть такая возможность, чтобы изменения вступили в силу!
Для уверенности, после перезапуска MySQL сервера ещё раз выводим на экран переменную secure_file_priv и копируем её значение в буфер обмена.
А теперь нам нужно вызвать команду, что и в начале, только перед названием файла, в который будет сохраняться информация из БД MySQL, прописать путь, хранимый в изменяемой нами переменной в следующем виде:
После этого экспорт данных из MySQL в моём случае заработал.
Важный момент! Если вы работаете с MySQL под Windows, то не забывайте при указании пути к файлу поменять «\» на «/», иначе ошибка с —secure-file-priv всё равно продолжит выводиться.
На этом статья о том, как сделать дамп базы MySQL и её таблиц, а также сделать вывод данных из MySQL таблиц в различные форматы, подходит к концу. Пишите в комментариях свои отзывы и делитесь со всеми своими вариантами скриптов, которыми вы пользуетесь на практике чаще всего.
Если статья вам понравилась — вы можете поблагодарить автора репостом статьи в социальные сети или финансово с помощью формы ниже, чтобы была возможность элементарной оплаты хостинга.
Всем удачи и до новых встреч! 🙂
В этой статье мы расскажем вам, как использовать оператор LOAD DATA INFILE для импорта CSV-файла в таблицу MySQL.
Оператор LOAD DATA INFILE позволяет считывать данные из текстового файла и очень быстро импортировать их в таблицу базы данных.
Перед импортом файла, вам необходимо подготовить следующее:
- Таблицу базы данных, в которую будут импортированы данные из файла;
- CSV-файл с соответствующим числом столбцов и соответствующим форматом данных в каждом столбце;
- Учетную запись пользователя, который подключается к серверу базы данных MySQL и имеет привилегии FILE и INSERT .
Предположим, что мы имеем таблицу, которая называется discounts со следующей структурой:
Мы используем оператор CREATE TABLE , чтобы создать таблицу discounts :
Файл discounts.csv в первой строке содержит заголовки столбцов, в трех других строках - данные:
Следующий оператор импортирует данные из файла c:tmpdiscounts.csv в таблицу discounts :
Поля файла завершаются запятой, относящейся к FIELD TERMINATED BY ',' , которая заключена в двойные кавычки, предусмотренные форматом ENCLOSED BY '"‘ .
Каждая строка CSV файла завершается символом новой строки, обозначающим TERMINATED BY 'n' .
Поскольку первая строка файла содержит заголовки столбцов, которые не должны быть импортированы в таблицу, мы игнорируем ее, указав опцию IGNORE 1 ROWS .
Теперь мы можем проверить, импортированы ли данные в таблицу discounts :
Загрузка данных в MySQL базу из файла в консоли
О восстановлении БД MySQL из дампа в консоли мы поговорили. Теперь самое время разобраться с тем, как аналогичным образом можно импортировать данные из файлов, в том числе из xls и csv в MySQL базу.
Для данной задачи у нас снова есть те же два инструмента, что и в предыдущем случае: командная строка MySQL и консоль сервера.
Снова начнём обзор по порядку.
Итак, для импорта файла в MySQL командной строке мы снова запускаем её и переходим на БД, в которую будут загружаться данные.
А далее прописываем в консоли следующую SQL команду:
Не забудьте, что, если сервер MySQL был запущен с опцией —secure-file-priv (что часто бывает при использовании MySQL дистрибутивов, входящих в WAMP/MAMP сборки), то имя файла нужно указывать с учётом системной переменной secure_file_priv.
О том, как узнать её значение и изменить его, подробно написано в статье об экспорте базы данных MySQL.
Для того, чтобы сделать импорт базы данных MySQL в консоли сервера, не заходя в MySQL Shell, нам пригодится утилита mysqlimport, входящая в состав дистрибутива MySQL, и следующий её вызов:
Данная утилита является аналогом SQL команды LOAD DATA INFILE, только для командной строки. Но, спрашивается, почему тогда среди параметров её вызова не указана таблица, в которую будут загружаться данные из файла?
Дело в том, что у mysqlimport просто физически нет данного параметра. Вместо этого имя таблицы, в которую будут загружаться данные, должно присутствовать в имени импортируемого файла.
Т.е. если вы захотите сделать импорт из Excel таблицы в MySQL таблицу users, то ваш файл должен называться users.xls.
Расширение у импортируемого файла, как уже говорилось, может быть любым.
С помощью mysqlimport также можно загружать сразу несколько файлов xls или csv в MySQL. Чтобы данные попали по назначению, названия файлов и таблиц БД, как и в предыдущем примере, также должны совпадать.
Если вдруг в импортируемом файле столбцы идут не в той же последовательности, что и колонки таблицы БД, то для уточнения их порядка нужно использовать опцию —columns в следующем виде:
Естественно, что в своих примерах я не рассмотрел полный список параметров mysqlimport, т.к. некоторые из них весьма специфичны и на практике используются крайне редко.
Импорт CSV файла с помощью MySQL Workbench
MySQL Workbench предоставляет инструмент для импорта данных в таблицу БД. Он позволяет редактировать данные перед внесением изменений.
Ниже приведены этапы импорта данных в таблицу БД:
Откройте таблицу, в которую будут загружаться данные:
Просмотрите данные, нажмите кнопку Apply :
В MySQL Workbench появится диалоговое окно « Apply SQL Script to Database », нажмите кнопку Apply , чтобы добавить данные в таблицу БД.
Мы рассказали вам, как импортировать CSV в таблицу MySQL с помощью LOAD DATA LOCAL , а также с использованием MySQL Workbench .
С помощью этих методов можно загружать данные и из других файлов текстовых форматов, таких, например, как текстовый файл с разделителями табуляции.
Home » SysAdmin » How to Import a CSV file into a MySQL database?
A CSV (Comma Separated Values) file uses commas to separate different values within the file. The CSV file is a standard format when transferring a table to a different system or importing it to another database application.
This tutorial shows you how to import a CSV file into your MySQL database in a few short steps.
Note: If you need to export a table first, see our article on How to Export a Table from MySQL to CSV.
- Access to a command line / terminal window
- A CSV file containing the data you want to import
- A MySQL user account with FILE and INSERT privileges
- Pre-configured phpMyAdmin account (optional)
Step 2: Create MySQL Table for CSV Import
The columns in your MySQL table need to match the data from the CSV file you plan to import. If you already have a table ready for the CSV import, you can skip to Step 3 of the tutorial.
Select a database by entering the following command:
Replace database_name with the name of the database you are importing data into. Use the following command to create a new table:
Replace the table_name value with the name you want to use for your table. The column_n variable represents the names for each column in your CSV file, and you should edit them accordingly.
- In this example, column_1 is formatted for text.
- column_2 is formatted for dates.
- column_3 is formatted for currencies. The numbers in the parentheses indicate the maximum size of the value and the decimal places.
- column_4 is formatted for whole numbers.
You can add, delete, or modify the data types used in this example to fit your needs. Visit the official MySQL documentation on data types to find out more.
Импорт базы данных MySQL: что и зачем?
Импорт базы данных MySQL — это операция, при которой происходит наполнение базы информацией. При этом источником данных является файл дампа — слепок другой базы, автоматически созданный при операции экспорта, или специально подготовленный SQL скрипт.
Импорт, как и экспорт БД MySQL, бывает двух видов информации, хранящейся в базе:
- структуры базы, её таблиц и хранимых в них данных (в простонародье именуемых дампом БД);
- просто данных, хранящихся в таблице либо собранных с помощью SELECT запросов.
В данной статье будут рассмотрены оба варианта.
Для восстановления из дампа MySQL базы данных с её структурой и всей хранимой информацией, как уже было сказано, нужен файл дампа БД, который представляет из себя текстовый файл с любым расширением (предварительно может быть запакован в архив для уменьшения размера), содержащий SQL команды для создания самой базы и таблиц, а также наполнения их информацией.
Следовательно, для того, чтобы восстановить MySQL базу из дампа, требуется выполнение содержащихся в файле команд.
Для обычного восстановления данных такие сложности не обязательны. Достаточно иметь в наличии тестовый файлик, информация в котором будет структурирована тем же образом, что и в таблице БД: количество колонок с информацией соответствует числу атрибутов записи таблицы.
Для этих целей подойдёт и обычный txt файл, данные в котором будут разделены, либо файлы, создаваемые в специальных табличных редакторах (Microsoft Office Excel, OpenOffice и т.д.), имеющих отличное расширение: xls, csv, odt и др.
Данные форматы даже предпочтительнее, т.к. при их создании ограничители данных добавляются редакторами автоматически, и нет нужды вносить их отдельно, как в случае обычного текстового файла.
Import CSV File Using Command Line
Добавление данных в MySQL: инструменты
По поводу инструментов осуществления импорта базы данных MySQL могу сказать, что их на сегодняшний день существует целых три.
Перечислю их, начиная с самых низкоуровневых, заканчивая высокоуровневыми (с точки зрения применения всяческих оболочек и надстроек):
- Консоль сервера и командная строка MySQL;
- Скрипты, написанные на языках программирования, позволяющие делать запись данных в MySQL с помощью языковых средств;
- Готовые программы, предоставляющие визуальный интерфейс для работы с БД (тот же самый phpMyAdmin, MySQL WorkBench, MySQL Manager и др.).
Думаю, что порядок расположения инструментов не вызовет ни у кого вопросов, т.к. средства языков программирования, как правило, работают на базе команд консоли MySQL, а программы базируются на скриптах или же работают с MySQL командной строкой напрямую.
Так или иначе, во главе всего лежит консоль, а остальные инструменты, по сути, являются её эмуляторами.
Поэтому использование консоли при импорте данных в MySQL позволяет обойти различного рода ограничения, устанавливаемые настройками языков программирования на Web сервере и самих программ (которые, кстати, не всегда возможно изменить).
За счёт этого залить базу данных MySQL через консоль можно не только быстрее, но и сделать эту операцию возможной в принципе, т.к. скрипты и программы склонны прерывать импорт при достижении максимального времени выполнения скрипта или вообще не стартовать его из-за размера загружаемого файла.
Думаю, все, кто хоть раз пытался загрузить дамп в MySQL БД большого размера через phpMyAdmin, понимают, о чём я говорю.
Зачастую именно эти лимиты являются причинами ошибок при импорте базы данных MySQL, которые при использовании консоли вы никогда не увидите.
Они, конечно, не константны, и их можно изменить, но это дополнительная головная боль, которая для рядовых пользователей, кстати, может оказаться нерешаемой.
Надеюсь, что я замотивировал вас делать импорт базы данных MySQL через консоль (причём, как её структуры, так и отдельно данных).
И на этой позитивной ноте мы переходим к долгожданной практике и рассмотрим способы и команды для консольного переноса данных в базу.
Делаем дамп таблицы MySQL и экспорт данных
Для создания дампа определённых таблиц MySQL базы данных нам понадобится всё та же утилита mysqldump, вызываемая со следующими параметрами:
Ещё при вызове mysqldump можно указывать требуемые таблицы в качестве значения параметра —tables, при использовании которого параметр —databases будет игнорироваться:
Приведённый пример выведет на экран следующую ошибку:
Как видите, будет использоваться только самая последняя БД из списка указанных. В принципе, такое поведение вполне логично, т.к. во всех БД указанных таблиц может не оказаться.
Хорошо, дамп таблиц MySQL базы данных мы получили. Его можно использовать для их восстановления или копирования вместе со структурой.
Достичь задуманного нам поможет вариант вызова утилиты mysql из консоли с определёнными параметрами:
Данная команда позволит нам выполнить запрос к требуемой БД и вывести результат в консоль, не заходя в командную строку MySQL.
Ну, а для того, чтобы не выводить данные в консоль, а записать их в файл, нужно дополнить команду следующим образом:
Благодаря данным конструкциям мы можем не только получить данные, хранящиеся во всех полях таблицы, но и в конкретных. Для этого достаточно вместо символа wildcards (*) прописать через запятую требуемые.
В результате у нас на выходе получится обычный текстовый файл, который будет содержать названия полей в виде шапки и информацию по ним для всех записей. Его можно открыть в обычном текстовом редакторе, независимо от того, какое разрешение вы ему зададите при создании.
Если же захотите экспортировать данные из MySQL базы в xls или csv формате, чтобы полученный файл корректно отображался в табличных редакторах, то о том, как это сделать будет рассказано немного позже 🙂
Преобразование данных при импорте
Иногда формат данных не соответствует целевым столбцам таблицы. В простых случаях, вы можете преобразовать их с помощью условия SET в операторе LOAD DATA INFILE .
Предположим, что столбец данных срока действия скидок в файле discount_2.csv имеет формат мм / дд / гггг:
При импорте данных в таблицу discounts мы должны преобразовать их в формат даты MySQL с помощью функции str_to_date() :
Step 3: Import CSV into MySQL Table
Import the data from the CSV file into the MySQL database, using the following lines:
Let us, in turn, explore the actions behind each of these lines:
- LOAD DATA INFILE – Defines the location of the CSV file to be imported. Change the path (between the quotes) to match the path and filename of your CSV file. If the CSV file is located on the local machine, you can use the LOAD DATA LOCAL INFILE statement instead.
- INTO TABLE – This indicates the destination table you are importing the CSV file into. Change the table_name to the name of your table.
- FIELDS TERMINATED BY – By default, comma-separated value files use a comma to identify individual data values. If your export file uses a different delimiter, you can modify this value.
- ENCLOSED BY – This specifies that a double-quote mark " surrounds values.
- LINES TERMINATED BY – Use this line to specify the code for a line break.
- IGNORE 1 ROWS; – Many CSV files export with the column labels as the first line. This command tells MySQL to ignore the first row as you have already created your table with the appropriate column headings. The semicolon at the end specifies the end of the command for MySQL to execute.
9 Answers 9
Try this command
The fields here are the actual table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.
For further details check the manual.
For setting the first row as the table column names, just ignore the row from being read and add the values in the command.
Few questions, firstly, do column1, column2 etc, need to have quotes around it? And my issue is that their are about 50+ column names I need to import. The first line contains all that data, so If there was some way to mysql read the first line, and set them as the column names, that would be the best. It would be way too tedious to write each name one-by-one. Thank you.
You dont need to write it, see that the column names are comma separated, so just cut the first line from your csv file and paste it in the command, And as far as I know, in phpMyAdmin, the csv is being read first to generate a query like this and then the import is being done.
Hmm, did not think of that. Let me give that a go. Thank you. Do you, by any chance, know how to copy from a text editor and paste into ubuntu terminal?
That is, if your file is comma separated and is not semi-colon separated. Else you might need to sed through it too.
does the table have to be created already with the headers? And what format is $yourfile , would *.csv work?
Shouldn't this be: `mysqlimport --columns=$(head -n 1 FILE) --ignore-lines=1 dbname FILE where file is the database file? Having --columns='head -n 1 $yourfile' produces a syntax error. Also you may have to add the option --local since many mysql servers are by default configured by the --secure-file-priv option.
You can simply import by
Note: Csv File name and Table name should be same
For importing csv with a header row using mysqlimport, just add
(ignores the first N lines of the data file)
This option is described in the page you've linked.
You can't do this with mysqlimport, but you can add the option --columns=column_list to give the command the order of your csv fields for your table.
You can put it in the following way:
LOAD DATA LOCAL INFILE 'C:/Users/userName/Downloads/tableName.csv' INTO TABLE tableName FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Not really the same, it loads the file from a specific location on a windows machine and it doesn't have the enclosed by '"' .
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement - Not worked
Another option is to use the csvsql command from the csvkit library.
Example usage directly on command line:
This can be executed directly on the command line, or built into a python or shell script for automation if you need to do this for a number of files.
csvsql allows you to create database tables on the fly based on the structure of your csv, so it is a lite-code way of getting the first row of your csv to automagically be cast as the MySQL table header.
Доброго времени суток, коллеги 🙂
Сегодня я продолжу знакомить вас с работой с MySQL в консоли и командной строкой MySQL.
Я уже написал статьи о том, как производить основные действия с данными MySQL через консоль иm делать бэкап базы MySQL, а также экспорт хранимой в ней информации.
Логическим продолжением этого повествования будет восстановление БД и хранящейся в ней информации с помощью операций импорта базы данных MySQL. И, что немаловажно, мы продолжим делать это с помощью инструмента всех трушных разработчиков — через консоль.
Если же вам нужна инструкция по импорту базы через phpMyAdmin, то вы можете найти её в статье о переносе WordPress на хостинг. В текущей статье я не горю желанием описывать её ещё раз, тем более, что сегодняшний материал будет посвящён исключительно импорту базы MySQL через консоль.
Но, перед тем, как мы приступим к обзору способов и инструментов, пару слов о том, что такое импорт базы данных MySQL, каким он бывает и как его лучше всего делать?
Читайте также: