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

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

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

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

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

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

discounts

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

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 в первой строке содержит заголовки столбцов, в трех других строках — данные:

Файл 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 имеет формат мм / дд / гггг:

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 предоставляет инструмент для импорта данных в таблицу БД. Он позволяет редактировать данные перед внесением изменений.

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

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

этапы импорта

Нажмите кнопку Import, выберите CSV файл и нажмите кнопку Open:

CSV файл

Просмотрите данные, нажмите кнопку Apply:

данные
Apply

В MySQL Workbench появится диалоговое окно «Apply SQL Script to Database», нажмите кнопку Apply, чтобы добавить данные в таблицу БД.

Мы рассказали вам, как импортировать CSV в таблицу MySQL с помощью LOAD DATA LOCAL, а также с использованием MySQL Workbench.

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

Перевод статьи «Import CSV File Into MySQL Table» был подготовлен дружной командой проекта Сайтостроение от А до Я.