ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
Если у вас есть данные в текстовом файле, вы можете легко загрузить их в одну или несколько таблиц в базе данных.
В базе данных MySQL (или MariaDB), используется команда “load data infile” вы можете загрузить данные из текстового файла в таблицы.
Команда загрузки данных из входного файла обеспечивает несколько гибких вариантов для загрузки различных форматов данных из текстового файла в таблицы.
Следующие примеры загрузки данных рассматриваются в данном руководстве:
В следующем примере файл worker.txt имеет значения полей, которые отделены от вкладки.
# cat worker.txt 100 Andreyex Sales 5000 200 Boris IT 5500 300 Anna IT 7000 400 Anton Marketing 9500 500 Dima IT 6000
По умолчанию команда загрузки файла данных использует TAB в качестве поля по умолчанию как разделитель.
Во-первых, перейдите в базу данных, куда вы хотите загрузить текстовый файл. В этом примере мы будем загружать вышеуказанный файл worker.txt в таблицу сотрудников, расположенной базе данных MySQL под названием andreyexbase.
USE andreyexbase;
Следующая команда MySQL будет загружать записи из выше указанного файла worker.txt в таблицу сотрудников, как показано ниже. Эта команда не использует никаких дополнительных опций.
LOAD DATA INFILE 'worker.txt' INTO TABLE employee;
Примечание: В приведенном выше примере, команда предполагает, что файл worker.txt находится в директории базы данных. Например, если вы выполняете приведенную выше команду в базу данных andreyexbase, а затем поместите файл в: /var/lib/mysql/andreyexbase/
Ниже будет вывод предыдущей команды.
Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
В приведенном выше:
После загрузки данных, следующее, что мы увидим в таблице сотрудников.
MariaDB [andreyexbase]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andreyex | Sales | 5000 | | 200 | Boris | IT | 5500 | | 300 | Anna | IT | 7000 | | 400 | Anton | Marketing | 9500 | | 500 | Dima | IT | 6000 | +-----+--------+------------+--------+
Примечание: Если вы хотите сделать резервную копию MySQL и восстановить всю базу данных MySQL, используйте команду Mysqldump.
В следующем примере, во входном файле worker2.txt, значения полей разделяются запятыми.
# cat worker2.txt 100,Andreyex,Sales,5000 200,Boris,IT,5500 300,Anna,IT,7000 400,Anton,Marketing,9500 500,Dima,IT,6000
Для того, чтобы загрузить вышеуказанные записи в таблицe сотрудников, используйте следующую команду.
Во время загрузки, используя вариант “FIELDS TERMINATED BY”, вы можете указать запятую как разделитель полей, как показано ниже.
LOAD DATA INFILE 'worker2.txt' INTO TABLE employee FIELDS TERMINATED BY ',';
Опять же, этот параметр используется только тогда, когда значения поля отделяются друг от друга ничем, кроме TAB. Если поля разделяет двоеточие, вы будете использовать следующую опцию в приведенной выше команде:
FIELDS TERMINATED BY ':';
Если вы новичок в MySQL прочитать: MySQL Учебник: установка, создание БД и таблицы, вставка и выбора записей
Ниже приведены несколько основных ошибок, которые могут произойти во время загрузки MySQL
Ошибка 1: Если текстовый файл не находится под соответствующим каталоге, вы можете получить следующие сообщение об ошибке “ERROR 13 (HY000) Can’t get stat of (Errcode: 2)”.
MariaDB [andreyexbase]> LOAD DATA INFILE 'worker2.txt' INTO TABLE employee; ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/andreyexbase/worker2.txt' (Errcode: 2)
Кроме того, вы можете указать полный путь к файлу в команде при нагрузки данных, как показано ниже. Если вы сделаете это, убедитесь, что файл можно получить по MySQL. Если нет, то измените владельца на MySQL соответствующим образом. Если нет, то вы получите сообщение ошибки в отказе доступа к файлу.
MariaDB [andreyexbase]> LOAD DATA INFILE '/data/worker2.txt' INTO TABLE employee;
Ошибка 2: Если вы не указали правильные поля разделителя, то вы увидите некоторые проблемы в загрузке. В этом примере только первое поле “ID” было загружено. Значение всех остальных полей являются NULL. Это потому, что следующая команда не определяет поле, заканчивающуюся параметром, так как входной файл имел запятую в качестве разделителя поля.
MariaDB [andreyexbase]> LOAD DATA INFILE 'worker2.txt' INTO TABLE employee; Query OK, 5 rows affected, 20 warnings (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 20 MariaDB [andreyexbase]> select * from employee; +-----+------+------+--------+ | id | name | dept | salary | +-----+------+------+--------+ | 100 | NULL | NULL | NULL | | 200 | NULL | NULL | NULL | | 300 | NULL | NULL | NULL | | 400 | NULL | NULL | NULL | | 500 | NULL | NULL | NULL | +-----+------+------+--------+
В следующем примере, текстовый файл имеет значения текстового поля, заключенные в двойные кавычки, т.е. name и department имеют двойные кавычки вокруг них.
# cat worker3.txt 100,"Andreyex Smith","Sales & Marketing",5000 200,"Boris Bourne","IT",5500 300,"Anna Jones","IT",7000 400,"Anton Patel","Sales & Marketing",9500 500,"Dima Lee","IT",6000
В этом случае используйте вариант “enclosed by”, как показано ниже.
LOAD DATA INFILE 'worker3.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Приведенная выше команда будет загружать записи должным образом, как показано ниже с помощью команды SELECT в mysql:
MariaDB [andreyexbase]> select * from employee; +-----+--------------+-------------------+--------+ | id | name | dept | salary | +-----+--------------+-------------------+--------+ | 100 | Andreyex Smith | Sales & Marketing | 5000 | | 200 | Boris Bourne | IT | 5500 | | 300 | Anna Jones | IT | 7000 | | 400 | Anton Patel | Sales & Marketing | 9500 | | 500 | Dima Lee | IT | 6000 | +-----+--------------+-------------------+--------+
Обратите внимание, на то что, когда вы объединяете поля с разрывом и поля, заключенные, вы не должны использовать ключевое слово “FIELDS” два раза, как показано ниже, который будет отображаться следующее сообщение об ошибке:
FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"';
Выше появится следующая ошибка “ERROR 1064 (42000)“:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS ENCLOSED BY '"'' at line 4
Допустим, у вас запятая в значении той или иной области.
Например, в следующем примере, имя 2-го поля имеет значение в следующем формате: “Firstname, Lastname“.
# cat worker4.txt 100,Andreyex, Smith,Sales,5000 200,Boris, Bourne,IT,5500 300,Anna, Jones,IT,7000 400,Anton, Patel,Marketing,9500 500,Dima, Lee,IT,6000
Если вы загрузите вышеуказанный файл с помощью следующей команды, вы увидите, что он будет отображать “10 предупреждений”
MariaDB [andreyexbase]> LOAD DATA INFILE 'worker4.txt'
-> INTO TABLE employee
-> FIELDS TERMINATED BY ',';
Query OK, 5 rows affected, 10 warnings (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 10
Записи также не загружается должным образом, потому что есть запятая в значении одного из полей.
MariaDB [andreyexbase]> select * from employee; +-----+--------+---------+--------+ | id | name | dept | salary | +-----+--------+---------+--------+ | 100 | Andreyex | Smith | 0 | | 200 | Boris | Bourne | 0 | | 300 | Anna | Jones | 0 | | 400 | Anton | Patel | 0 | | 500 | Dima | Lee | 0 | +-----+--------+---------+--------+
Правильный файл: Для того, чтобы решить данную проблему, используйте обратную косую черту (\) перед запятой в значении имени поля, как показано ниже.
# cat worker4.txt 100,Andreyex\, Smith,Sales,5000 200,Boris\, Bourne,IT,5500 300,Anna\, Jones,IT,7000 400,Anton\, Patel,Marketing,9500 500,Dima\, Lee,IT,6000
Ниже будет работать на этот раз без каких-либо ошибок, так как мы указали \ в качестве экранирующего символа.
MariaDB [andreyexbase]> LOAD DATA INFILE 'worker4.txt'
-> INTO TABLE employee
-> FIELDS TERMINATED BY ',';
MariaDB [andreyexbase]> select * from employee;
+-----+---------------+------------+--------+
| id | name | dept | salary |
+-----+---------------+------------+--------+
| 100 | Andreyex, Smith | Sales | 5000 |
| 200 | Boris, Bourne | IT | 5500 |
| 300 | Anna, Jones | IT | 7000 |
| 400 | Anton, Patel | Marketing | 9500 |
| 500 | Dima, Lee | IT | 6000 |
+-----+---------------+------------+--------+
Вы можете также использовать экранирующий символ, как показано ниже. В этом примере мы используем ^ вместо стандартного \.
# cat worker41.txt 100,Andreyex^, Smith,Sales,5000 200,Boris^, Bourne,IT,5500 300,Anna^, Jones,IT,7000 400,Anton^, Patel,Marketing,9500 500,Dima^, Lee,IT,6000
В этом случае используйте вариант “ESCAPED BY“, как показано ниже.
LOAD DATA INFILE 'worker41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\^'
Обратите внимание, что некоторые символы не могут быть использованы в качестве экранирующего символа. Например, если вы используете % в качестве экранирующего символа, вы получите следующее сообщение об ошибке.
LOAD DATA INFILE 'worker41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\%' ERROR 1083 (42000): Field separator argument is not what is expected; check the manual
Вместо того, чтобы все записи были указанны на отдельной строке, вы также можете указать их на той же строке.
В следующем примере, каждая запись отделена символом |.
# cat worker5.txt 100,Andreyex,Sales,5000|200,Boris,IT,5500|300,Anna,IT,7000|400,Anton,Marketing,9500|500,Dima,IT,6000
Чтобы загрузить вышеуказанный файл, используйте вариант, как показано ниже.
LOAD DATA INFILE 'worker5.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '|';
Приведенная выше команда будет загружать записи из worker5.txt, как показано ниже.
MariaDB [andreyexbase]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andreyex | Sales | 5000 | | 200 | Boris | IT | 5500 | | 300 | Anna | IT | 7000 | | 400 | Anton | Marketing | 9500 | | 500 | Dima | IT | 6000 | +-----+--------+------------+--------+
Ниже приведены несколько моментов, чтобы иметь в виду:
Вы также можете иметь некоторый префикс к записям в текстовом файле ввода, который может быть проигнорирован во время загрузки.
Например, в следующем файле worker6.txt, для 1-го, 2-го и 5-й записи, у нас есть “данные:” в начале строки. Вы можете загрузить только эти записи, игнорируя префикс строки.
# cat worker6.txt Data:100,Andreyex,Sales,5000 Data:200,Boris,IT,5500 300,Anna,IT,7000 400,Anton,Marketing,9500 Data:500,Dima,IT,6000
Чтобы игнорировать префикс строки и загружать эти записи, (например: “Data:” в приведенном выше файле), используйте опцию “lines starting by”, как показано ниже.
LOAD DATA INFILE 'worker6.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES STARTING BY 'Data:';
Ниже приводится выход указанной команды:
Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
Как вы видите ниже, выше команда загрузила только записи, созданные с приставкой “Data:“. Это полезно, чтобы выборочно загружать только те записи, которые имеет определенный префикс.
MariaDB [andreyexbase]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andreyex | Sales | 5000 | | 200 | Boris | IT | 5500 | | 500 | Dima | IT | 6000 | +-----+--------+------------+--------+ 3 rows in set (0.00 sec)
В следующем входном текстовом файле, первая строка является строкой заголовка, который имеет название столбцов.
# cat worker7.txt empid,name,department,salary 100,Andreyex,Sales,5000 200,Boris,IT,5500 300,Anna,IT,7000 400,Anton,Marketing,9500 500,Dima,IT,6000
Во время загрузки, мы хотим, игнорировать 1-й строку заголовка из файла worker7.txt. Для этого используйте опцию IGNORE 1, как показано ниже.
LOAD DATA INFILE 'worker7.txt' INTO TABLE employee FIELDS TERMINATED BY ',' IGNORE 1 LINES;
Как видно из следующих выходных данных, даже если входной файл имеет 6 строк, он игнорирует 1-ю линию (которая является строка заголовка) и загрузит оставшиеся 5 строк.
Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 MariaDB [andreyexbase]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andreyex | Sales | 5000 | | 200 | Boris | IT | 5500 | | 300 | Anna | IT | 7000 | | 400 | Anton | Marketing | 9500 | | 500 | Dima | IT | 6000 | +-----+--------+------------+--------+
В следующем примере мы имеем значения только для трех полей. У нас нет столбца department в этом примере файла.
# cat worker8.txt 100,Andreyex,5000 200,Boris,5500 300,Anna,7000 400,Anton,9500 500,Dima,6000
Для того, чтобы загрузить значения из входной записи на определенный столбец в таблице, укажите имена столбцов во время загрузки данных INFILE, как показано ниже. В последней строке в следующей команде есть имена столбцов, которые должны использоваться для загрузки записей из входного текстового файла.
LOAD DATA INFILE 'worker8.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, salary);
Так как мы не указываем колонку “DEPT” в приведенной выше команде, мы увидим, что этот столбец NULL, как показано ниже.
MariaDB [andreyexbase]> select * from employee; +-----+--------+------+--------+ | id | name | dept | salary | +-----+--------+------+--------+ | 100 | Andreyex | NULL | 5000 | | 200 | Boris | NULL | 5500 | | 300 | Anna | NULL | 7000 | | 400 | Anton | NULL | 9500 | | 500 | Dima | NULL | 6000 | +-----+--------+------+--------+
Опять же, имейте в виду, что, когда вы не указываете список столбцов, то команда будет ожидать, что все столбцы должны присутствовать в файле ввода.
Кроме того, если вы не указали список столбцов в последней строке, вы получите ошибку синтаксиса, как показано ниже.
MariaDB [andreyexbase]> LOAD DATA INFILE 'worker7.txt'
-> INTO TABLE employee (id, name, salary)
-> FIELDS TERMINATED BY ',';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS TERMINATED BY ','' at line 3
Для этого примера, давайте использовать следующий файл worker2.txt.
# cat worker2.txt 100,Andreyex,Sales,5000 200,Boris,IT,5500 300,Anna,IT,7000 400,Anton,Marketing,9500 500,Dima,IT,6000
В этом примере, мы хотим, увеличить зарплату на 500, прежде чем загружать его в таблицу. Например, заработная плата для Andreyex (является первой записью) 5000. Но, во время загрузки мы хотим увеличить на 500 до 5500, и обновлять это измененное значение в таблице.
Для этого используйте команду SET и использовать зарплату в качестве переменной и сделать приращение, как показано ниже.
LOAD DATA INFILE 'worker2.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, dept, @salary) SET salary = @salary+500;
Как видно из следующего вывода, столбец зарплата увеличивается на 500 для всех записей во время загрузки данных из текстового файла.
MariaDB [andreyexbase]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andreyex | Sales | 5500 | | 200 | Boris | IT | 6000 | | 300 | Anna | IT | 7500 | | 400 | Anton | Marketing | 10000 | | 500 | Dima | IT | 6500 | +-----+--------+------------+--------+
Иногда вы можете загрузить данные из текстового файла автоматически, без необходимости входа в MySQL каждый раз.
Скажем, мы хотим поставить следующую команду внутри сценария оболочки и выполнить это автоматически для базе данных andreyexbase.
LOAD DATA INFILE 'worker2.txt' INTO TABLE employee FIELDS TERMINATED BY ','
Чтобы выполнить загрузку из командной строки, вы можете использовать опцию -e в команде mysql и выполнить его из строки Linux, как показано ниже.
# mysql -e "LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','" \ -u root -pMySQLPassword andreyexbase
Или, вы можете положить, внутри сценария оболочки, как показано ниже. В этом примере сценарий оболочки loads-data.sh имеет указанную выше команду MySQL.
# cat loads-data.sh
mysql -e "\
LOAD DATA INFILE 'worker2.txt'\
INTO TABLE employee \
FIELDS TERMINATED BY ','\
" \
-u root -pMySQLPwd4MDN! test
Дайте разрешение на выполнение на этот скрипт loads-data.sh, и выполните его из командной строки, которая будет загружать данные автоматически в таблицу. Можно также запланировать это как cronjob для загрузки данных из файла автоматически в таблицу на запланированный интервал.
# chmod u+x loads-data.sh # ./loads-data.sh
Норм шпаргалка
А что делать если ошибка 2068?