Isalathiso senqaku
Ungayingenisa njani ifayile ye-TXT kwitheyibhile ye -MySQL ? Isifundo sokungenisa iifayile ze-SQL kwisiseko sedatha se-MySQL.
Idatha yokungenisa i-MySQL
Kukho iindlela ezimbini ezilula zokungenisa idatha ethunyelwa ngaphandle yi-MySQL kwi-MySQL.
Thatha ngaphandle idatha usebenzisa LOAD DATA
Ingxelo ye-LOAD DATA INFILE inikwe kwi-MySQL ukufaka idatha.Lo mzekelo ulandelayo uyakufunda ifayile yokulahla.txt kulawulo lwangoku kwaye ufake idatha kwifayile kwitafile yemytbl yesiseko sedatha yangoku.
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
Ukuba igama elingundoqo LOCAL lixeliwe, libonisa ukuba ifayile ifundwa ngendlela kumxhasi womxhasi.Ukuba ayichazwanga, ifayile ifundwa ngendlela kumncedisi.
Ungacacisa ngokucacileyo isihluli sexabiso lekholamu kunye nophawu lokuphela komgca kwingxelo ye-LOAD DATA, kodwa iimpawu ezisisiseko zii -anchors kunye nemigca emitsha.
Isivakalisi se- FIELDS kunye ne LINES amagatya iyafana kuyo yomibini imiyalelo.Omabini amagatya ayazikhethela, kodwa ukuba omabini acacisiwe, igatya le-FIELDS kufuneka livele phambi kwe LINES igatya.
Ukuba umsebenzisi uchaza igatya le-FIELDS, amagatya alo (EKUYEKWE NGAYO, [NGOKUZITHANDELA] IVIKELWE NGU, kwaye UPHUMELELWE NGU) azikhethele, nangona kunjalo, umsebenzisi kufuneka achaze nokuba mnye kubo.
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl -> FIELDS TERMINATED BY ':' -> LINES TERMINATED BY '\r\n';
Ngokungagqibekanga, LOAD DATA ifaka idatha ngokolandelelwano lwemiqolo kwifayile yedatha.Ukuba iikholamu kwifayile yedatha ayihambelani namakholomu kwitheyibhile efakiweyo, kufuneka ucacise ulandelelwano lweekholomu.
Umzekelo, ulandelelwano lwekholam kwifayile yedatha ngu-a,b,c, kodwa ulandelelwano lwekholam kwitheyibhile efakelweyo ithi b,c,a, isintaksi yokungenisa idatha ngolu hlobo lulandelayo:
mysql> LOAD DATA LOCAL INFILE 'dump.txt' -> INTO TABLE mytbl (b, c, a);
Ngenisa idatha usebenzisa i-mysqlimport
Umxhasi we-mysqlimport ubonelela ngojongano lomgca womyalelo kwinkcazo ye-LOAD DATA INFILEQL. Uninzi lweenketho ze-mysqlimport zihambelana ngqo negatya le-LOAD DATA INFILE.
Ukungenisa idatha kwitheyibhile yedatha yemytbl esuka kwifayile dump.txt, lo myalelo ulandelayo unokusetyenziswa:
$ mysqlimport -u root -p --local database_name dump.txt password *****
Umyalelo we-mysqlimport ungakhankanya iinketho zokuseta ifomathi ekhankanyiweyo.
$ mysqlimport -u root -p --local --fields-terminated-by=":" \ --lines-terminated-by="\r\n" database_name dump.txt password *****
Sebenzisa i --columns ukhetho kwingxelo ye-mysqlimport ukuseta ulungelelwaniso lwezintlu:
$ mysqlimport -u root -p --local --columns=b,c,a \ database_name dump.txt password *****
Intshayelelo kwiinketho eziqhelekileyo ze-mysqlimport
| 选项 | Umsebenzi |
|---|---|
| -d okanye --cima | Cima lonke ulwazi kwitheyibhile yedatha phambi kokuba idatha entsha ingeniswe kwitafile yedatha |
| -f okanye -force | i-mysqlimport iyakunyanzela ukuqhubeka nokufaka idatha nokuba idibana nempazamo okanye hayi |
| -i okanye -ungahoyi | i-mysqlimport iyatsiba okanye ayihoyi imigca enesitshixo esifanayo esisodwa, kwaye idatha kwifayile ethathwe ngaphandle ayinanzwa. |
| -l okanye -tshixa-iitafile | Itheyibhile itshixiwe phambi kokuba idatha ifakwe, nto leyo ethintela imibuzo yabasebenzisi kunye nohlaziyo ekubeni luchaphazeleke xa uhlaziya isiseko sedatha. |
| -r okanye -buyisela | Olu khetho luchasene no -i ukhetho, olu khetho luzakubuyisela iirekhodi ngesitshixo esisodwa esisodwa kwitafile. |
| --imimandla-evaliweyo-nge= char | Cacisa into ekufuneka uyifake kwirekhodi yedatha kwifayile yombhalo.Kwiimeko ezininzi, idatha ifakwe kumanqaku amabini okucaphula.Idatha ayivalelwanga ngoonobumba ngokungagqibekanga. |
| --amasimi-apheliswe-nge=char | Ixela i-delimiter phakathi kwamaxabiso edatha nganyeUngasebenzisa olu khetho ukukhankanya umdimi phakathi kwedatha.Idelimiter engagqibekanga ngumlinganiswa wesithuba (Tab) |
| --imigca-ipheliswe-nge=str | Olu khetho lukhankanya umtya okanye uphawu olwahlulahlula idata phakathi kwelayini kwifayile yokubhaliweyo.Ngokungagqibekanga i-mysqlimport isebenzisa umgca omtsha njengomahluli welayini.Unokukhetha ukufaka endaweni yomlinganiswa omnye ngomtya: umgca omtsha okanye ukubuya kwekhareji. |
Iinketho ezisetyenziswa ngokuqhelekileyo zomyalelo we-mysqlimport zi-v ukubonisa uguqulelo (uguqulelo), -p ukwazisa igama eligqithisiweyo, njalo njalo.
Ngethemba, inqaku elithi "Ungazingenisa njani iifayile ze-TXT kwiitafile ze-MySQL? Isifundo sokungenisa iifayile ze-SQL kwiDatabase," esabelwe kwibhlog kaChen Weiliang ( https://www.chenweiliang.com/ ), siya kuba luncedo kuwe.
Zive ukhululekile ukwabelana ngekhonkco leli nqaku: https://www.chenweiliang.com/cwl-503.html