Файл 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 ' mpmyExportFile.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY ' ';
Замените myTable реальным именем таблицы из вашей базы данных. Вы можете заменить mpmyExportFile.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 = ' mp';
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 '
';");
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 ' mpmyExportFile.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY ' ')
Работа с пустыми значениями
Если в базе данных есть нулевые (пустые) значения, этот процесс экспортирует их как букву N. Вы можете заменить значения NULL другой строкой текста, которая имеет больше смысла:
SELECT column1, column2, IFNULL(column3, 'N/A') FROM myTable INTO OUTFILE ' mpmyExportFile.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY ' ');
В этом случае команда 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=' ' 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.
Шаг 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.
