MySQL: Загрузка данных в таблицу из файла

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

Загрузить данные в таблицу из файла

LOAD DATA INFILE 'data.csv' INTO TABLE my_table

Такая конструкция поможет вам загрузить данные из файла в вашу таблицу.

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

LOAD DATA INFILE 'data.csv' INTO TABLE my_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

Что это означает?

• Искать концы строк в виде символов '\r\n'
• Разбивать строки на поля по символам запятой (,).
• Ожидать, что поля могут быть заключены в символы цитирования.
• Интерпретировать встречающиеся символы табуляции, новой строки или '\', предваренные '\', как литералы, являющиеся частью значения поля.

Конструкция LOAD DATA INFILE

Все, что мы описали выше, это знаменитая конструкция LOAD DATA INFILE.

Более подробно вы можете прочитать в официально руководстве и в сообществе php.su.

Ошибка Access denied for user

Если у вас вдруг возникла ошибка Access denied for user, то вам нужно просто добавить атрибут LOCAL перед INFILE. Например:

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE my_table

Загрузить данные в таблицу из файла для конкретных полей

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE t1 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
(@col1,@col2,@col3,@col4) set name=@col4,id=@col2;

Здесь мы указываем имя файла (data.csv), с которого будет производится загрузка данных. В конструкции (@col1,@col2,@col3,@col4) мы нумеруем все столбцы файла для дальнейшего их использования. А далее при помощи знакомого нам метода set устанавливаем значения этих столбцов для конкретных полей таблицы.

LOAD DATA INFILE вместе с JOIN

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

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE table_1 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (@col1,@col2,@col3,@col4) set user_id=@col1, username=(select username from users where user_id = @col1);

The used command is not allowed with this MySQL version

Если возникла подобная ошибка, то при подключении к MySQL, нужно указать флаг --local-infile. Например:

mysql -uroot --local-infile database

LOAD DATA INFILE игнорировать первую строку

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

Например, ваш файл (data.csv):

id title value

3 Paris 100

4 Lodon 67

5 Moscow 341

Команда для загрузки файла в базу данных MySQL будет выглядеть следующим образом:

LOAD DATA INFILE '/home/user1/data.csv' INTO TABLE my_table IGNORE 1 LINES;