img

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

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

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

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


Экспорт MySQL в CSV

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

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

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

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

  1. Войдите в phpMyAdmin.
  2. Затем нажмите кнопку Databases (Базы данных) в верхней части баннера. Databases
  3. В списке баз данных щелкните ссылку на базу данных, которую вы хотите экспортировать. В этом примере мы выбрали базу данных user. user
  4. На следующем экране отображается список таблиц в этой базе данных. Установите флажки для таблиц, которые вы хотите экспортировать.
  5. Нажмите кнопку Export на баннере внизу.
  6. Оставьте метод экспорта установленным как есть. Используйте раскрывающееся меню Format, чтобы выбрать CSV, затем нажмите Go. Export
  7. Диалоговое окно предлагает указать место, где вы хотите сохранить файл CSV. Сохранение 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

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

  1. Доступ к командной строке или окну терминала
  2. CSV-файл, содержащий данные, которые вы хотите импортировать
  3. Учетная запись пользователя MySQL с привилегиями FILE и INSERT
  4. Предварительно настроенная учетная запись 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.

  1. Войдите в cPanel и запустите phpMyAdmin. phpMyAdmin
  2. Используйте левую панель, чтобы выбрать базу данных и таблицу, в которую вы импортируете CSV-файл.
  3. Используйте верхнее меню, чтобы выбрать Import. Import
  4. Нажмите Choose File (Выбрать файл) и перейдите к местоположению файла CSV.
  5. Используйте раскрывающееся меню Format, чтобы выбрать CSV, и параметры, относящиеся к формату, чтобы определить параметры для отдельных столбцов. Go
  6. Нажмите Goчтобы начать импорт CSV.

Заключение

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

Ссылка
скопирована
Системное администрирование
Скидка 10%
Администратор Linux
Стань Linux администратором и сделай весомый шаг в сторону карьеры в DevOps. Самые важные знания от сертифицированного и практикующего тренера с 20 летним стажем
Получи бесплатный
вводный урок!
Пожалуйста, укажите корректный e-mail
отправили вводный урок на твой e-mail!
Получи все материалы в telegram и ускорь обучение!
img
Еще по теме:
img
  Всплески трафика могут стать как проблемой для вашей инфраструктуры хостинга, так и потрясающей возможностью для развития.  На
img
Подробное руководство, предназначенное для понимания и оптимизации различных типов соединений в SQL, которое повысит вашу произв
img
  Введение Цель резервного копирования и репликации – предотвратить критическую потерю данных в результате непредвиденных событи
img
  Вы можете более эффективно запускать и компилировать свои программы с помощью этого удобного средства автоматизации. Есл
img
Дотфайлы (dotfiles) – это немаловажные файлы, которые могут сыграть ключевую роль в вашей карьере разработчика программного обес
img
Linux Jargon Buster: что такое менеджер пакетов в Linux? Как это работает? То, что позволяет отличать дистрибутивы Linux друг от
Комментарии
ЛЕТНИЕ СКИДКИ
40%
50%
60%
До конца акции: 30 дней 24 : 59 : 59