Экспорт и импорт CSV в MySQL

Файл CSV (Comma Separated Values - значения, разделенные запятыми) использует запятые для разделения различных значений в файле. Файл CSV является стандартным форматом при переносе таблицы в другую систему или ее импорте в другое приложение базы данных.

Это подробное руководство покажет вам, как экспортировать базу данных MySQL в файл CSV и импортировать файл CSV обратно в базу данных MySQL.

Экспорт MySQL в CSV

Нам понадобится:

• Доступ к командной строке или окну терминала
• Учетная запись пользователя с привилегиями root или sudo
• Учетная запись пользователя MySQL с правами root
• Предварительно настроенная учетная запись phpMyAdmin (необязательно)

Экспорт MySQL в CSV с phpMyAdmin

Инструмент phpMyAdmin предоставляет бесплатный графический интерфейс для управления базами данных MySQL. Вы можете использовать его для экспорта любой из отслеживаемых баз данных в файл CSV.

• Войдите в phpMyAdmin.

Затем нажмите кнопку Databases (Базы данных) в верхней части баннера.

В списке баз данных щелкните ссылку на базу данных, которую вы хотите экспортировать. В этом примере мы выбрали базу данных user.

• На следующем экране отображается список таблиц в этой базе данных. Установите флажки для таблиц, которые вы хотите экспортировать.
• Нажмите кнопку Export на баннере внизу.

Оставьте метод экспорта установленным как есть. Используйте раскрывающееся меню Format, чтобы выбрать CSV, затем нажмите Go.

Диалоговое окно предлагает указать место, где вы хотите сохранить файл CSV.

Экспорт из MySQL в CSV с помощью командной строки

Вы можете выполнить экспорт без излишеств через CLI, выбрав все данные в таблице и указав место, куда их нужно сохранить.

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

Введите следующую команду:

SELECT * FROM myTable
INTO OUTFILE '\tmp\myExportFile.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Замените myTable реальным именем таблицы из вашей базы данных. Вы можете заменить \tmp\myExportFile.csv любым другим именем файла или местоположением. Не забудьте сохранить имя файла .csv в конце.

Примечание. В этом примере используется местоположение файла Linux. Если вы работаете в Windows, вы можете использовать c:/folder/file.csv для вашего местоположения файла.

Дополнительные параметры для экспорта из MySQL

Чтобы указать отдельные наборы данных для экспорта из таблицы:

SELECT column1, column2, column3, column4
FROM myTable
WHERE column2 = 'value';

Замените column1 (и остальные) фактическими именами столбцов, которые вы хотите экспортировать. Обязательно используйте команду FROM, чтобы указать таблицу, из которой вы экспортируете. Оператор WHERE является необязательным и позволяет экспортировать только те строки, которые содержат определенное значение. Замените значение фактическим значением, которое вы хотите экспортировать. Например:

SELECT order_date, order_number, order_status
FROM current_orders
WHERE order_status='pending';

<Экспорт и временная метка (timestamp) CSV-файла

Используйте следующую команду для экспорта в файл CSV и добавьте метку времени для времени создания файла:

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = '\tmp';
SET @PREFIX = 'myTable';
SET @EXT = '.csv';
SET @CMD = CONCAT("SELECT * FROM myTable INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
"' FIELDS ENCLOSED BY '\"'
TERMINATED BY ';'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;

Как обычно, замените myTable реальным именем экспортируемой таблицы.

Примечание: вы можете заметить команду SELECT * FROM в скобках. Мы обернули команду в функцию, которая добавляет метку времени.

Экспорт с заголовками столбцов

Используйте оператор UNION для добавления заголовков столбцов в экспортируемый файл:

(SELECT 'column1','column2','column3','column4')
UNION
(SELECT column1, column2, column3, column4
FROM myTable
INTO OUTFILE '\tmp\myExportFile.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n')

Работа с пустыми значениями

Если в базе данных есть нулевые (пустые) значения, этот процесс экспортирует их как букву N. Вы можете заменить значения NULL другой строкой текста, которая имеет больше смысла:

SELECT column1, column2, IFNULL(column3, 'N/A')
FROM myTable INTO OUTFILE '\tmp\myExportFile.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"' LINES
TERMINATED BY '\r\n');

В этом случае команда IFNULL ищет пустые значения в column3. Когда он находит их, он заменяет их текстовой строкой N/A.

Экспорт MySQL в CSV с помощью mysqldump

Вы можете использовать приложение mysqldump для экспорта базы данных MySQL в файл CSV. Введите в командной строке следующее:

mysqldump ––tab=/var/lib/mysql–files/ ––fields–enclosed–by='"' ––fields–terminated–by=',' ––lines–terminated–by='\n' myTable

Эта команда создает копию базы данных myTable в /var/lib/mysql-files.

Примечание. Ваша учетная запись должна иметь права доступа к каталогу, в котором сохранен файл CSV. Вы можете сохранить его по другому пути, но это должен быть путь, к которому у MySQL есть полный доступ.

Экспорт MySQL в CSV с использованием CSV Engine

В некоторых случаях вы можете использовать CSV Engine в MySQL для изменения таблицы. Этот метод не будет работать, если ваша таблица имеет индекс или если ваша таблица использует auto_increment. Используйте следующую команду:

ALTER TABLE myTable ENGINE=CSV;

Этот оператор изменяет формат базы данных на CSV. Затем вы можете скопировать файл CSV в другую систему.

В определенных конфигурациях, CSV Engine или метод mysqldump также могут сэкономить много времени.

Импорт CSV в MySQL

Нам понадобится:

• Доступ к командной строке или окну терминала
• CSV-файл, содержащий данные, которые вы хотите импортировать
• Учетная запись пользователя MySQL с привилегиями FILE и INSERT
• Предварительно настроенная учетная запись phpMyAdmin (необязательно)

Импорт файла CSV с помощью командной строки

Шаг 1: Доступ к оболочке MySQL

Откройте окно терминала и войдите в MySQL, используя следующую команду:

mysql –u username –p

Замените username вашим реальным именем пользователя. Система предложит вам ввести пароль для вашего пользователя MySQL. Ввод правильного пароля дает вам доступ к клиенту MySQL.

mysql –u username –p

Шаг 2. Создание таблицы MySQL для импорта в CSV

Столбцы в вашей таблице MySQL должны соответствовать данным из файла CSV, который вы планируете импортировать. Если у вас уже есть таблица, готовая для импорта в CSV, вы можете перейти к шагу 3 руководства.

Выберите базу данных, введя следующую команду:

USE database_name;

Замените database_name именем базы данных, в которую вы импортируете данные. Используйте следующую команду для создания новой таблицы:

CREATE TABLE table_name (
id INT NOT NULL AUTO_INCREMENT,
column_1 VARCHAR(255) NOT NULL,
column_2 DATE NOT NULL,
column_3 DECIMAL(10 , 2 ) NULL,
column_4 INTEGER,
PRIMARY KEY (id)
);

Замените значение table_name на имя, которое вы хотите использовать для своей таблицы. Переменная column_n представляет имена для каждого столбца в вашем CSV-файле, и вы должны отредактировать их соответствующим образом.

• В этом примере column_1 отформатирован для текста.
• column_2 отформатирован для дат.
• column_3 отформатирован для валют. Числа в скобках указывают максимальный размер значения и десятичные разряды.
• column_4 отформатирован для целых чисел.

Вы можете добавлять, удалять или изменять типы данных, используемые в этом примере, в соответствии с вашими потребностями. Посетите официальную документацию MySQL по типам данных, чтобы узнать больше.

Шаг 3: Импорт CSV в MySQL Table

Импортируйте данные из файла CSV в базу данных MySQL, используя следующие строки:

LOAD DATA INFILE '/home/export_file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '/n'
IGNORE 1 ROWS;

Давайте, в свою очередь, рассмотрим действия, стоящие за каждой из этих строк:

• LOAD DATA INFILE - Определяет расположение файла CSV для импорта. Измените путь (между кавычками), чтобы он соответствовал пути и имени файла вашего CSV-файла. Если файл CSV находится на локальном компьютере, вместо него можно использовать оператор LOAD DATA LOCAL INFILE.
• INTO TABLE - указывает таблицу назначения, в которую вы импортируете файл CSV. Измените table_name на имя вашей таблицы.
• FIELDS TERMINATED BY – По умолчанию файлы значений, разделенные запятыми, используют запятую для идентификации отдельных значений данных. Если в вашем файле экспорта используется другой разделитель, вы можете изменить это значение.
• ENCLOSED BY - указывает, что двойная кавычка " окружает значения.
• LINES TERMINATED BY - Используйте эту строку, чтобы указать код для разрыва строки.
• IGNORE 1 ROWS; - Многие CSV-файлы экспортируются с метками столбцов в первой строке. Эта команда говорит MySQL игнорировать первую строку, так как вы уже создали свою таблицу с соответствующими заголовками столбцов. Точка с запятой в конце указывает конец команды для выполнения MySQL.

Импорт CSV с помощью phpMyAdmin

Если ваш сервер или база данных использует phpMyAdmin, вы можете использовать графический интерфейс для импорта файла CSV.

Войдите в cPanel и запустите phpMyAdmin.

• Используйте левую панель, чтобы выбрать базу данных и таблицу, в которую вы импортируете CSV-файл.

Используйте верхнее меню, чтобы выбрать Import.

• Нажмите Choose File (Выбрать файл) и перейдите к местоположению файла CSV.

Используйте раскрывающееся меню Format, чтобы выбрать CSV, и параметры, относящиеся к формату, чтобы определить параметры для отдельных столбцов.

• Нажмите Goчтобы начать импорт CSV.

Заключение

Теперь вы знаете, как экспортировать CSV-файлы из MySQL и импортировать CSV-файлы в MySQL из командной строки и с помощью phpMyAdmin.