• Оптимизация
  • Социальные сети
  • Sitemap
  • Drupal
  • Usability
  • Юзабилити
  • B2b
  • Антивирус
  • Семантическое ядро
  • Новости
    • <

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

    1. 1. Базовий приклад для завантаження даних з текстового файлу
    2. 2. Вивантаження даних за допомогою опції "Fields terminated by"
    3. 3. Завантажити дані за допомогою опції "Enclosed by"
    4. 4. Використання екрануючого символу в текстових даних файлу
    5. 5. Вивантаження даних за допомогою опції "Lines terminated by"
    6. 6. Ігнорувати рядки префікса при відправці файлів за допомогою опції "Starting By"
    7. 7. Ігнорувати рядки заголовка при завантаження з файлу
    8. 8. Завантажити тільки певні стовпці (і ігнорувати інші) при завантаження з файлу
    9. 9. Використання змінних під час завантаження з опцією "Set"
    10. 10. Запис Shell Скрипт для завантаження даних з текстового файлу
    Е слі у вас є дані в текстовому файлі, ви можете легко завантажити їх в одну або кілька таблиць в базі даних.

    В базі даних 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 має значення полів, які відділені від вкладки.

    # 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

    У наведеному вище:

    • Перший рядок "Query OK", говорить, що запит був виконаний без будь-яких помилок. Він також говорить, що в цілому 5 рядів були завантажені в таблицю. Це також показує час, необхідний для завантаження даних з текстового файлу в таблицю в секундах.
    • Другий рядок відображає загальну кількість рядків завантажене, скільки було пропущено і скільки записів відображаються з попередженнями під час завантаження.

    Після завантаження даних, наступне, що ми побачимо в таблиці співробітників.

    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.

    2. Вивантаження даних за допомогою опції "Fields terminated by"

    У наступному прикладі, у вхідному файлі 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 not get stat of (Errcode: 2)".

    MariaDB [andreyexbase]> LOAD DATA INFILE 'worker2.txt' INTO TABLE employee; ERROR 13 (HY000): Can not 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 | + ----- + ------ + ------ + -------- +

    3. Завантажити дані за допомогою опції "Enclosed by"

    У наступному прикладі, текстовий файл має значення текстового поля, укладені в подвійні лапки, тобто 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

    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

    5. Вивантаження даних за допомогою опції "Lines terminated by"

    Замість того, щоб всі записи були вказані на окремому рядку, ви також можете вказати їх на тому ж рядку.

    У наступному прикладі, кожен запис відокремлена символом |.

    # 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 | + ----- + -------- + ------------ + -------- +

    Нижче наведено кілька моментів, щоб мати на увазі:

    • Якщо вхідний файл надходить з вікна, то ви можете використовувати цю функцію: 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-й записи, у нас є "дані:" на початку рядка. Ви можете завантажити тільки ці записи, ігноруючи префікс рядка.

    # 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)

    7. Ігнорувати рядки заголовка при завантаження з файлу

    У наступному вхідному текстовому файлі, перший рядок є рядком заголовка, який має назву стовпців.

    # 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 | + ----- + -------- + ------------ + -------- +

    8. Завантажити тільки певні стовпці (і ігнорувати інші) при завантаження з файлу

    У наступному прикладі ми маємо значення тільки для трьох полів. У нас немає стовпчика 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

    9. Використання змінних під час завантаження з опцією "Set"

    Для цього прикладу, давайте використовувати наступний файл 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 | + ----- + -------- + ------------ + -------- +

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

    Іноді ви можете завантажити дані з текстового файлу автоматично, без необхідності входу в 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

    Якщо ви знайшли помилку, будь ласка, виділіть фрагмент тексту і натисніть Ctrl + Enter.

    IRC (Internet Relay Chat)

    Все права защищены © 2013 IRC-сервер — Irc.Online.com.ua