Kalozera wa Nkhani
Kodi mungalowetse bwanji fayilo ya TXT mu tebulo la MySQL ? Maphunziro okhudza kulowetsa mafayilo a SQL mu database ya MySQL.
MySQL import data
Pali njira ziwiri zosavuta zotumizira deta yotumizidwa ndi MySQL mu MySQL.
Lowetsani data pogwiritsa ntchito LOAD DATA
Mawu a LOAD DATA INFILE amaperekedwa mu MySQL kuti muyike deta.Chitsanzo chotsatirachi chiwerenga fayilo ya dump.txt kuchokera m'ndandanda wamakono ndikuyika zomwe zili mufayiloyo mu tebulo la mytbl la database yamakono.
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
Ngati mawu ofunika a LOCAL atchulidwa, akuwonetsa kuti fayilo imawerengedwa ndi njira kuchokera kwa kasitomala.Ngati sichinatchulidwe, fayilo imawerengedwa ndi njira pa seva.
Mungathe kufotokoza momveka bwino cholekanitsa mtengo wa mzere ndi chizindikiro chomaliza mzere mu LOAD DATA statement, koma zizindikiro zokhazikika ndi anchors ndi newlines.
Mawu a mawu a FIELDS ndi LINES ndi ofanana pa malamulo onse awiri.Ndime zonse ziwiri ndizosankha, koma ngati zonse zafotokozedwa, ndime ya FIELDS iyenera kuwonekera pamaso pa ndime ya LINES.
Ngati wogwiritsa ntchito atchula ndime ya FIELDS, ziganizo zake (ZOTHA NDI, [POSAKHUDZA] ZOPHUNZITSIDWA NDI, NDI ZOPHUNZITSIDWA NDI) ndizosankha, komabe, wogwiritsa ntchitoyo ayenera kutchula chimodzi mwa izo.
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl -> FIELDS TERMINATED BY ':' -> LINES TERMINATED BY '\r\n';
Mwachisawawa, LOAD DATA imayika deta motsatira ndondomeko ya zipilala mu fayilo ya deta Ngati mizati mu fayilo ya deta ikusagwirizana ndi mizati yomwe ili pa tebulo lolowetsedwa, muyenera kufotokoza ndondomeko ya mizati.
Mwachitsanzo, dongosolo la magawo mu fayilo ya data ndi a,b,c, koma dongosolo lazagawo patebulo loyikidwa ndi b,c,a, mawu otengera kulowetsa deta ali motere:
mysql> LOAD DATA LOCAL INFILE 'dump.txt' -> INTO TABLE mytbl (b, c, a);
Lowetsani deta pogwiritsa ntchito mysqlimport
Makasitomala a mysqlimport amapereka mawonekedwe a mzere wolamula ku LOAD DATA INFILEQL statement. Zosankha zambiri za mysqlimport zimagwirizana mwachindunji ndi gawo la LOAD DATA INFILE.
Kuti mulowetse deta mu mytbl data table kuchokera pa fayilo dump.txt, lamulo ili lingagwiritsidwe ntchito:
$ mysqlimport -u root -p --local database_name dump.txt password *****
Lamulo la mysqlimport litha kufotokoza zosankha kuti muyike mtundu womwe wasankhidwa.
$ mysqlimport -u root -p --local --fields-terminated-by=":" \ --lines-terminated-by="\r\n" database_name dump.txt password *****
Gwiritsani ntchito --columns njira mu mysqlimport statement kuti muyike dongosolo la mizati:
$ mysqlimport -u root -p --local --columns=b,c,a \ database_name dump.txt password *****
Chidziwitso chazosankha zodziwika bwino za mysqlimport
| 选项 | Ntchito |
|---|---|
| -d kapena --delete | Chotsani zidziwitso zonse mu tebulo la data musanalowetse deta yatsopano ku tebulo la data |
| -f kapena --force | mysqlimport ikakamiza kupitiliza kuyika deta mosasamala kanthu kuti ikukumana ndi vuto kapena ayi |
| -ine kapena -nyalanyaza | mysqlimport imadumpha kapena kunyalanyaza mizere yomwe ili ndi fungulo lapadera lomwelo, ndipo deta yomwe ili mu fayilo yotumizidwa imanyalanyazidwa. |
| -l kapena -lock-tebulo | Gomelo limatsekedwa deta isanalowetsedwe, zomwe zimalepheretsa kuti mafunso a ogwiritsa ntchito ndi zosintha zisakhudzidwe mukamakonza database. |
| -r kapena -kusintha | Njira iyi ndi yosiyana ndi -i kusankha; njirayi idzalowa m'malo mwa makiyi ndi kiyi yapadera yomweyi patebulo. |
| --minda-yotsekedwa-by= char | Tchulani zomwe muyenera kuziyika mu fayilo yolemba.Nthawi zambiri, datayo imatsekeredwa m'magawo awiri.Deta sichimatsekeredwa mu zilembo mwachisawawa. |
| --minda-yothetsedwa-by=char | Imatchula malire pakati pamtengo wa data iliyonse. Mufayilo yokhala ndi nthawi, chotsitsa ndi nthawi.Mutha kugwiritsa ntchito njirayi kuti mufotokozere malire pakati pa data.Delimiter yokhazikika ndi mawonekedwe a tabu (Tab) |
| --mizere-yothetsedwa-ndi=str | Izi zimatchula chingwe kapena zilembo zomwe zimayika malire pakati pa mizere yamafayilo.Mwachikhazikitso mysqlimport imagwiritsa ntchito newline ngati cholekanitsa mzere.Mukhoza kusankha kusintha munthu m'modzi ndi chingwe: mzere watsopano kapena kubwereranso. |
Zosankha zodziwika bwino za lamulo la mysqlimport zikuphatikiza -v kuwonetsa mtundu (mtundu), -p kuyitanitsa mawu achinsinsi, ndi zina zotero.
Tikukhulupirira kuti nkhani yakuti "Momwe Mungatumizire Mafayilo a TXT mu Matebulo a MySQL? Phunziro Lokhudza Kutumiza Mafayilo a SQL mu Database," yomwe yagawidwa pa blog ya Chen Weiliang ( https://www.chenweiliang.com/ ), idzakuthandizani.
Khalani omasuka kugawana ulalo wa nkhaniyi: https://www.chenweiliang.com/cwl-503.html