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

Перенос базы данных MySQL с помощью импорта и экспорта

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных Azure для MySQL — отдельный сервер

База данных Azure для MySQL — один сервер находится на пути прекращения поддержки. Настоятельно рекомендуется выполнить обновление до База данных Azure для MySQL — гибкий сервер. Дополнительные сведения о переходе на База данных Azure для MySQL —гибкий сервер см. в статье Что происходит с База данных Azure для MySQL отдельным сервером?

В этой статье описываются два обычных подхода к импорту и экспорту данных в базе данных Azure для сервера MySQL с помощью MySQL Workbench.

Подробные и исчерпывающие инструкции по миграции см. в разделе Ресурсы руководств по миграции.

Другие сценарии миграции рассматриваются в руководстве по переносу баз данных.

Предварительные требования

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

• Создайте базу данных Azure для сервера MySQL с помощью портала Azure.
• Скачайте и установите MySQL Workbench или другое стороннее средство MySQL для импорта и экспорта.

Создание базы данных в службе базы данных Azure для сервера MySQL

Создайте пустую базу данных на сервере базы данных Azure для MySQL с помощью инструментов MySQL Workbench, Toad или Navicat. База данных может иметь то же имя, что и база данных, которая содержит данные дампа. Вы также можете создать базу данных с другим именем.

Для подключения выполните следующие действия.

1. На портале Azure найдите сведения о подключении на панели Обзор базы данных Azure для MySQL.
2. Добавьте сведения о подключении MySQL Workbench.

Определите необходимость использования методов импорта и экспорта

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

В следующих сценариях следует использовать средства MySQL для импорта и экспорта баз данных в базу данных MySQL в Azure. Для сведений о работе с другими инструментариями перейдите к разделу «Методы миграции» (стр. 22) в руководстве по миграции базы данных MySQL в Azure.

• Если вам нужно выбрать несколько таблиц для импорта из имеющейся базы данных MySQL в базу данных Azure, лучше всего использовать метод импорта и экспорта. Таким образом, можно пропустить все ненужные таблицы в процессе переноса, чтобы сэкономить время и ресурсы. Например, используйте параметр —include-tables или —exclude-tables с mysqlpump и параметр —tables с mysqldump.
• При перемещении объектов, отличных от таблиц, необходимо будет явно создать эти объекты. Включите ограничения (первичный ключ, внешний ключ, индексы), представления, функции, процедуры, триггеры и другие объекты базы данных, которые требуется перенести.
• При перемещении данных из внешних источников данных, отличных от базы данных MySQL, создайте неструктурированные файлы и импортируйте их с помощью команды mysqlimport.

Как Отдельный сервер, так и Гибкий сервер поддерживают только подсистему хранилища InnoDB. Убедитесь, что все таблицы в базе данных используют подсистему хранилища InnoDB при загрузке данных в базу данных Azure для MySQL.

Если база данных источника использует другую подсистему хранилища, преобразуйте ее в подсистему InnoDB перед переносом базы данных. Например, при наличии WordPress или веб-приложения, которое использует ядро MyISAM, сначала преобразуйте таблицы путем переноса данных в таблицы InnoDB. Используйте предложение ENGINE=INNODB , чтобы задать ядро для создания таблицы, а затем передайте данные в совместимую таблицу перед переносом.

INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns 

Рекомендации по повышению производительности импорта и экспорта

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

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

Импорт и экспорт с помощью MySQL Workbench

Существует два способа экспорта и импорта данных в MySQL Workbench: из контекстного меню обозревателя объектов или из области навигатора. Каждый из них предназначен для своей цели.

Если вы добавляете подключение к отдельному серверу MySQL или гибкому серверу в MySQL Workbench, выполните следующие действия.

• Для отдельного сервера MySQL убедитесь, что имя пользователя имеет формат .
• Для Гибкого сервера MySQL следует использовать только . Если для подключения используется , произойдет сбой подключения.

Запустите мастера экспорта и импорта данных таблиц в контекстном меню обозревателя объектов

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

Для доступа к этим мастерам из контекстного меню обозревателя объектов щелкните правой кнопкой мыши таблицу, а затем выберите Мастер экспорта данных таблиц или Мастер импорта данных таблиц.

Мастер экспорта данных таблиц

Для выполнения экспорта таблицы в CSV-файл:

1. Щелкните правой кнопкой мыши таблицу базы данных, которую необходимо экспортировать.
2. Выберите Table Data Export Wizard (Мастер экспорта данных таблиц). Выберите столбцы, которые необходимо экспортировать, смещение строки (при необходимости) и количество (при необходимости).
3. На панели Выбор данных для экспорта нажмите кнопку Далее. Выберите путь к файлу, тип файла CSV или JSON. Также выберите разделитель строк, метод включения строк и разделитель полей.
4. На странице Выбор расположения выходного файла щелкните Далее.
5. На панели Экспорт данных нажмите кнопку Далее.

Мастер импорта данных таблиц

Чтобы импортировать таблицу из файла CSV, выполните следующее:

1. Щелкните правой кнопкой мыши таблицу базы данных, которую необходимо импортировать.
2. Найдите CSV-файл, который необходимо импортировать, выберите его, а затем щелкните Далее.
3. Выберите таблицу назначения (новую или имеющуюся), установите или снимите флажок Усечение таблицы перед импортом, затем нажмите Далее.
4. Выберите кодировку и столбцы, которые необходимо импортировать, и нажмите кнопку Далее.
5. На панели Импорт данных нажмите кнопку Далее. Мастер импортирует данные.

Запуск мастеров экспорта и импорта данных SQL через панель Навигатора

Используйте мастер для экспорта или импорта данных SQL, созданных в MySQL Workbench или с помощью команды mysqldump. Вы можете получить доступ к мастерам из области Навигатора или выбрать пункт Сервер в главном меню.

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

Используйте вкладку Экспорт данных для экспорта данных MySQL.

1. В MySQL Workbench в области Навигатор выберите Экспорт данных.
2. На панели Экспорт данных выберите каждую из схем, которые необходимо экспортировать. Для каждой схемы можно выбрать определенные объекты схемы или таблицы для экспорта. Параметры конфигурации включают экспорт в папку проекта или в самодостаточный файл SQL, выполнение дампа сохраненных процедур и событий или пропуск данных таблицы. В качестве альтернативы можно использовать команду Export a Result Set (Экспортировать результирующий набор), чтобы выполнить экспорт конкретного результирующего набора из редактора SQL в другой формат, например CSV, JSON, HTML или XML.
3. Выберите объекты базы данных, которые необходимо экспортировать, и настройте связанные параметры.
4. Щелкните Обновить, чтобы загрузить текущие объекты.
5. При необходимости выберите Дополнительные параметры в правом верхнем углу, чтобы настроить операцию экспорта. Например, можно добавить блокировки таблиц, использовать инструкцию replace вместо insert и заключить идентификаторы в кавычки в виде обратного апострофа.
6. Щелкните Начать экспорт, чтобы начать процесс экспорта.

Импорт данных

Вкладку Импорт данных можно использовать для импорта или восстановления данных из операции экспортирования данных или из команды mysqldump.

1. В MySQL Workbench в области Навигатор выберите Импорт и восстановление данных.
2. Выберите папку проекта или автономный файл SQL, затем выберите схему для импорта или нажмите кнопку Создать новую, чтобы определить новую схему.
3. Щелкните Начать импорт, чтобы начать процесс импорта.

Дальнейшие шаги

• Дополнительные сведения о другом способе переноса см. в статье Перенос базы данных MySQL в базу данных Azure для MySQL с помощью дампа и восстановления.
• Дополнительные сведения о переносе баз данных в службу «База данных Azure для MySQL» см. в статье Руководство по миграции баз данных.

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

Экспорт базы данных 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).

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

Импорт и экспорт баз данных 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