Momwe mungatumizire mafayilo kuchokera ku Linux MySQL database?export csv statement command

Linux MySQL databaseMomwe mungatumizire mafayilo a data?export csv statement command

MySQLExport data

Mu MySQL mungagwiritse ntchitoSANKHANI ... MU OUTFILEchiganizo chongotumiza deta ku fayilo yolemba.

Tumizani zinthu kunja pogwiritsa ntchito mawu akuti SELECT ... INTO OUTFILE

Muchitsanzo chotsatira tidzatumiza tebulo la data ku fayilo ya /tmp/tutorials.txt:

mysql > SELECT * FROM chenweiliang_tbl 
     - > INTO OUTFILE '/tmp/tutorials.txt' ;

Mutha kukhazikitsa mtundu womwe mwasankha wa data yomwe mwasankha kudzera muzosankha zamalamulo. Chitsanzo chotsatira ndichotumiza mtundu wa CSV:

mysql > SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.csv' - > FIELDS TERMINATED BY '，' ENCLOSED BY ''' - > LINES TERMINATED BY '\ r \ n' ;
    
    

Muchitsanzo chomwe chili pansipa, fayilo imapangidwa ndi zinthu zomwe zimasiyanitsidwa ndi koma.Mtundu uwu ukhoza kugwiritsidwa ntchito ndi mapulogalamu ambiri.

SELECT a ，b ，a + b INTO OUTFILE '/tmp/result.text'FIELDS 
TERMINATED BY '，' OPTIONALLY ENCLOSED BY '''行
终止'\ 
n'FROM test_table ;

Mawu a SELECT ... INTO OUTFILE ali ndi izi:

• LOAD DATA INFILE ndiye njira yosinthira ya SELECT ... MU OUTFILE, SINKHANI mawu.Kuti mulembe deta kuchokera pankhokwe kupita ku fayilo, gwiritsani ntchito SINANI ... MU OUTFILE, ndipo kuti muwerengenso fayiloyo mu database, gwiritsani ntchito LOAD DATA INFILE.
• SANKHANI ... MU OUTFILE KUSANKHA kwa fomu ya 'file_name' kumatha kulemba mizere yosankhidwa ku fayilo.Fayiloyo imapangidwa pa seva yolandila, kotero muyenera kukhala ndi chilolezo cha FILE kuti mugwiritse ntchito mawuwa.
• Zotulutsa sizingakhale fayilo yomwe ilipo.Pewani mafayilo kuti asasokonezedwe.
• Kodi muyenera kukhala ndi akaunti kuti mulowe ku seva kuti mutenge mafayilo.Kupanda kutero SINANI ... MU OUTFILE sikudzakhala ndi zotsatira.
• Mu UNIX, fayilo imapangidwa kuti iwerengedwe, ndipo zilolezo zimakhala ndi seva ya MySQL.Izi zikutanthauza kuti ngakhale mutha kuwerenga fayiloyo, simungathe kuichotsa.

Tumizani tebulo ngati data yaiwisi

Imapanga script ya SQL yomwe ili ndi malamulo CREATE TABLE INSERT ndi zina zofunika kuti mukonzenso database kuyambira pachiyambi.

Kuti mutumize deta pogwiritsa ntchito mysqldump, muyenera kugwiritsa ntchito njira ya -tab kuti mufotokoze chikwatu chomwe chafotokozedwa ndi fayilo yotumiza kunja, ndipo cholingacho chiyenera kulembedwa.

Chitsanzo chotsatirachi chimatumiza tebulo la data chenweiliang_tbl ku /tmp chikwatu:

$ mysqldump - u root - p - no - create - info
             - tab = / tmp chenweiliang chenweiliang_tbl
密码******

Tumizani deta mumtundu wa SQL

Tumizani deta mumtundu wa SQL ku fayilo yotchulidwa, motere:

$ mysqldump - u root - p chenweiliang chenweiliang_tbl > dump 。文本 
密码******

Zomwe zili mufayilo yopangidwa ndi lamulo ili pamwambapa ndi izi:

- MySQL 转储8.23 - - 主机：localhost     数据库：chenweiliang
 ----------------------------------- ---------------------- - 服务器版本        3.23 。58   

 
 

- - 表结构为表`chenweiliang_tbl` -
 


CREATE TABLE chenweiliang_tbl （
  chenweiliang_id INT （11 ）NOT NULL的auto_increment ，
  chenweiliang_title VARCHAR （100 ）NOT NULL 默认'' ，
  chenweiliang_author VARCHAR （40 ）NOT NULL 默认'' ，
  submission_date日期默认NULL ，
  PRIMARY KEY   （chenweiliang_id ），
  UNIQUE KEY AUTHOR_INDEX （chenweiliang_author ））TYPE = MyISAM ;  


- - 转储数据的表`chenweiliang_tbl` -
 


INSERT INTO chenweiliang_tbl 
       VALUES （1 ，'Learn PHP' ，'John Poul' ，'2007-05-24' ）;
INSERT INTO chenweiliang_tbl 
       VALUES （2 ，'学习MySQL' ，'Abdul S' ，'2007-05-24' ）;
INSERT INTO chenweiliang_tbl 
       VALUES （3 ，'JAVA Tutorial' ，'Sanjay' ，'2007-05-06' ）;

Ngati mukufuna kutumiza deta ya database yonse, mutha kugwiritsa ntchito lamulo ili:

$ mysqldump - u root - p chenweiliang > database_dump 。文本
密码******

Ngati mukufuna kusunga ma database onse, mutha kugwiritsa ntchito lamulo ili:

$ mysqldump - u root - p - all - databases > database_dump 。文本
密码******

Njira ya --all-databases idawonjezedwa mu MySQL 3.23.12 ndi pambuyo pake.

Njirayi ingagwiritsidwe ntchito kukhazikitsa njira yosunga zosunga zobwezeretsera database.

Koperani matebulo a data ndi nkhokwe kwa osunga ena

Ngati mukufuna kukopera deta ku seva ina ya MySQL, mukhoza kufotokoza dzina lachinsinsi ndi tebulo mu mysqldump command.

Perekani lamulo ili pa gwero la wolandira kuti musungitse deta ku fayilo ya dump.txt:

$ mysqldump - u root - p database_name table_name > dump 。文本 
密码*****

Ngati musungira deta yanu yonse, simukuyenera kugwiritsa ntchito dzina la tebulo.

Ngati mukufuna kulowetsa zosunga zobwezeretsera ku seva ya MySQL, mutha kugwiritsa ntchito lamulo ili, pogwiritsa ntchito lamulo ili lomwe muyenera kutsimikizira kuti database yapangidwa:

$ mysql - u root - p database_name < dump 。文本 
密码*****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：</ p > 
$ mysqldump的- ü根- p DATABASE_NAME \
        | mysql - h other - host 。com database_name

Lamulo lomwe lili pamwambapa limagwiritsa ntchito chitoliro kulowetsa deta yomwe yatumizidwa kwa omwe atchulidwa akutali.