Навоз недалеко от того, кого слабит (Пословица).

10 Примеров входной загрузки данных из текстового файла в таблицы MySQL

FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...

Статья опубликована: 4 ноября 2016

10 Примеров входной загрузки данных из текстового файла в таблицы MySQL
Если у вас есть данные в текстовом файле, вы можете легко загрузить их в одну или несколько таблиц в базе данных.

В базе данных MySQL (или MariaDB), используется команда “load data infile” вы можете загрузить данные из текстового файла в таблицы.

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

Следующие примеры загрузки данных рассматриваются в данном руководстве:

  1. Базовый пример для загрузки данных из текстового файла
  2. Загрузка данных с помощью опции “Fields terminated by”
  3. Загрузить данные с помощью опции “Enclosed by”
  4. Использование экранирующего символа в текстовых данных файла
  5. Загрузить данных с помощью опции “Lines terminated by”
  6. Игнорировать строки префикса при отправке файлов с помощью опции “Starting By”
  7. Игнорировать строки заголовка при загрузки файла
  8. Загрузить только определенные столбцы (и игнорировать другие) при загрузки из файла
  9. Использование переменной во время загрузки с опцией “Set”
  10. Написать Shell Скрипт для загрузки данных из текстового файла

1. Базовый пример для загрузки данных из текстового файла

В следующем примере файл worker.txt имеет значения полей, которые отделены от вкладки.

 

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

Во-первых, перейдите в базу данных, куда вы хотите загрузить текстовый файл. В этом примере мы будем загружать вышеуказанный файл worker.txt в таблицу сотрудников, расположенной базе данных MySQL под названием andreyexbase.

 

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

 

Примечание: В приведенном выше примере, команда предполагает, что файл worker.txt находится в директории базы данных. Например, если вы выполняете приведенную выше команду в базу данных andreyexbase, а затем поместите файл в: /var/lib/mysql/andreyexbase/

Ниже будет вывод предыдущей команды.

 

В приведенном выше:

  • Первая строка “Query OK”, говорит, что запрос был выполнен без каких-либо ошибок. Он также говорит, что в общей сложности 5 рядов были загружены в таблицу. Это также показывает время, необходимое для загрузки данных из текстового файла в таблицу в секундах.
  • Вторая строка отображает общее количество строк загружено, сколько было пропущено и сколько записей отображаются с предупреждениями во время загрузки.

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

 

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

2. Выгрузка данных с помощью опции “Fields terminated by”

В следующем примере, во входном файле worker2.txt, значения полей разделяются запятыми.

 

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

Во время загрузки, используя вариант “FIELDS TERMINATED BY”, вы можете указать запятую как разделитель полей, как показано ниже.

 

Опять же, этот параметр используется только тогда, когда значения поля отделяются друг от друга ничем, кроме TAB. Если поля разделяет двоеточие, вы будете использовать следующую опцию в приведенной выше команде:

 

Если вы новичок в MySQL прочитать: MySQL Учебник: установка, создание БД и таблицы, вставка и выбора записей

Ниже приведены несколько основных ошибок, которые могут произойти во время загрузки MySQL

Ошибка 1: Если текстовый файл не находится под соответствующим каталоге, вы можете получить следующие сообщение об ошибке “ERROR 13 (HY000) Can’t get stat of (Errcode: 2)”.

 

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

 

Ошибка 2: Если вы не указали правильные поля разделителя, то вы увидите некоторые проблемы в загрузке. В этом примере только первое поле “ID” было загружено. Значение всех остальных полей являются NULL. Это потому, что следующая команда не определяет поле, заканчивающуюся параметром, так как входной файл имел запятую в качестве разделителя поля.

 

3. Загрузить данные с помощью опции “Enclosed by”

В следующем примере, текстовый файл имеет значения текстового поля, заключенные в двойные кавычки, т.е. name и department имеют двойные кавычки вокруг них.

 

В этом случае используйте вариант “enclosed by”, как показано ниже.

 

Приведенная выше команда будет загружать записи должным образом, как показано ниже с помощью команды SELECT в mysql:

 

Обратите внимание, на то что, когда вы объединяете поля с разрывом и поля, заключенные, вы не должны использовать ключевое слово “FIELDS” два раза, как показано ниже, который будет отображаться следующее сообщение об ошибке:

 

Выше появится следующая ошибка “ERROR 1064 (42000)“:

 

4. Использование экранирующего символа в текстовых данных файла

Допустим, у вас запятая в значении той или иной области.

Например, в следующем примере, имя 2-го поля имеет значение в следующем формате: “Firstname, Lastname“.

 

Если вы загрузите вышеуказанный файл с помощью следующей команды, вы увидите, что он будет отображать “10 предупреждений”

 

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

 

Правильный файл: Для того, чтобы решить данную проблему, используйте обратную косую черту (\) перед запятой в значении имени поля, как показано ниже.

 

Ниже будет работать на этот раз без каких-либо ошибок, так как мы указали \ в качестве экранирующего символа.

 

Вы можете также использовать экранирующий символ, как показано ниже. В этом примере мы используем ^ вместо стандартного \.

 

В этом случае используйте вариант “ESCAPED BY“, как показано ниже.

 

Обратите внимание, что некоторые символы не могут быть использованы в качестве экранирующего символа. Например, если вы используете % в качестве экранирующего символа, вы получите следующее сообщение об ошибке.

 

5. Выгрузка данных с помощью опции “Lines terminated by”

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

В следующем примере, каждая запись отделена символом |.

 

Чтобы загрузить вышеуказанный файл, используйте вариант, как показано ниже.

 

Приведенная выше команда будет загружать записи из worker5.txt, как показано ниже.

 

Ниже приведены несколько моментов, чтобы иметь в виду:

  • Если входной файл поступает из окна, то вы можете использовать эту функцию: LINES TERMINATED BY ‘\r\n’
  • Если вы используете CSV файл для загрузки данных в таблицу, то попробуйте одну из этих: 1) LINES TERMINATED BY ‘\r’  2) LINES TERMINATED BY ‘\r\n’

6. Игнорировать строки префикса при отправке файлов с помощью опции “Starting By”

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

Например, в следующем файле worker6.txt, для 1-го, 2-го и 5-й записи, у нас есть “данные:” в начале строки. Вы можете загрузить только эти записи, игнорируя префикс строки.

 

Чтобы игнорировать префикс строки и загружать эти записи, (например: “Data:” в приведенном выше файле), используйте опцию “lines starting by”, как показано ниже.

 

Ниже приводится выход указанной команды:

 

Как вы видите ниже, выше команда загрузила только записи, созданные с приставкой “Data:“. Это полезно, чтобы выборочно загружать только те записи, которые имеет определенный префикс.

 

7. Игнорировать строки заголовка при загрузки из файла

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

 

Во время загрузки, мы хотим, игнорировать 1-й строку заголовка из файла worker7.txt. Для этого используйте опцию IGNORE 1, как показано ниже.

 

Как видно из следующих выходных данных, даже если входной файл имеет 6 строк, он игнорирует 1-ю линию (которая является строка заголовка) и загрузит оставшиеся 5 строк.

 

8. Загрузить только определенные столбцы (и игнорировать другие) при загрузки из файла

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

 

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

 

Так как мы не указываем колонку “DEPT” в приведенной выше команде, мы увидим, что этот столбец NULL, как показано ниже.

 

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

Кроме того, если вы не указали список столбцов в последней строке, вы получите ошибку синтаксиса, как показано ниже.

 

9. Использование переменных во время загрузки с опцией “Set”

Для этого примера, давайте использовать следующий файл worker2.txt.

 

В этом примере, мы хотим, увеличить зарплату на 500, прежде чем загружать его в таблицу. Например, заработная плата для Andreyex (является первой записью) 5000. Но, во время загрузки мы хотим увеличить на 500 до 5500, и обновлять это измененное значение в таблице.

Для этого используйте команду SET и использовать зарплату в качестве переменной и сделать приращение, как показано ниже.

 

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

 

10. Запись Shell Скрипт для загрузки данных из текстового файла

Иногда вы можете загрузить данные из текстового файла автоматически, без необходимости входа в MySQL каждый раз.

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

 

Чтобы выполнить загрузку из командной строки, вы можете использовать опцию -e в команде mysql и выполнить его из строки Linux, как показано ниже.

 

Или, вы можете положить, внутри сценария оболочки, как показано ниже. В этом примере сценарий оболочки loads-data.sh имеет указанную выше команду MySQL.

 

Дайте разрешение на выполнение на этот скрипт loads-data.sh, и выполните его из командной строки, которая будет загружать данные автоматически в таблицу. Можно также запланировать это как cronjob для загрузки данных из файла автоматически в таблицу на запланированный интервал.

10 Примеров входной загрузки данных из текстового файла в таблицы MySQL


Читайте также

Добавить комментарий

Войти с помощью: 

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

badge
Обратный звонок 1
Отправить
galka

Спасибо! Ваша заявка принята

close
galka

Спасибо! Ваша заявка принята

close