Yuav ua li cas import txt rau hauv MySQL cov ntaub ntawv?Ntshuam cov ntaub ntawv sql rau hauv database tutorial

MySQLYuav ua li cas import cov ntaub ntawv rooj rau hauv txt?import cov ntaub ntawv sqlMySQL databaseQhia Ua Kev Kawm

MySQL import cov ntaub ntawv

Muaj ob txoj hauv kev yooj yim rau import cov ntaub ntawv xa tawm los ntawm MySQL hauv MySQL.

Ntshuam cov ntaub ntawv siv LOAD DATA

LOAD DATA INFILE nqe lus tau muab rau hauv MySQL los ntxig cov ntaub ntawv.Cov piv txwv hauv qab no yuav nyeem cov ntaub ntawv dump.txt los ntawm cov npe tam sim no thiab ntxig cov ntaub ntawv hauv cov ntaub ntawv rau hauv mytbl rooj ntawm cov ntaub ntawv tam sim no.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

　Yog tias LOCAL lo lus tseem ceeb tau teev tseg, nws qhia tias cov ntaub ntawv tau nyeem los ntawm txoj kev los ntawm tus tswv tsev.Yog tias tsis tau teev tseg, cov ntaub ntawv tau nyeem los ntawm txoj hauv kev ntawm lub server.

Koj tuaj yeem hais qhia tus nqi tus nqi delimiters thiab qhov kawg ntawm kab cim qhia meej hauv LOAD DATA nqe lus, tab sis cov cim cim yogTxoj Haujlwmcov cim thiab kab tawg.

Lub syntax ntawm FIELDS thiab LINES clauses yog tib yam rau ob qho lus txib.Ob lub ntsiab lus yog xaiv tau, tab sis yog tias ob leeg tau teev tseg, FIELDS clause yuav tsum tshwm sim ua ntej LINES clause.

Yog tias tus neeg siv qhia meej txog FIELDS clause, nws cov nqe lus (TSIS TAU LOS NTAWM, [Yeem] ENCLOSED BY, thiab ESCAPED BY) yog xaiv tau, txawm li cas los xij, tus neeg siv yuav tsum qhia tsawg kawg ib qho ntawm lawv.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

Los ntawm lub neej ntawd, LOAD DATA ntxig cov ntaub ntawv nyob rau hauv qhov kev txiav txim ntawm txhua kab hauv cov ntaub ntawv cov ntaub ntawv.Yog cov kab hauv cov ntaub ntawv cov ntaub ntawv tsis sib haum nrog cov kab hauv cov lus ntxig, koj yuav tsum qhia qhov kev txiav txim ntawm txhua kab.

Piv txwv li, kab lus txiav txim nyob rau hauv cov ntaub ntawv cov ntaub ntawv yog a, b, c, tab sis kab lus txiav txim nyob rau hauv lub rooj yog b, c, a, cov ntaub ntawv import syntax yog raws li nram no:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);

Ntshuam cov ntaub ntawv siv mysqlimport

Tus neeg siv khoom mysqlimport muab cov kab lus hais kom ua rau LOAD DATA INFILEQL nqe lus. Feem ntau cov kev xaiv ntawm mysqlimport sib raug ncaj qha mus rau LOAD DATA INFILE clause.

Txhawm rau import cov ntaub ntawv rau hauv mytbl cov ntaub ntawv rooj los ntawm cov ntaub ntawv dump.txt, cov lus txib hauv qab no tuaj yeem siv:

$ mysqlimport -u root -p --local database_name dump.txt
password *****

Cov lus txib mysqlimport tuaj yeem qhia cov kev xaiv los teeb tsa cov hom ntawv teev tseg.

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****

Siv cov kev xaiv --columns hauv mysqlimport nqe lus los teeb tsa qhov kev txiav txim ntawm kab:

$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

Taw qhia rau cov kev xaiv ntau ntawm mysqlimport

Cov kev xaiv uas nquag siv ntawm mysqlimport hais kom ua yog -v los tso saib cov version (version), -p kom sai rau tus password, thiab lwm yam.