Jeśli masz dane w pliku tekstowym, możesz je łatwo załadować do jednej lub więcej tabel w bazie danych.
W bazie danych MySQL (lub MariaDB), używając polecenia „load data infile”, możesz załadować dane z pliku tekstowego do tabel.
Polecenie load data infile zapewnia kilka elastycznych opcji ładowania różnych formatów danych z pliku tekstowego do tabel.
W tym poradniku omówione zostaną następujące przykłady ładowania danych:
- Podstawowy przykład wczytywania danych z pliku tekstowego
- Wczytaj dane używając opcji „Fields terminated by”
- Wczytaj dane używając opcji „Enclosed by”
- Use Escape Character in Text File Data
- Wczytaj dane używając opcji „Lines terminated by”
- Ignore Line Prefix in Upload File using „Starting By” Option
- Ignore Header Line from the Upload File
- Upload Only Specific Columns (and Ignore Others) from Upload File
- Use Variable during Upload with „Set” Option
- Write Shell Script to Load data from Text File
Basic Example to Load Data From Text File
W poniższym przykładzie, plik employee1.txt posiada wartości pól oddzielone tabulatorami.
# cat employee1.txt 100 Thomas Sales 5000200 Jason Technology 5500300 Mayla Technology 7000400 Nisha Marketing 9500500 Randy Technology 6000
Domyślnie, polecenie load data infile używa TAB jako domyślnego separatora pól.
Najpierw przejdź do bazy danych, do której chcesz załadować plik tekstowy. W tym przykładzie, załadujemy powyższy plik employee1.txt do tabeli employee znajdującej się w bazie mysql thegeekstuff.
USE thegeekstuff;
Następujące polecenie MySQL załaduje rekordy z powyższego pliku employee1.txt do tabeli employee, jak pokazano poniżej. Polecenie to nie używa żadnych dodatkowych opcji.
LOAD DATA INFILE 'employee1.txt' INTO TABLE employee;
Uwaga: W powyższym przykładzie, polecenie zakłada, że plik employee1.txt znajduje się w katalogu bazy danych. Na przykład, jeśli wykonujesz powyższe polecenie w bazie danychgeekstuff, to umieść plik pod: /var/lib/mysql/thegeekstuff/
Następujące dane będą danymi wyjściowymi powyższego polecenia.
Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
W powyższym:
- Pierwsza linia „Query OK” mówi, że zapytanie zostało wykonane bez żadnego błędu. Mówi również, że łącznie 5 wierszy zostało załadowanych do tabeli. Wyświetla to również czas potrzebny na przesłanie danych z pliku tekstowego do tabeli w sekundach.
- Druga linia wyświetla całkowitą liczbę przesłanych wierszy, ile z nich zostało pominiętych oraz ile rekordów wyświetliło ostrzeżenia podczas przesyłania.
Po załadowaniu danych, to co zobaczymy w tabeli pracowników wygląda następująco.
Uwaga: Jeśli chcesz wykonać kopię zapasową i przywrócić całą bazę danych MySQL, użyj polecenia mysqldump.
Upload danych przy użyciu opcji „Fields terminated by” Option
W poniższym przykładzie, w pliku wejściowym pracownik2.txt, wartości pól są oddzielone przecinkami.
Aby załadować powyższe rekordy do tabeli pracownik, użyj następującego polecenia.
Podczas wysyłania, używając opcji „FIELDS TERMINATED BY”, możesz określić delimiter pól przecinkiem, jak pokazano poniżej.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',';
Ponownie, użyj tej opcji tylko wtedy, gdy wartości pól są oddzielone czymkolwiek innym niż TAB. Jeśli pola są rozdzielone dwukropkiem, użyjesz następującej opcji w powyższym poleceniu:
FIELDS TERMINATED BY ':';
Jeśli jesteś nowy w MySQL przeczytaj to: MySQL Tutorial: Instalacja, tworzenie bazy danych i tabeli, wstawianie i wybieranie rekordów
Poniżej podano kilka podstawowych błędów, które mogą wystąpić podczas wysyłania danych do MySQL
Błąd 1: Jeśli plik tekstowy nie znajduje się w odpowiednim katalogu, możesz otrzymać następujący komunikat „ERROR 13 (HY000) Can’t get stat of (Errcode: 2)”.
Możesz również podać pełną ścieżkę do pliku w poleceniu load data infile jak pokazano poniżej. Jeśli to zrobisz, upewnij się, że plik może być dostępny dla mysql. Jeśli nie, zmień odpowiednio własność na mysql. Jeśli nie, otrzymasz komunikat o błędzie load data infile permission denied.
MariaDB > LOAD DATA INFILE '/data/employee2.txt' INTO TABLE employee;
Błąd 2: Jeśli nie określisz poprawnych pól zakończonych przez, zobaczysz pewne problemy podczas wysyłania danych. W tym przykładzie, tylko pierwsze pole „id” zostało załadowane. Wartość wszystkich innych pól jest NULL. Dzieje się tak dlatego, że poniższe polecenie nie określa pola zakończonego opcją, ponieważ plik wejściowy ma przecinek jako separator pól.
Upload Data Using „Enclosed by” Option
W poniższym przykładzie, wejściowy plik tekstowy ma wartości pól tekstowych zamknięte w cudzysłowach, tj. wartości name i department mają podwójne cudzysłowy wokół nich.
W tym przypadku, użyj opcji „enclosed by” jak pokazano poniżej.
LOAD DATA INFILE 'employee3.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Powyższe polecenie załaduje rekordy poprawnie, jak pokazano poniżej za pomocą polecenia mysql select:
Pamiętaj, że podczas łączenia pól zakończonych przez i pól zamkniętych przez, nie musisz używać słowa kluczowego „FIELDS” dwa razy, jak pokazano poniżej, co spowoduje wyświetlenie następującego komunikatu o błędzie:
FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"';
Powyżej zostanie wyświetlony następujący błąd „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
Używanie znaku ucieczki w danych pliku tekstowego
Powiedzmy, że masz przecinek w wartości danego pola.
Na przykład w poniższym przykładzie nazwa 2. pola ma wartość w tym formacie: „firstname, lastname”.
Jeśli załadujesz powyższy plik za pomocą poniższego polecenia, zobaczysz, że wyświetli on „10 warnings”
Rekordy również nie są ładowane poprawnie, ponieważ w wartości jednego z pól znajduje się przecinek.
Poprawny plik: Aby rozwiązać powyższy problem, użyj ukośnika wstecznego (\) przed przecinkiem w wartości pola name, jak pokazano poniżej.
Następujący plik zadziała tym razem bez żadnego błędu, ponieważ mamy \u0026apos; jako znak ucieczki.
Możesz również użyć innego znaku ucieczki, jak pokazano poniżej. W tym przykładzie używamy ^ jako znaku ucieczki zamiast defualtu \u0026apos;
W tym przypadku użyj opcji „ESCAPED BY”, jak pokazano poniżej.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\^'
Proszę pamiętać, że niektóre znaki nie mogą być używane jako znaki ucieczki. Na przykład, jeśli użyjesz % jako znaku ucieczki, otrzymasz następujący komunikat o błędzie.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\%'ERROR 1083 (42000): Field separator argument is not what is expected; check the manual
Upload Data Using „Lines terminated by” Option
Zamiast mieć wszystkie rekordy w osobnej linii, możesz mieć je również w tej samej linii.
W poniższym przykładzie, każdy rekord jest oddzielony symbolem |.
Aby załadować powyższy plik, użyj opcji lines terminated by, jak pokazano poniżej.
LOAD DATA INFILE 'employee5.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '|';
Powyższe polecenie załaduje rekordy z pliku employee5.txt, jak pokazano poniżej.
Następujące punkty, o których należy pamiętać:
- Jeśli plik wejściowy pochodzi z komputera z systemem Windows, możesz użyć tego: LINES TERMINATED BY '
- Jeśli używasz pliku CSV do załadowania danych do tabeli, 1) LINES TERMINATED BY '\r' 2) LINES TERMINATED BY '\n'
Ignore Line Prefix in Upload File Using „Starting By” Option
Możesz również dodać prefiks do rekordów w pliku tekstowym, który może zostać zignorowany podczas wysyłania.
Na przykład, w poniższym pliku employee6.txt, dla 1, 2 i 5 rekordu, mamy „Data:” na początku linii. Aby zignorować prefiks linii i przesłać tylko te rekordy, (na przykład: „Dane:” w powyższym pliku), użyj opcji „linie zaczynające się od”, jak pokazano poniżej.
LOAD DATA INFILE 'employee6.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES STARTING BY 'Data:';
Poniżej znajduje się wynik działania powyższego polecenia:
Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
Jak widać poniżej, powyższe polecenie przesłało tylko rekordy, które zaczynały się od prefiksu „Data:”. Jest to pomocne w selektywnym wgrywaniu tylko rekordów, które mają określony prefiks.
Ignoruj nagłówek z pliku wgrywanego
W poniższym pliku tekstowym, pierwsza linia jest linią nagłówka, która zawiera nazwy kolumn.
Podczas wgrywania chcemy zignorować 1. nagłówek z pliku employee7.txt. W tym celu użyj opcji IGNORE 1 lines jak pokazano poniżej.
LOAD DATA INFILE 'employee7.txt' INTO TABLE employee FIELDS TERMINATED BY ',' IGNORE 1 LINES;
Jak widać na poniższym wyjściu, mimo że plik wejściowy ma 6 linii, zignorował 1 linię (która jest linią nagłówka) i załadował pozostałe 5 linii.
Upload Only Only Specific Column (and Ignore Others Columns) from Upload File
W poniższym przykładzie, mamy wartości tylko dla trzech pól. Nie mamy kolumny dział w tym przykładzie.
# cat employee8.txt 100,Thomas,5000200,Jason,5500300,Mayla,7000400,Nisha,9500500,Randy,6000
Aby przesłać wartości z rekordu wejściowego do konkretnej kolumny w tabeli, określ nazwy kolumn podczas ładowania danych do pliku, jak pokazano poniżej. Ostatnia linia poniższego polecenia zawiera nazwy kolumn, które powinny zostać użyte do załadowania rekordów z pliku wejściowego.
LOAD DATA INFILE 'employee8.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, salary);
Ponieważ nie określiliśmy kolumny „dept” w powyższym poleceniu, zobaczymy, że ta kolumna ma wartość NULL, jak pokazano poniżej.
Ponownie, należy pamiętać, że gdy nie określisz listy kolumn, polecenie będzie oczekiwać, że wszystkie kolumny będą obecne w pliku wejściowym.
Jak również, jeśli nie określisz listy kolumn w ostatniej linii, otrzymasz błąd składni, jak pokazano poniżej.
Użyj zmiennych podczas wysyłania z opcją „Set”
Dla tego przykładu, użyjmy następującego pliku employee2.txt.
W tym przykładzie, chcemy zwiększyć wynagrodzenie o 500 przed wysłaniem go do tabeli. Na przykład, pensja dla Thomasa (który jest 1. rekordem) wynosi 5000. Ale podczas wysyłania chcemy ją zwiększyć o 500 do 5500 i zaktualizować tę zwiększoną wartość w tabeli.
W tym celu użyj polecenia SET i użyj wynagrodzenia jako zmiennej i wykonaj inkrementację, jak pokazano poniżej.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, dept, @salary) SET salary = @salary+500;
Jak widać na poniższym wyjściu, kolumna wynagrodzenia została zwiększona o 500 dla wszystkich rekordów podczas ładowania danych z pliku tekstowego.
Napisz skrypt powłoki aby załadować dane z pliku tekstowego
Czasami możesz chcieć załadować dane z pliku tekstowego automatycznie bez konieczności logowania się do mysql za każdym razem.
Powiedzmy, że chcemy umieścić poniższą komendę wewnątrz skryptu powłoki i wykonać ją automatycznie na bazie danych thegeekstuff.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','
Aby wykonać load z linii poleceń, użyjemy opcji -e w poleceniu mysql i wykonamy je z linuksowej zachęty jak pokazano poniżej.
# mysql -e "LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','" \ -u root -pMySQLPassword thegeekstuff
Albo, możesz umieścić to wewnątrz skryptu powłoki jak pokazano poniżej. W tym przykładzie skrypt powłoki load-data.sh ma powyższe polecenie mysql.
Nadaj uprawnienia execute temu skryptowi load-data.sh i wykonaj go z wiersza poleceń, co spowoduje automatyczne załadowanie danych do tabeli. Możesz również zaplanować to jako cronjob, aby załadować dane z pliku automatycznie do tabeli w zaplanowanym odstępie czasu.
# chmod u+x load-data.sh# ./load-data.sh
Jeśli podobał Ci się ten artykuł, you might also like..
|
|
 |
 |
 |
 |