Momwe mungalowetse txt mu tebulo la data la MySQL?Lowetsani fayilo ya sql mu maphunziro a database

MySQLMomwe mungalowetse tebulo la data mu txt?import sql fileMySQL databaseMaphunziro

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.

Mutha kutchula zochepetsa mtengo ndi zolembera kumapeto kwa mzere momveka bwino mu LOAD DATA statement, koma zolembera zokhazikika ndi.Kuyikazilembo ndi zoduka mizere.

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

Zosankha zodziwika bwino za lamulo la mysqlimport zikuphatikiza -v kuwonetsa mtundu (mtundu), -p kuyitanitsa mawu achinsinsi, ndi zina zotero.