MySQL ma funkcję eksportowania tabeli do pliku CSV. Format pliku CSV to wartość oddzielona przecinkami, której używamy do wymiany danych między różnymi aplikacjami, takimi jak Microsoft Excel, Goole Docs i Open Office. Przydatne jest posiadanie danych MySQL w formacie pliku CSV, który pozwala nam je analizować i formatować w dowolny sposób. Jest to plik tekstowy, który pomaga nam bardzo łatwo eksportować dane.
MySQL'a zapewnia łatwy sposób eksportowania dowolnej tabeli do plików CSV znajdujących się na serwerze bazy danych. Przed eksportem danych MySQL musimy upewnić się, że:
- Proces serwera MySQL ma dostęp do odczytu/zapisu do określonego (docelowego) folderu, który zawiera plik CSV.
- Podany plik CSV nie powinien istnieć w systemie.
Aby wyeksportować tabelę do pliku CSV, użyjemy metody WYBIERZ DO....PLIKU OUT oświadczenie. To stwierdzenie jest komplementem dla Wczytaj dane polecenie, które służy do zapisu danych z tabeli, a następnie wyeksportowania ich do określonego formatu pliku na hoście serwera. Ma to na celu zapewnienie, że mamy uprawnienia do korzystania z tej składni.
SELECT column_lists INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Możemy również użyć tej składni z instrukcją wartości, aby wyeksportować dane bezpośrednio do pliku. Poniższe stwierdzenie wyjaśnia to jaśniej:
SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1 INTO OUTFILE '/tmp/selected_values.txt';
Jeśli chcemy eksportować wszystkie kolumny tabeli , użyjemy poniższej składni. Dzięki tej instrukcji kolejność i liczba wierszy będą kontrolowane przez ZAMÓW PRZEZ I LIMIT klauzula.
przejście przezroczystości CSS
TABLE table_name ORDER BY lname LIMIT 1000 INTO OUTFILE '/path/filename.txt' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';;
Z góry,
LINIE ZAKOŃCZONE PRZEZ „,” : Służy do wskazania wierszy pliku zakończonych operatorem przecinkiem. Każda linia zawiera dane każdej kolumny w pliku.
POLA OBJĘTE PRZEZ ''' : Służy do określenia pola pliku ujętego w podwójny cudzysłów. Zapobiega wartościom zawierającym separatory przecinkami. Jeśli wartości zawarte są w podwójnym cudzysłowie, przecinek nie jest rozpoznawany jako separator.
Lokalizacja przechowywania wyeksportowanego pliku
Lokalizacja przechowywania każdego eksportowanego pliku w MySQL jest przechowywana w zmiennej domyślnej bezpieczny_plik_priv . Możemy wykonać poniższe polecenie, aby uzyskać domyślną ścieżkę eksportowanego pliku.
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
Po wykonaniu da wynik w następujący sposób, gdzie możemy zobaczyć tę ścieżkę: C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ jako domyślną lokalizację pliku. Ta ścieżka będzie używana w momencie uruchomienia polecenia eksportu.
cykl życia SDLC
Jeśli chcemy zmienić domyślną lokalizację eksportu pliku CSV określoną w pliku bezpieczny_plik_priv zmienną, musimy edytować plik moje.ini plik konfiguracyjny. Na platformie Windows plik ten znajduje się w następującej ścieżce: C:ProgramDataMySQLMySQL Server X.Y .
Jeśli chcemy wyeksportować dane MySQL, najpierw musimy utworzyć plik Baza danych z co najmniej jednym tabela . Użyjemy tej tabeli jako przykładu.
Możemy stworzyć baza danych i tabela wykonując poniższy kod w edytorach, których używamy:
CREATE DATABASE testdb; USE testdb; CREATE TABLE employee_detail ( ID int NOT NULL AUTO_INCREMENT, Name varchar(45) DEFAULT NULL, Email varchar(45) DEFAULT NULL, Phone varchar(15) DEFAULT NULL, City varchar(25) DEFAULT NULL, PRIMARY KEY (ID), UNIQUE KEY unique_email (Email), UNIQUE KEY index_name_phone (Name,Phone) ) INSERT INTO employee_detail ( Id, Name, Email, Phone, City) VALUES (1, 'Peter', '[email protected]', '49562959223', 'Texas'), (2, 'Suzi', '[email protected]', '70679834522', 'California'), (3, 'Joseph', '[email protected]', '09896765374', 'Alaska'), (4, 'Alex', '[email protected]', '97335737548', 'Los Angeles'), (5, 'Mark', '[email protected]', '78765645643', 'Washington'), (6, 'Stephen', '[email protected]', '986345793248', 'New York');
Jeżeli wykonamy WYBIERAĆ instrukcji, zobaczymy następujące dane wyjściowe:
Eksportuj dane MySQL w formacie CSV za pomocą instrukcji SELECT INTO ... OUTFILE
Aby wyeksportować dane tabeli do pliku CSV, musimy wykonać zapytanie w następujący sposób:
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Otrzymamy następujące dane wyjściowe, w których widać, że dotyczy to sześciu wierszy. Dzieje się tak dlatego, że określona tabela zawiera tylko sześć wierszy.
Jeśli ponownie wykonamy tę samą instrukcję, MySQL wyświetli komunikat o błędzie, który można zobaczyć w poniższych wynikach:
Komunikat o błędzie informuje nas, że podana nazwa pliku już istnieje w określonej lokalizacji. Zatem jeśli wyeksportujemy nowy plik CSV o tej samej nazwie i lokalizacji, nie będzie można go utworzyć. Możemy rozwiązać ten problem, usuwając istniejący plik w określonej lokalizacji lub zmieniając nazwę pliku, aby utworzyć go w tym samym miejscu.
Możemy zweryfikować plik CSV utworzony we wskazanej lokalizacji lub nie, przechodząc do podanej ścieżki w następujący sposób:
Kiedy otworzymy ten plik, będzie on wyglądał jak na obrazku poniżej:
Na obrazku widzimy, że pola numeryczne są ujęte w cudzysłów. Możemy zmienić ten styl dodając Klauzula OPCJONALNIE przed ENCLOSED BY :
kiedy pojawił się system Windows 7
SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''' LINES TERMINATED BY ' ';
Eksportowanie danych z nagłówkiem kolumny
Czasami chcemy wyeksportować dane wraz z nagłówkami kolumn, aby plik był wygodny. Wyeksportowany plik jest bardziej zrozumiały, jeśli pierwsza linia pliku CSV zawiera nagłówki kolumn. Możemy dodać nagłówki kolumn za pomocą UNIA WSZYSTKICH oświadczenie w następujący sposób:
jak wywołać metodę w Javie
SELECT 'Id', 'Name', 'Email', 'Phone', 'City' UNION ALL SELECT Id, Name, Email, Phone, City FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS TERMINATED BY ';' ENCLOSED BY ''' ESCAPED BY ''' LINES TERMINATED BY ' ';
W tym zapytaniu widzimy, że dodaliśmy nagłówek do każdej nazwy kolumny. Możemy zweryfikować dane wyjściowe, przechodząc do określonego adresu URL, gdzie pierwsza linia zawiera nagłówek każdej kolumny:
Eksportuj tabelę MySQL w formacie CSV
MySQL OUTFILE pozwala nam również wyeksportować tabelę bez podawania nazwy kolumny. Możemy użyć poniższej składni, aby wyeksportować tabelę w formacie pliku CSV:
TABLE employee_detail ORDER BY City LIMIT 1000 INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Jeśli wykonamy powyższą instrukcję, nasze narzędzie wiersza poleceń wygeneruje następujący wynik. Oznacza to, że określona tabela zawiera sześć wierszy, które zostały wyeksportowane pracownik_kopia zapasowa.csv plik.
Obsługa wartości null
Czasami pola w zestawie wyników mają wartości NULL, wówczas plik docelowy (typ pliku eksportowanego) będzie zawierał N zamiast NULL. Możemy rozwiązać ten problem, zastępując wartość NULL przez „nie dotyczy (nie dotyczy)” używając JEŻELI NULL funkcjonować. Poniższe stwierdzenie wyjaśnia to jaśniej:
SELECT Name, Email, Phone, IFNULL(Phone, 'N/A') FROM employee_detail INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' FIELDS ENCLOSED BY ''' TERMINATED BY ';' ESCAPED BY ''' LINES TERMINATED BY ' ';
Eksportuj tabelę do formatu CSV za pomocą MySQL Workbench
Jeśli nie chcemy uzyskać dostępu do serwera bazy danych w celu wyeksportowania pliku CSV, MySQL udostępnia inny sposób, tj. Za pomocą MySQL Workbench. Workbench to narzędzie GUI umożliwiające pracę z bazą danych MySQL bez użycia narzędzia wiersza poleceń. Pozwala nam wyeksportować zestaw wyników wyciągu do formatu CSV w naszym lokalnym systemie. Aby to zrobić, musimy wykonać poniższe kroki:
- Uruchom instrukcję/zapytanie i uzyskaj zestaw wyników.
- Następnie w panelu wyników kliknij „eksportuj zestaw rekordów do pliku zewnętrznego” opcja. Zestaw rekordów jest używany jako zestaw wyników.
- Na koniec zostanie wyświetlone nowe okno dialogowe. Tutaj musimy podać nazwę pliku i jego format. Po uzupełnieniu szczegółów kliknij przycisk Ratować przycisk. Poniższy obraz wyjaśnia to jaśniej:
Teraz możemy zweryfikować wynik, przechodząc do określonej ścieżki.