SQL-Ex blog

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

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

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

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

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

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


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

Для примеров в этой статье я использовал опцию LOCAL. Без нее не только усложняются требования к подключению MySQL, но они также не очень хорошо документированы, что разочарует вас, если вы столкнетесь с какими-либо глюками. Если вы посмотрите публикации на различных форумах, в которых обсуждаются проблемы подключения с оператором LOAD DATA, вы обнаружите, что в большинстве случаев люди отвечают на пост, предлагая использовать опцию 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 задает имя целевой таблицы. Здесь главное убедиться, что вам были предоставлены необходимые привилегии для добавления данных в таблицу.

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

• Предложение TERMINATED BY задает строку, которая используется в текстовом файле для разделения полей. Строка может состоять из одного или более символов. Значением по умолчанию является \t (табулятор), которое означает, что табулятор используется для разделения полей.


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


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

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

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

Подобно предложению FIELDS, предложение LINES также является необязательным. Предложение LINES поддерживает два подчиненных предложения:

• Предложение STARTING BY задает общий префикс, используемый в начале каждой строки текстового файла. Значением по умолчанию является пустая строка, означающее, что никакой конкретный префикс не используется. Если префикс указан, но строка не содержит этот префикс, MySQL пропустит эту строку при импорте данных.


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

Если включены оба предложения, FIELDS и LINES, то предложение FIELDS должно идти первым. Предложение IGNORE n LINES следует после этих двух предложений. Предложение IGNORE n LINES задает число строк, пропускаемых от начала файла при импорте данных. Это предложение обычно используется, когда файл содержит строку заголовков; в подобном случае предложение должно быть написано так: IGNORE 1 LINES.

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

Оператор 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