Как добавить sql файл в базу данных

Подключение баз данных SQL Server

В этом разделе описывается процедура подключения базы данных в SQL Server с помощью среды SQL Server Management Studio. Одновременно может быть подключена только одна база данных.

Для подключения базы данных необходимо иметь любое из следующих разрешений: CREATE DATABASE (Создание базы данных), CREATE ANY DATABASE (Создание любой базы данных) или ALTER ANY DATABASE (Изменение любой базы данных). Обычно эти разрешения предоставляются роли sysadmin экземпляра.

Как подключить базу данных

1. Запустите среду Microsoft SQL Server Management Studio.
2. Подключитесь к требуемому экземпляру SQL Server и разверните его.
3. Правой кнопкой мыши щелкните пункт Базы данных и щелкните Подключить .
4. Нажмите кнопку Добавить .
5. В диалоговом окне Поиск файлов баз данных найдите и выберите MDF-файл базы данных.
6. В разделе Сведения о базе данных убедитесь, что остальные файлы базы данных (NDB-файлы и LDF-файлы) также найдены. Подробнее. Файлы базы данных SQL Server могут быть не найдены автоматически, если:
   • Они находятся в расположении, отличном от расположения по умолчанию, или они не находятся в одной папке с основным файлом базы данных (MDF). Решение: Укажите путь к требуемым файлам вручную в столбце Путь к текущему файлу .
   • Вы восстановили неполный набор файлов, составляющих базу данных. Решение: Восстановите отсутствующие файлы базы данных SQL Server из резервной копии.
7. Когда все файлы будут найдены, нажмите кнопку ОК .

Дата последней сборки: 6 марта 2023 г.

Веб-справка по Acronis Cyber Protect 15 Обновление 6 . © Acronis International GmbH, 2003-2023

SQL-Ex blog

Импорт данных в базу данных MySQL с помощью LOAD DATA

Добавил Sergey Moiseenko on Среда, 6 сентября. 2023

Команды разработки и баз данных часто загружают данные из плоских текстовых файлов в свои базы данных MySQL. Файлы могут использоваться для добавления справочных данных, поддержки тестов и сред разработки, наполнении новых экземпляров MySQL, загрузки данных на регулярной основе, или иным образом поддерживать их деятельность. В помощь процессу импорта MySQL предоставляет оператор LOAD DATA, который читает строки из текстового файла и вставляет их в целевую таблицу.

В этой статье я покажу как использовать оператор LOAD DATA для добавления данных из файлов CSV (значения с запятой-разделителем) и других плоских текстовых файлов. Хотя примеры довольно просты, они демонстрируют основные элементы использования оператора LOAD DATA и некоторые проблемы, с которыми вы можете столкнуться по пути. Каждый пример извлекает данные из файла в локальной системе и добавляет данные в таблицу manufacturers в базе данных travel, которую вы уже видели в предыдущих статьях этой серии.

Замечание. Для примеров этой статьи использовался локальный экземпляр MySQL, на котором была создана очень простая база данных и таблица. В последнем разделе статьи — «Приложение: подготовка среды MySQL» — приведена информация об установке моей системы и дан скрипт SQL для создания базы данных и таблицы, на которых основаны примеры.

Подключение к серверу MySQL

Импорт данных из текстового файла в базу данных MySQL сам по себе достаточно простой процесс. Часто самым трудным оказывается такая установка вашей среды, которая позволит выполнять оператор LOAD DATA и импортировать данные в целевую таблицу. Как и для любого оператора SQL в MySQL, вы должны иметь предоставленные привилегии, необходимые для выполнения операций (тема, выходящая за рамки этой статьи). Однако есть несколько других проблем, о которых необходимо знать, чтобы импортировать данные, начиная с опции LOCAL.

• Если вы не указываете опцию LOCAL, текстовый файл источника должен размещаться на хосте MySQL. Когда вы выполняете оператор LOAD DATA, MySQL читает файл непосредственно из каталога и вставляет данные в целевую таблицу. Этот подход обычно работает немного лучше, чем при включении опции LOCAL, поскольку данные загружаются напрямую. Однако получение прав на подключение значительно более сложное (по этому поводу ведется много дискуссий в сети).
• Если вы указываете опцию LOCAL, текстовый файл источника должен размещаться на машине клиента. Клиент читает файл и посылает содержимое на сервер, где оно сохраняется во временном файле, пока не будет загружен в целевую таблицу для обработки. Опция LOCAL также работает, если клиент и MySQL находятся на одной и той же машине, и этот подход я применяю в этой статье. Соединение обычно значительно проще установить, когда вы используете опцию LOCAL.

Я также считаю, что для многих администраторов и разработчиков баз данных размещение исходных файлов на стороне клиента является предпочтительным для загрузки этих файлов на сервер MySQL, если им вообще разрешено это делать. Если вы используете опцию LOCAL, вам не нужна привилегия FILE для выполнения оператора LOAD DATA, и вы можете хранить текстовый файл источника в любой локальной папке, к которой клиентское приложение имеет доступ; клиентом в нашем случае является MySQL Workbench.

Замечание. В документации MySQL говорится, что «если указана опция LOCAL, то файл может находиться на машине клиента». Однако я смог выполнить оператор LOAD DATA, который включал опцию LOCAL и который извлекал данные из других систем в моей сети. Первым был другой компьютер Mac, а другим — Windows 11 на виртуальной машине. Другие варианты я не тестировал.

При использовании опции LOCAL вы должны убедиться, что загрузка данных включена, как на стороне клиента, так и на стороне сервера. Чтобы включить ее на стороне клиента в Workbench, вы должны переключиться на главный экран инструментов. В главном окне щелкните правой кнопкой на соединении и щелкните Edit connection. На странице Connection диалогового окна Manage Server Connections выберите вкладку Advanced и добавьте следующую команду в поле Others:

OPT_LOCAL_INFILE=1

Эта команда устанавливает опцию local-infile в значение ON (включено), делая возможным выполнение оператора LOAD DATA, который включает опцию LOCAL. На следующем изображении показано (выделено красным), как установка выглядит на вкладке соединения Advanced. Эта установка применяется только к подключениям данного пользователя в Workbench. Другие соединения должны конфигурироваться индивидуально.

Помимо включения опции local-infile, вы должны также включить глобальную переменную local_infile на сервере, если она еще не включена. (Эти имена различаются только нижним подчеркиванием в имени глобальной переменной.) Для подтверждения установки переменной вы можете выполнить оператор SHOW GLOBAL VARIABLES на экземпляре MySQL:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

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

SET GLOBAL local_infile = 1;

Как только вы включили локальную загрузку данных и на клиенте, и на сервере, вы должны быть готовы к выполнению ваших операторов LOAD DATA. Последующие примеры демонстрируют различные аспекты импорта данных из текстового файла. Я покажу вам содержимое каждого файла, с которыми мы будем работать в примерах. Вы сможете создать их в своей системе, если захотите попрактиковаться.

Введение в оператор LOAD DATA

Прежде чем перейти к первому примеру, важно понять основные компоненты, которые присутствуют в операторе LOAD DATA и которые включают большое число предложений и субпредложений. Следующий синтаксис несколько упрощает оператор, чтобы дать вам обзор основных элементов и показать, как они сочетаются друг с другом:

LOAD DATA [LOCAL] 
INFILE 'имя_файла' 
[REPLACE | IGNORE] 
INTO TABLE имя_таблицы 
FIELDS 
[TERMINATED BY 'string'] 
[[OPTIONALLY] ENCLOSED BY 'char'] 
[ESCAPED BY 'char'] 
LINES 
[STARTING BY 'string'] 
[TERMINATED BY 'string'] 
IGNORE n LINES 
[(список_столбцов)]

Предложение LOAD DATA — это то место, где вы указываете, нужно ли включать опцию LOCAL. Как я упомянул ранее, это тот подход, который используется в данной статье. Следующее предложение, INFILE, задает путь и имя текстового файла источника (в кавычках). Вы можете указать абсолютный путь или относительный. Если относительный, то используется путь относительно каталога вызовов.

Затем вы можете указать либо REPLACE, либо IGNORE, которые не являются обязательными опциями. Опция REPLACE сообщает MySQL заменить существующие строки, которые имеют то же самое значение уникального ключа. Опция IGNORE говорит MySQL игнорировать строки с тем же самым значением ключа. Опция IGNORE оказывает тот же эффект, что и опция LOCAL, поэтому, если вы используете LOCAL, вам никогда не нужно использовать IGNORE. Однако вы можете использовать опцию REPLACE с LOCAL.

Предложение INTO TABLE задает имя целевой таблицы. Здесь главное убедиться, что вам были предоставлены необходимые привилегии для добавления данных в таблицу.

• Предложение TERMINATED BY задает строку, которая используется в текстовом файле для разделения полей. Строка может состоять из одного или более символов. Значением по умолчанию является \t (табулятор), которое означает, что табулятор используется для разделения полей.
• Предложение ENCLOSED BY задает символ, используемый в текстовом файле для ограничивания значений, например, кавычек вокруг строковых значений. Слово OPTIONALLY, которое само является необязательным, используется, «если входные значения не обязательно заключаются в кавычки», согласно документации MySQL. (Об этом чуть позже.) Значением по умолчанию для предложения ENCLOSED BY является пустая строка, говорящее о том, что поля не закавычиваются.
• Предложение ESCAPED BY задает символ, используемый в текстовом файле для экранирования символов, которые могут повлиять на интерпретацию данных MySQL. Значением по умолчанию является обратный слэш (\), который также используется в MySQL для экранирования символов, включая сам обратный слэш. Многие языки программирования также используют обратный слэш для экранирования символов.

Замечание. Опция OPTIONALLY в подчиненном предложении ENCLOSED BY является элементом, вызывающим наибольшее недоумение в операторе LOAD DATA. Его использование никак не сказывалось в различных тестах, которые я выполнял. Например, в одном тесте я заключал все значения в полях manufacturer в двойные кавычки, за исключением одного. MySQL импортировал данные корректно вне зависимости от того, была ли включена опция OPTIONALLY. Я также тестировал вариант использования NULL-значений и пустых строк и получал те же результаты. Возможно, есть случаи использования, в которых опция дает различия, но я пока этого не обнаружил. Однако предложения FIELDS и LINES в операторе LOAD DATA подобны используемым в операторе SELECT…INTO OUTFILE, и большая часть обсуждения опции OPTIONALLY в документации MySQL связана с SELECT… INTO OUTFILE, так что, возможно, именно здесь она наиболее актуальна.

• Предложение STARTING BY задает общий префикс, используемый в начале каждой строки текстового файла. Значением по умолчанию является пустая строка, означающее, что никакой конкретный префикс не используется. Если префикс указан, но строка не содержит этот префикс, MySQL пропустит эту строку при импорте данных.
• Предложение TERMINATED BY задает строку, используемую в текстовом файле для завершения каждой строки. Эта строка может состоять из одного или большего числа символов. Значением по умолчанию является \n, означающее символ новой строки (подача строки). Я создавал мой текстовый файл в приложении Apple TextEdit, поэтому значение по умолчанию работает в моей системе, но не все системы работают так же. Например, если вы создаете текстовый файл в Windows, вам может потребоваться задать ‘\r\n’ в качестве значения TERMINATED BY.

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

Оператор LOAD DATA содержит несколько других предложений, но тех, которые я рассмотрел здесь, вполне достаточно, чтобы начать работать. Тем не менее, я рекомендую вам посмотреть статью MySQL об операторе LOAD DATA, чтобы познакомиться с различными элементами этого оператора.

Импорт файла CSV

Теперь, когда вы познакомились с оператором LOAD DATA, давайте рассмотрим несколько примеров, которые покажут его в действии. Вы можете возвращаться к предыдущему разделу при необходимости во время работы со следующими разделами.

Для подготовки первого примера я создал файл с именем manufacturers1.csv и следующими данными:

101,Airbus
102,Beagle Aircraft Limited
103,Beechcraft
104,Boeing
105,Bombardier
106,Cessna
107,Embraer

Я сохранил этот файл в папке /Users/mac3/Documents/TravelData/ на локальном компьютере. Если вы собираетесь сами выполнять примеры, то можете сохранять файлы в любом месте в вашей системе, к которой Workbench имеет доступ. Просто не забывайте обновлять путь к файлу в примерах перед выполнением операторов.

После создания файла manufacturers1.csv я выполнил следующий оператор LOAD DATA, который сохраняет данные в таблице manufacturers в базе данных travel:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers1.csv' 
INTO TABLE manufacturers 
FIELDS TERMINATED BY ',' 
(manufacturer_id, manufacturer);

Как видно, предложение LOAD DATA включает опцию LOCAL, и предложение INFILE задает файл источника. За этим следует предложение INTO TABLE, которое указывает на таблицу manufacturers.

Следующее предложение, FIELDS, включает подчиненное предложение TERMINATED BY, которое указывает, что в качестве разделителя полей используется запятая, а не принимаемый по умолчанию табулятор. Затем в операторе приводятся имена двух целевых столбцов — manufacturer_id и manufacturer — которые заключены в скобки.

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

SELECT * FROM manufacturers;

Оператор SELECT возвращает результаты, показанные на следующем рисунке, из которого очевидно, что данные был успешно вставлены в таблицу. Сохраните этот оператор, поскольку вы можете его использовать для проверки результатов в остальных примерах.

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

TRUNCATE TABLE manufacturers;

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

Игнорировать первые строки в импортируемом файле

Некоторые файлы-источники, с которым вы работаете, могут содержать строку заголовков, в которой перечисляются имена полей или включена информация другого типа, например, комментарии о том, где и когда был создан файл. Вы можете пропустить эти строки при импорте данных, включив предложение IGNORE n LINES в ваш оператор LOAD DATA.

Чтобы увидеть как это работает, создайте текстовый файл с именем manufacturers2.csv file, добавьте в него следующие данные, и сохраните файл в том же месте, где и manufacturers1.csv:

manufacturer_id,manufacturer
101,Airbus
102,Beagle Aircraft Limited
103,Beechcraft
104,Boeing
105,Bombardier
106,Cessna
107,Embraer

Теперь выполните следующий оператор LOAD DATA, который включает предложение IGNORE 1 LINES, говорящее MySQL пропустить первую строку:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers2.csv' 
INTO TABLE manufacturers 
FIELDS TERMINATED BY ',' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

После выполнения оператора LOAD DATA вы снова можете выполнить оператор SELECT, чтобы проверить правильность добавленных данных. Результаты должны показать, что строка заголовков была опущена. Затем вы снова можете выполнить оператор TRUNCATE для подготовки к следующему примеру.

Предложение IGNORE n LINES не ограничивается одной строкой. Например, следующее предложение IGNORE n LINES задает 5 строк, а не одну:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers2.csv' 
INTO TABLE manufacturers 
FIELDS TERMINATED BY ',' 
IGNORE 5 LINES 
(manufacturer_id, manufacturer);

Если теперь выполнить оператор SELECT, вы должны получить результаты, показанные на следующем рисунке. (Не удаляйте содержимое таблицы для этого примера или следующего, поскольку я хочу указать на пару других проблем.)

Как видно, таблица содержит только последние три строки из файла источника. Однако предположим, что вы должны были запустить оператор снова, только на этот раз указав одну строку в предложении IGNORE n LINES:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers2.csv' 
INTO TABLE manufacturers 
FIELDS TERMINATED BY ',' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

При выполнении этого оператора MySQL пытается вставить все семь строк данных в целевую таблицу, но только первые четыре строки достигают цели. После выполнения оператора MySQL возвращает следующее сообщение:
4 row(s) affected, 3 warning(s): 1062 Duplicate entry ‘105’ for key ‘manufacturers.PRIMARY’ 1062 Duplicate entry ‘106’ for key ‘manufacturers.PRIMARY’ 1062 Duplicate entry ‘107’ for key ‘manufacturers.PRIMARY’ Records: 7 Deleted: 0 Skipped: 3 Warnings: 3
(обработано 4 строки, 3 предупреждения: Дубликат записи ‘105’ для ключа
‘manufacturers.PRIMARY’
Дубликат записи ‘106’ для ключа ‘manufacturers.PRIMARY’
Дубликат записи ‘107’ для ключа ‘manufacturers.PRIMARY’
Записи: 7 Удалено: 0 Пропущено 3 Предупреждений: 3)

В сообщении говорится, что существующе строки со значениями manufacturer_id 105, 106 и 107 были пропущены. Т.е. новые строки с этим значениями не были вставлены в таблицу. Были добавлены только первые четыре строки. Если выполнить оператор SELECT еще раз, то вы должны получить результаты, подобные приведенным на следующем рисунке. (Опять таки не очищайте таблицу; оставьте ее для следующего примера.)

Теперь таблица содержит все семь строк данных, но если внимательно посмотреть на время на рисунке, то можно заметить, что последние три строк предшествуют первым пяти примерно на 30 секунд. (Я выполнял последние операторы LOAD DATA один за другим.)

Теперь предположим, что вы снова выполняете тот же самый оператор LOAD DATA, но теперь вы включает опцию REPLACE:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers2.csv' 
REPLACE 
INTO TABLE manufacturers 
FIELDS TERMINATED BY ',' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

При выполнении оператора MySQL вернет теперь следующее сообщение:

14 row(s) affected Records: 7 Deleted: 7 Skipped: 0 Warnings: 0
Обработано 14 строк Записи: 7 Удалено: 7 Пропущено: 0 Предупреждений: 0)

В сообщении сказано об обработке 14 строк. Однако только 7 строк было затронуто и 7 — удалено. Это означает, что ядро базы данных удалило семь существующих записей и повторно добавило их в таблицу. Вы можете это проверить, снова выполнив оператор SELECT. Ваши результаты должны показать другое время по сравнению с предыдущими результатами, при этом все значения должны быть близки, если не одинаковы.

Теперь вы можете снова выполнить оператор TRUNCATE TABLE, чтобы подготовить таблицу manufacturers для следующего примера.

Работа с заковыченными полями в файле импорта

При импорте данных ваш текстовый файл может включать все или некоторые поля заключенными в кавычки. Для примера я создал файл manufacturers3.csv, содержащий следующие данные, которые включают одиночные кавычки вокруг строковых значений:

manufacturer_id,manufacturer 
101,'Airbus' 
102,'Beagle Aircraft Limited' 
103,'Beechcraft' 
104,'Boeing' 
105,'Bombardier' 
106,'Cessna' 
107,'Embraer'

Для обработки полей в кавычках вы можете добавить подчиненное предложение ENCLOSED BY в предложение FIELDS, как показано в следующем примере:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers3.csv' 
INTO TABLE manufacturers 
FIELDS TERMINATED BY ',' ENCLOSED BY '\'' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

Предложение ENCLOSED BY указывает, что поля заключены в одиночные кавычки. Символу кавычки предшествует обратный слэш для экранирования символа при передаче его ядру базы данных. Если вы не используете предложение ENCLOSED BY, ядро базы данных будет рассматривать символы кавычки как литеральные значения и сохранять их наряду с остальными значениями.

После выполнения оператора LOAD DATA вы можете выполнить оператор SELECT для проверки результатов, а затем подготовить таблицу manufacturers для следующего примера, выполнив оператор TRUNCATE.

При задании символа одиночной кавычк в подчиненном предложении ENCLOSED BY вы можете заключить ее в двойные кавычки, а не экранировать с помощью обратного слэша:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers3.csv' 
INTO TABLE manufacturers 
FIELDS TERMINATED BY ',' ENCLOSED BY "'" 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

В некоторых случаях текстовый файл будет использовать двойные кавычки для ограничителей значений полей, а не одиночные кавычки. Для демонстрации их обработки я создал файл manufacturers4.csv со следующими данными:

manufacturer_id,manufacturer
101,»Airbus»
102,»Beagle Aircraft Limited»
103,»Beechcraft»
104,»Boeing»
105,»Bombardier»
106,»Cessna»
107,»Embraer»

Для обработки этого файла нужно модифицировать подчиненное предложение ENCLOSED BY для указания двойной кавычки, заключив ее в одиночные кавычки:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers4.csv' 
INTO TABLE manufacturers 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

После выполнения этого оператора LOAD DATA вы можете снова выполнить оператор SELECT для проверки результатов. После просмотра вы можете выполнить оператор TRUNCATE для подготовки следующего примера. (Вам следует это делать для всех оставшихся примеров.)

Работа с различными форматами в текстовых файлах

Текстовые файлы, с которыми вы работаете, могут использовать в качестве разделителя табулятор, а не запятую, и они могут включать другие элементы, которые требуют специальной обработки. Рассмотрим файл manufacturers5.txt, который я создал со следующими данными:

manufacturer_id manufacturer
,*101 «Airbus»
,*102 «Beagle Aircraft Limited»
,*103 «Beechcraft»
,*104 «Boeing»
,*105 «Bombardier»
,*106 «Cessna»
,*107 «Embraer»

В данном случае разделителем полей служит табулятор, а каждой строке предшествуют символы ,*. В результате вам не требуется задавать подчиненное предложение TERMINATED BY в предложении FIELDS, поскольку табулятор является значением по умолчанию, но вам потребуется кое-что предпринять для обработки префикса строки. Для этого вам нужно добавить предложение LINES с подчиненным предложением STARTING BY, которое задает символы префикса:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers5.txt' 
INTO TABLE manufacturers 
FIELDS ENCLOSED BY '"' 
LINES STARTING BY '*,*' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

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

Как уже отмечалось, предыдущий пример не включает подчиненное предложение TERMINATED BY в предложении FIELDS. Он также не включает подчиненное предложение TERMINATED BY в предложении LINES, поскольку в текстовом файле используется принимаемое по умолчанию значение конца строки. Однако вы по-прежнему можете включить оба эти предложения, если хотите:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers5.txt' 
INTO TABLE manufacturers 
FIELDS TERMINATED BY '\t' ENCLOSED BY '"' 
LINES TERMINATED BY '\n' STARTING BY '*,*' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

При использовани подчиненного предложения STARTING BY имейте в виду, что ваш текстовый файл должен последовательно использовать эти префиксы, или вы можете получить неожиданные результаты. Например, следующий текстовый файл, manufacturers6.txt, включает строку с двумя записями, но перед первой нет префикса:

manufacturer_id manufacturer
,*101 «Airbus»
,*102 «Beagle Aircraft Limited»
,*103 «Beechcraft»
104 «Boeing» ,*105 «Bombardier»
,*106 «Cessna»
,*107 «Embraer»

После создания файла у себя вы можете выполнить следующий оператор LOAD DATA, чтобы посмотреть, что получится:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers6.txt' 
INTO TABLE manufacturers 
FIELDS ENCLOSED BY '"' 
LINES STARTING BY '*,*' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

При выполнении этого оператора MySQL пропустит запись с значением manufacturer_id 104, но добавит запись с значением 105. Вы можете проверить это, снова выполнив оператор SELECT, который возвращает результаты, показанные на следующем рисунке.

В некоторых случаях вы можете столкнуться с текстовыми файлами, строки которых заканчиваются нетрадиционными символами (в отличие от обычных подачи строки или возврата каретки). Например, я создал файл manufacturers7.txt со следующими данными, в которых строки разделяются тройными хэш-символами (###):

manufacturer_id manufacturer###101 "Airbus"###102 "Beagle Aircraft Limited"###103 "Beechcraft"###104 "Boeing"###105 "Bombardier"###106 "Cessna"###107 "Embraer"

Для обработки этого файла вам нужно включить подчиненное предложение TERMINATED BY в предложение LINES, которое задает хэш-символы:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers7.txt' 
INTO TABLE manufacturers 
FIELDS ENCLOSED BY '"' 
LINES TERMINATED BY '###' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

Когда вы выполняете этот оператор, ядро базы данных узнает, как интерпретировать хэш-символы, и будет вставлять данные соответствующим образом, отбрасывая хэш-символы.

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

manufacturer_id,manufacturer
101,Airbus
102,Beagle Aircraft Limited
103,Beechcraft
104,Aviat Aircraft^, Inc.
105,Bombardier
106,Cessna
107,Embraer

В данном случае запятая в имени экранируется символом каре (^). Поскольку этот символ не является обратным слэшем (символом экранирования по умолчанию), вам необходимо добавить предложение ESCAPE BY, которое задает «каре», как показано в следующем примере:

LOAD DATA LOCAL INFILE 
'/Users/mac3/Documents/TravelData/manufacturers8.txt' 
INTO TABLE manufacturers 
FIELDS TERMINATED BY ',' ESCAPED BY'^' 
IGNORE 1 LINES 
(manufacturer_id, manufacturer);

Если не включить предложение ESCAPE BY, ядро базы данных оставит каре, но обрежет имя производителя до Aviat Aircraft^. Однако, если включить это предложение, MySQL уберет каре и будет считать запятую литеральным значением, что даст значение столбца Aviat Aircraft, Inc., а не усеченную версию.

Начало работы с импортом данных в MySQL

Как было упомянуто ранее, оператор LOAD DATA включает и другие элементы помимо рассмотренных здесь. Имеются также другие варианты импорта данных, такие как утилита командной строки mysqlimport , которая генерирует и посылает операторы LOAD DATA на сервер MySQL. Большинство опций утилиты подобны используемым в операторе LOAD DATA. Другим вариантом является мастер Table Data Import в MySQL Workbench. Мастер проводит вас через процесс импорта данных из файлов CSV или JSON.

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

Приложение. Подготовка среды MySQL

При создании примеров для этой статьи я использовал компьютер Mac, на котором был установлен локально экземпляр MySQL 8.0.29 (Community Server edition). Я также испльзовал MySQL Workbench в качестве интерфейса к MySQL. Импорт данных в примерах использовал набор тестовых текстовых файлов, которые я создал в текстовом редакторе TextEdit от Apple.

Я предоставил вам содержимое файлов в тексте статьи, наряду с примерам операторов LOAD DATA. Если вы собираетесь поработать с этими примерами, вы можете создавать файлы в своей системе по мере рассмотрения примеров. Однако прежде чем начать, следует выполнить следующий скрипт на вашем экземпляре MySQL:

DROP DATABASE IF EXISTS travel; 
CREATE DATABASE travel; 
USE travel; 
CREATE TABLE manufacturers ( 
manufacturer_id INT UNSIGNED NOT NULL, 
manufacturer VARCHAR(50) NOT NULL, 
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
last_update TIMESTAMP NOT NULL 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (manufacturer_id) );

Этот скрипт создает базу данных travel с таблицей manufacturers. Это все, что вам необходимо для проверки работы примеров (помимо создания текстовых файлов источника). Для большинства примеров я просто удалял данные для подготовки таблицы к следующему примеру. Если вы уже создали базу данных и таблицу, используя материал из предыдущих статей, я рекомендую пересоздать их или, по крайней мере, почистить таблицу manufacturers до начала работы.

Ссылки по теме

1. Резервирование в MySQL. Часть 1: mysqldump
2. Оператор TRUNCATE TABLE

Импорт и экспорт баз данных MySQL через консоль и phpMyAdmin

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

Все таблицы из базы данных можно экспортировать в отдельный файл, который потом же импортировать обратно для восстановления информации. Кроме этого, созданный дамп БД можно использовать для переноса информации в другую базу MySQL.

Существует два способа импорта/экспорта:

• через веб-интерфейс PhpMyAdmin;
• подключившись к консоли MySQL локально или через SSH.

Подробнее о подключении через протокол SSH можно узнать здесь.

Все описанные действия с MySQL схожи по выполнению на любых операционных системах Linux.

Экспорт базы данных с помощью PhpMyAdmin

В первую очередь требуется выполнить вход в интерфейс PhpMyAdmin. Список существующих баз находится в верхнем левом углу.

Выбрав необходимую базу данных, нужно перейти в раздел «Экспорт».

Для быстрого экспорта подойдёт вариант «Быстрый – отображать минимум настроек», при котором используются оптимальные параметры переноса. Чтобы открыть список дополнительных опций, нужно выбрать метод «Обычный – отображать все возможные настройки».

Во вкладке «Формат» рекомендуется выставить расширение SQL (.sql) для экспортируемого файла. Но также можно использовать и другие форматы, такие как CSV (.csv) и XML (.xml).

Определившись с параметрами, остаётся выполнить экспорт нажатием «Вперёд». В браузере начнётся загрузка файла.

Экспорт базы данных MySQL через консоль

Экспорт базы данных выполняется с помощью утилиты MySQLdump:

mysqldump --user=Login --password=Password db_name > db_name.sql

Переменные в приведенном примере:

• «Login» – имя пользователя MySQL.
• «Password» – пароль пользователя MySQL.
• «db_name» – наименование базы данных.
• «db_name.sql» – конечный файл.

MySQLdump позволяет экспортировать базу данных с удаленного сервера (если хосту разрешено подключение):

mysqldump --user=Login --password=Password --host=XXX.XXX.XXX.XXX db_name > db_name.sql

*Вместо «XXX.XXX.XXX.XXX» указывается актуальный IP-адрес сервера.

Импорт базы данных с помощью PhpMyAdmin

Для начала необходимо авторизоваться в интерфейсе PhpMyAdmin и выбрать базу данных в левом верхнем углу. После этого стоит перейти в раздел «Импорт».

Теперь нужно нажать «Выбрать файл» и в открывшемся проводнике выбрать SQL-файл (принимаются также архивы Gzip, Bzip2, Zip). В разделе «Формат» отмечается расширение используемого файла.

Все остальные параметры изменять необязательно. Завершить импорт можно нажатием кнопки «Вперёд»

Импорт базы данных MySQL через консоль (замена существующей)

Для начала необходимо авторизоваться в командной строке MySQL:

mysql --user=Login --password=Password

*Вместо «Login» и «Password» вводятся действительные данные пользователя MySQL.

Теперь выбирается существующая база данных, с которой планируется дальнейшая работа:

use db_name

*Вместо «db_name» вводится наименование существующей БД.

Остаётся выполнить импорт таблиц следующей командой:

source db_name.sql

*Вместо «db_name.sql» вводится путь до файла SQL (предварительно загружается на сервер).

Импорт и создание базы данных MySQL через консоль

Выше был описан способ импорта, при котором осуществляется замена таблиц в существующей базе данных. Но такой вариант не подойдёт, если БД изначально не существует. В таком случае потребуется сначала создать новую базу данных, а уже потом импортировать в неё таблицы из резервной копии.

Для начала необходимо авторизоваться в консоли MySQL:

mysql --user=Login --password=Password

*Вместо «Login» и «Password» вводятся действительные данные пользователя MySQL.

Далее нужно создать новую базу данных:

CREATE DATABASE new_database;

*Вместо «new_database» вводится собственное наименование базы данных.

На экране появится следующий вывод:

Output Query OK, 1 row affected (0.00 sec)

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

use new_database

Остаётся импортировать таблицы из резервной копии:

source db_name.sql

*Вместо «db_name.sql» вводится путь до файла SQL (предварительно загружается на сервер).

Надежный хостинг для сайта. 14 дней — бесплатно!

Мы всегда на связи в соцсетях

Поддержка в привычной среде

Мы в Яндекс Кью

• Хостинг для сайта
• CMS хостинг
• VPS сервер
• Удалённый рабочий стол
• Домены
• Стресс-тест на DDoS

Импорт и экспорт базы данных MySQL

Экспорт базы данных MySQL через консоль и phpMyAdmin

через phpMyAdmin
через консоль

Войдите в phpMyAdmin. Кликните по названию базы данных, экспорт которой вы хотите произвести:

Перейдите на вкладку «Экспорт», выберите формат SQL и нажмите Вперед:

На вашем компьютере откроется диалоговое окно. Сохраните базу данных.

Готово, база данных экспортирована на ваш компьютер.

• USER — пользователь базы данных (например, «u1234567_dbuser»),
• DBNAME — имя экспортируемой базы данных (например, «u1234567_database»),
• ~/DUMPFILE.sql — путь к файлу, в котором сохранится дамп базы данных в формате SQL.

Затем введите пароль базы данных (PASSWORD).

Готово, дамп базы данных с названием DUMPFILE.sql будет сохранен в корневую директорию услуги хостинга.

Импорт базы данных MySQL через консоль и phpMyAdmin

через phpMyAdmin
через консоль

Выберите базу данных, в которую будет производиться импорт дампа (резервной копии базы данных):

Перейдите на вкладку «Импорт», нажмите Выберите файл и укажите файл дампа базы. При необходимости смените кодировку. Выберите формат SQL и нажмите Вперёд:

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

При успешном импорте появится сообщение:

Готово, база данных импортирована.

Предварительно загрузите в корневую директорию хостинга файл дампа базы данных по FTP или через панель управления.

• USER — пользователь базы данных (например, «u1234567_dbuser»),
• DBNAME — имя импортируемой базы данных (например, «u1234567_database»),
• ~/DUMPFILE.sql — путь к файлу, в котором сохранится дамп базы данных в формате SQL.

Затем введите пароль базы данных (PASSWORD).

Готово, база данных импортирована.