Bii o ṣe le gbe txt sinu tabili data MySQL?Ṣe agbewọle faili sql sinu ikẹkọ data data

MySQLBii o ṣe le gbe tabili data wọle si txt?gbe wọle sql failiMySQL databaseTutorial

MySQL gbe wọle data

Awọn ọna ti o rọrun meji lo wa lati gbejade data ti MySQL si okeere ni MySQL.

Gbe data wọle nipa lilo DATA LOAD

Awọn alaye LOAD DATA INFILE ti pese ni MySQL lati fi data sii.Apẹẹrẹ ti o tẹle yoo ka faili dump.txt lati itọsọna lọwọlọwọ ki o fi data sii ninu faili naa sinu tabili mytbl ti aaye data lọwọlọwọ.

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

　Ti o ba jẹ pato koko-ọrọ LOCAL, o tọka si pe faili ka nipasẹ ọna lati ọdọ agbalejo alabara.Ti ko ba ṣe pato, faili naa jẹ kika nipasẹ ọna lori olupin naa.

O le pato awọn apinpin iye ọwọn ati awọn asami ila-ipari ni gbangba ninu alaye LOAD DATA, ṣugbọn awọn asami aiyipada jẹIpokikọ ki o si fi opin si ila.

Sintasi ti awọn aaye ati awọn gbolohun LINES jẹ kanna fun awọn aṣẹ mejeeji.Awọn gbolohun ọrọ mejeeji jẹ iyan, ṣugbọn ti awọn mejeeji ba ni pato, gbolohun FIELDS gbọdọ han niwaju gbolohun LINES.

Ti olumulo ba sọ asọye awọn aaye aaye kan, awọn gbolohun ọrọ rẹ (TI TERMINATED BY, [Optionally] ENCLOSED BY, ati ESCAPED BY) jẹ iyan, sibẹsibẹ, olumulo gbọdọ pato o kere ju ọkan ninu wọn.

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

Nipa aiyipada, LOAD DATA nfi data sii ni ọna ti awọn ọwọn ninu faili data Ti awọn ọwọn ti o wa ninu faili data ko ni ibamu pẹlu awọn ọwọn ninu tabili ti a fi sii, o nilo lati pato aṣẹ ti awọn ọwọn.

Fun apẹẹrẹ, aṣẹ ọwọn ninu faili data jẹ a,b,c, ṣugbọn aṣẹ ọwọn ninu tabili ti a fi sii jẹ b,c,a, sintasi agbewọle data jẹ bi atẹle:

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

Gbe data wọle nipa lilo mysqlimport

Onibara mysqlimport n pese wiwo laini aṣẹ si alaye LOAD DATA INFILEQL. Pupọ awọn aṣayan ti mysqlimport ṣe deede taara si gbolohun ọrọ LOAD DATA INFILE.

Lati gbe data wọle sinu tabili data mytbl lati faili dump.txt, aṣẹ atẹle le ṣee lo:

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

Aṣẹ mysqlimport le pato awọn aṣayan lati ṣeto ọna kika ti a sọ. Ọna kika alaye aṣẹ jẹ bi atẹle:

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

Lo aṣayan --columns ninu alaye mysqlimport lati ṣeto aṣẹ ti awọn ọwọn:

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

Ifihan si awọn aṣayan ti o wọpọ ti mysqlimport

Awọn aṣayan ti o wọpọ ti aṣẹ mysqlimport jẹ -v lati ṣafihan ẹya (ẹya), -p lati tọ fun ọrọ igbaniwọle, ati bẹbẹ lọ.