Экспорт данных MySQL

В MySQL можно использовать операторSELECT...INTO OUTFILE, чтобы легко экспортировать данные в текстовый файл.


Использование оператора SELECT ... INTO OUTFILE для экспорта данных

SELECT...INTO OUTFILE— это синтаксис в MySQL, используемый для экспорта результатов запроса в файл.

SELECT...INTO OUTFILEпозволяет записать результаты запроса в текстовый файл. Базовое использование:

SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;

Описание параметра:

  • column1, column2, ...: Столбцы для выбора.
  • 'file_path': Укажите путь и имя выходного файла.
  • your_table: Таблица, к которой выполняется запрос.
  • your_conditions: Условие запроса.

Ниже приведён простой пример:

Пример

SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;

В приведённой выше инструкции SQL мы выбрали столбцы id, name и email из таблицы users и записали результаты в файл /tmp/user_data.csv. FIELDS TERMINATED BY ',' задаёт разделитель между столбцами (запятая), а LINES TERMINATED BY '\n' задаёт разделитель между строками (символ новой строки).

Обратите внимание, что для выполненияSELECT...INTO OUTFILEтребуется соответствующее разрешение, а каталог выходного файла должен быть местом, в которое сервер MySQL может выполнять запись.

В следующем примере мы экспортируем данные из таблицы example_tbl в файл /tmp/example.txt:

mysql> SELECT * FROM example_tbl 
    -> INTO OUTFILE '/tmp/example.txt';

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

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/example.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

В следующем примере создаётся файл со значениями, разделёнными запятыми. Этот формат может использоваться многими программами.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

Инструкция SELECT ... INTO OUTFILE имеет следующие свойства:

  • LOAD DATA INFILE — это обратная операция по отношению к SELECT ... INTO OUTFILE, использующая синтаксис SELECT. Чтобы записать данные из базы данных в файл, используйте SELECT ... INTO OUTFILE; чтобы прочитать файл обратно в базу данных, используйте LOAD DATA INFILE.
  • Инструкция SELECT в форме SELECT ... INTO OUTFILE 'file_name' может записать выбранные строки в файл. Файл создаётся на хосте сервера, поэтому для использования этого синтаксиса необходимо иметь привилегию FILE.
  • Выходной файл не может быть существующим файлом. Это предотвращает несанкционированное изменение данных файла.
  • Вам нужна учётная запись, вошедшая на сервер, чтобы получить файл. В противном случае SELECT ... INTO OUTFILE не будет иметь эффекта.
  • В UNIX файл доступен для чтения после создания, с правами, принадлежащими серверу MySQL. Это означает, что даже если вы можете прочитать файл, возможно, вы не сможете его удалить.

mysqldump Экспорт таблиц в виде необработанных данных

mysqldump — это инструмент командной строки, предоставляемый MySQL для резервного копирования и экспорта баз данных.

mysqldumpЭто утилита, используемая mysql для выгрузки баз данных. В основном она создаёт SQL-скрипт, содержащий команды, необходимые для воссоздания базы данных с нуля, такие как CREATE TABLE, INSERT и т. д.

Используяmysqldumpдля экспорта данных, необходимо использовать опцию--tab, чтобы указать каталог, в котором будут сохранены экспортированные файлы. Этот целевой каталог должен быть доступен для записи.

Основное использование mysqldump:

mysqldump -u username -p password -h hostname database_name > output_file.sql

Описание параметра:

  • -u: Указать имя пользователя MySQL.
  • -p: Запросить пароль.
  • -h: Указать имя хоста MySQL.
  • database_name: Имя экспортируемой базы данных.
  • output_file.sql: Файл, в который сохраняются экспортированные данные.

В следующем примере таблица example_tbl экспортируется в каталог /tmp:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp EXAMPLE example_tbl
password ******

Пример mysqldump

1. Экспорт всей базы данных

Экспорт базы данных mydatabase в файл mydatabase_backup.sql:

mysqldump -u root -p mydatabase > mydatabase_backup.sql

2. Экспорт конкретной таблицы

Если вы хотите экспортировать только определенную таблицу из базы данных, можно использовать следующую команду:

mysqldump -u username -p password -h hostname database_name table_name > output_file.sql

Или:

mysqldump -u root -p mydatabase mytable > mytable_backup.sql

3. Экспорт структуры базы данных

Если вы хотите экспортировать только структуру базы данных, не включая данные, можно использовать опцию--no-data :

mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql

4. Экспорт сжатого файла

Вы можете сжать экспортированные данные, чтобы уменьшить размер файла. Например, с помощью gzip:

mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz

Экспорт данных в формате SQL

Экспорт данных в формате SQL в указанный файл, как показано ниже:

$ mysqldump -u root -p EXAMPLE example_tbl > dump.txt
password ******

Содержимое файла, созданного приведенной выше командой, выглядит следующим образом:

-- MySQL dump 8.23
--
-- Host: localhost    Database: EXAMPLE
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `example_tbl`
--

CREATE TABLE example_tbl (
  example_id int(11) NOT NULL auto_increment,
  example_title varchar(100) NOT NULL default '',
  example_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (example_id),
  UNIQUE KEY AUTHOR_INDEX (example_author)
) TYPE=MyISAM;

--
-- Dumping data for table `example_tbl`
--

INSERT INTO example_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO example_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO example_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

Если вам нужно экспортировать данные всей базы данных, можно использовать следующую команду:

$ mysqldump -u root -p EXAMPLE > database_dump.txt
password ******

Если вам нужно создать резервные копии всех баз данных, можно использовать следующую команду:

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

Опция --all-databases была добавлена в MySQL 3.23.12 и более поздних версиях.

Этот метод можно использовать для реализации стратегии резервного копирования базы данных.


Копирование таблиц и баз данных на другие хосты

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

Выполните следующую команду на исходном хосте, чтобы создать резервную копию данных в файле dump.txt:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

Если выполняется резервное копирование всей базы данных, нет необходимости использовать конкретные имена таблиц.

Если вам нужно импортировать базу данных из резервной копии на сервер MySQL, можно использовать следующую команду. При использовании следующей команды нужно убедиться, что база данных уже создана:

$ mysql -u root -p database_name < dump.txt
password *****

Вы также можете использовать следующую команду, чтобы напрямую импортировать экспортированные данные на удалённый сервер, но убедитесь, что два сервера соединены и имеют доступ друг к другу:

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

Приведённая выше команда использует конвейер для импорта экспортированных данных на указанный удалённый хост.

Другие расширения