Как загрузить csv в mysql

Mysql. Загрузка таблицы из CSV

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

DROP TABLE IF EXISTS orders; CREATE TABLE orders ( id_o INT, user_id INT, price DOUBLE, o_date DATE );

Файл CSV будем загружать из директории «/var/lib/mysql-files/», потому что mysql сервер запущен в безопасном режиме, который не позволяет загружать файлы откуда угодно.

Во время загрузки файла преобразуем поле с десятичной запятой в точку и сделаем распознание даты:

LOAD DATA INFILE '/var/lib/mysql-files/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ';' -- разделитель полей ENCLOSED BY '"' -- обрамление полей LINES TERMINATED BY '\n' -- конец строки IGNORE 1 ROWS -- первую строку пропускаем, в ней заголовки (@id_o, @user_id, @price, @o_date) SET id_o = @id_o, user_id = @user_id, price = REPLACE(@price, ',', '.'), o_date = STR_TO_DATE(@o_date,'%d.%m.%Y');

Импорт CSV-файла в таблицу MySQL

В этой статье мы расскажем вам, как использовать оператор LOAD DATA INFILE для импорта CSV-файла в таблицу MySQL.

Оператор LOAD DATA INFILE позволяет считывать данные из текстового файла и очень быстро импортировать их в таблицу базы данных.

Перед импортом файла, вам необходимо подготовить следующее:

• Таблицу базы данных, в которую будут импортированы данные из файла;
• CSV-файл с соответствующим числом столбцов и соответствующим форматом данных в каждом столбце;
• Учетную запись пользователя, который подключается к серверу базы данных MySQL и имеет привилегии FILE и INSERT .

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

Мы используем оператор CREATE TABLE , чтобы создать таблицу discounts :

Обновлено: 2014-03-12 11:18:17 Вадим Дворников автор материала

CREATE TABLE discounts ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, expired_date DATE NOT NULL, amount DECIMAL(10,2) NULL, PRIMARY KEY (id) );

Файл discounts.csv в первой строке содержит заголовки столбцов, в трех других строках — данные:

Следующий оператор импортирует данные из файла c:tmpdiscounts.csv в таблицу discounts :

LOAD DATA INFILE 'c:/tmp/discounts.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS;

Поля файла завершаются запятой, относящейся к FIELD TERMINATED BY ‘,’ , которая заключена в двойные кавычки, предусмотренные форматом ENCLOSED BY ‘»‘ .

Каждая строка CSV файла завершается символом новой строки, обозначающим TERMINATED BY ‘n’ .

Поскольку первая строка файла содержит заголовки столбцов, которые не должны быть импортированы в таблицу, мы игнорируем ее, указав опцию IGNORE 1 ROWS .

Теперь мы можем проверить, импортированы ли данные в таблицу discounts :

SELECT * FROM discounts;

Преобразование данных при импорте

Иногда формат данных не соответствует целевым столбцам таблицы. В простых случаях, вы можете преобразовать их с помощью условия SET в операторе LOAD DATA INFILE .

Предположим, что столбец данных срока действия скидок в файле discount_2.csv имеет формат мм / дд / гггг:

При импорте данных в таблицу discounts мы должны преобразовать их в формат даты MySQL с помощью функции str_to_date() :

LOAD DATA INFILE 'c:/tmp/discounts_2.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS (title,@expired_date,amount) SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');

Импорт файла клиента для замещения на сервере базы данных MySQL

Можно импортировать данные от клиента (локального компьютера) на удаленный сервер базы данных MySQL, с помощью оператора LOAD DATA INFILE .

При использовании опции LOCAL в LOAD DATA INFILE клиентская программа считывает файл на стороне клиента и отправляет его на сервер MySQL. Файл будет загружен во временную папку базы данных сервера операционной системы, например, C: Windows Temp для ОС Windows или / TMP для Linux .

Эта папка не настраивается и не задается MySQL.

Давайте рассмотрим следующий пример:

LOAD DATA LOCAL INFILE 'c:/tmp/discounts.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS;

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

Учетная запись пользователя, под которым мы подключается к серверу MySQL для импорта данных, может не иметь привилегию FILE , если используется опция LOCAL .

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

Импорт CSV файла с помощью MySQL Workbench

MySQL Workbench предоставляет инструмент для импорта данных в таблицу БД. Он позволяет редактировать данные перед внесением изменений.

Ниже приведены этапы импорта данных в таблицу БД:

Откройте таблицу, в которую будут загружаться данные:

MySQL: быстрая работа с CSV-файлами

Олимпийский девиз «Быстрее, выше, сильнее» постепенно начинает распространяться не только на спорт, но и на другие сферы жизни, и Веб не исключение: требования к скорости (читай — комфортности) работы постоянно растут. В этой статье мы рассмотрим, как ускорить обработку файлов обменных типов (CSV, XML) за счет использования базы данных.

Для начала обратим свои взоры на формат CSV.

CSV-файл (Comma Separated Values — «значения, разделенные запятыми») представляет собой обычный текстовый файл, содержащий таблицу данных. В первой строке такого файла обычно через запятую указаны имена столбцов, а во всех последующих строках — их значения в том же порядке. Так, например, при экспорте адресной книги из почтовой программы The Bat! в CSV-файл он может выглядеть так:

С помощью CSV-файла удобно обмениваться табличными данными

ФИО, E-mail, псевдоним
Иванов И. И., i@server.ua, Вано
Петров П. П., p@server.ua, Петруччо
Сидоров С. С., s@server.ua, Сидор

Самый большой плюс, снискавший популярность этому формату, — простота. Многие языки программирования имеют встроенные функции для работы с ним (например, fputcsv() и fgetcsv() в PHP), а в отсутствие оных программисту не составит труда написать функцию, разбирающую файл на составляюшие. Поэтому если наша задача — импортировать данные из CSV-файла в базу данных, то алгоритм будет таков:

• считать очередную строку;
• сформировать массив значений;
• выполнить INSERT-запрос к базе с этими значениями.

Этот метод отлично работает, если файл данных небольшой. Но реальный мир предъявляет свои требования — и как поведет себя такая функция с файлом размером в несколько десятков мегабайт?

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

Как решение можно использовать группировку INSERT-запросов: один большой INSERT вместо множества маленьких, MySQL это позволяет. Но построчная обработка все равно занимает львиную долю времени.

В таком случае лучше использовать технологию, которая называется bulk insert, — загрузка файла целиком, благо, как оказалось, MySQL предоставляет такую возможность: существует специальный запрос LOAD DATA INFILE, позволяющий оперативно загружать данные CSV-файла в таблицу базы данных, ведь они очень похожи по структуре. Этот запрос позволяет определить следующие параметры загрузки:

• имя загружаемого файла;
• опции полей и строк: какие символы используются для их разделения и экранирования;
• что делать с повторяющимися строками — пропускать или перезаписывать;
• пропуск первых N строк. Как правило, пропускают первую строку, содержащую заголовок;
• задать определенные значения нужным полям.

Читайте инструкцию, там все есть

Дополнительную информацию по более тонкой настройке можно найти в документации, впрочем, не слишком изобилующей примерами.

Поразительно, но упомянутый файл был загружен таким образом за 16 секунд.

Кстати, в других серверах БД тоже есть похожий инструментарий. Например, в MS SQL можно воспользоваться консольной программой bcp. Тем не менее выполнение SQL-запроса предпочтительней, поскольку обеспечивает кросс-платформенность, в то время как дополнительное ПО обычно жестко привязано к определенной операционной системе.

Таким образом, загрузка файла с:\1.csv может быть выполнена следующим запросом:

LOAD DATA INFILE ‘с:\\1.csv’
REPLACE
INTO TABLE table1
FIELDS
TERMINATED BY ‘,’
LINES
TERMINATED BY ‘\r\n’
IGNORE 1 LINES

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

Данные бывают разные, и часто нужно указать, в какой кодировке они созданы. К сожалению, запрос LOAD DATA INFILE не позволяет указать это явно, поэтому перед его выполнением нужно выставить нужную кодировку в серверной переменной character_set_database — согласно документации, именно она отвечает за разбор данных. Так, например, если файл был создан в Windows и содержит русский текст (кодировка Windows 1251), нужно запустить такую SQL-команду:

SET SESSION character_set_database = cp1251;

Как видно из этого примера, MySQL имеет собственный список имен кодировочных таблиц, не совпадающий со ставшими привычными обозначениями, используемыми в веб-страничках. Чтобы получить список доступных на MySQL-сервере кодировок (а этот список варьируется от одного сервера к другому), выполните следующий запрос:

SHOW CHARACTER SET;

Для импорта CSV-файлов в MySQL-базу через PHP можно использовать готовый класс Quick CSV import.

Теперь рассмотрим работу с XML (eXtensible Markup Language — «расширяемый язык разметки»).

Этот текстовый теговый формат весьма популярен, поскольку в унифицированной форме представляет данные любого формата, что делает его универсальным средством для обмена данными, но не их хранения, и потому его унифицированность наносит непоправимый ущерб удобству доступа к данным. XML в его стандратном обличье никогда не сравнится в удобстве работы с данными, скажем, с SQL.

Предыдущий пример CSV-файла, перенесенный в XML, будет выглядеть так:

Для переноса данных из XML-файла в базу данных изначально использовался такой алгоритм:

• считать весь файл в строку;
• преобразовать XML в PHP-массив;
• выполнить серию INSERT-запросов;

Этот метод, как и следовало ожидать, оказался, во-первых, очень медленным, во-вторых, требующим много памяти. По умолчанию PHP владеет 8 мегабайтами оперативной памяти, и попытки быстро работать с массивами, преобразованными из многомегабайтных прайс-листов в XML-формате, — жалкое зрелище. Другой замедляющий момент — обилие запросов к базе. Даже если веб-сервер и БД стоят на одной машине, что должно снижать накладные расходы, количество запросов будет так велико, что работа затянется надолго.

Поэтому имеет смысл опробовать такой алгоритм:

• считать весь файл в строку;
• преобразовать XML в PHP-массив;
• сохранить весь массив в CSV-файл;
• использовать быструю загрузку CSV в БД.

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

Пример ошибки, возникающей, если файл уже существует

По поводу упомянутого преобразования XML в массив: множество примеров можно найти на сайте php.net. Алгоритм этот нетривиальный, поэтому следует поэкспериментировать и выбрать подходящий.

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

Единственное ограничение — файл не должен существовать; это сделано во избежание несчастных случаев на производстве вроде затертых системных файлов.

Данный запрос использует те же настройки, что и запрос LOAD DATA INFILE.

Простейший SQL-запрос для экспорта будет выглядеть так:

SELECT * FROM ‘test’
INTO OUTFILE ‘c:\\1.txt’
FIELDS TERMINATED BY ‘,’

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

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