Yadda ake fitarwa fayilolin bayanai daga Linux MySQL database?umurnin sanarwa csv fitarwa

Linux MySQL databaseYadda ake fitarwa fayilolin bayanai?umurnin sanarwa csv fitarwa

MySQLfitarwa bayanai

A cikin MySQL zaka iya amfaniZABI… CIKIN WANDAsanarwa don fitarwa kawai bayanai zuwa fayil ɗin rubutu.

Fitar da bayanai ta amfani da SELECT ... INTO OUTFILE sanarwa

A cikin misali mai zuwa za mu fitar da teburin bayanai zuwa fayil /tmp/tutorials.txt:

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

Kuna iya saita ƙayyadadden tsarin fitar da bayanai ta hanyar zaɓuɓɓukan umarni.Misali mai zuwa shine don fitar da tsarin CSV:

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

A cikin misalin da ke ƙasa, ana ƙirƙira fayil tare da ƙimar waƙafi.Wannan tsari na iya amfani da shi da yawa shirye-shirye.

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

Bayanin SELECT ... INTO OUTFILE yana da halaye masu zuwa:

• LOAD DATA INFILE shine juzu'in aiki na SELECT ... CIKIN WATA, SELECT syntax.Don rubuta bayanai daga rumbun adana bayanai zuwa fayil, yi amfani da SELECT ... INTO OUTFILE, kuma don sake karanta fayil ɗin a cikin ma'ajin bayanai, yi amfani da LOAD DATA INFILE.
• ZABI... A CIKIN WATA KYAUTA ZABI na sigar 'file_name' na iya rubuta zaɓaɓɓun layukan zuwa fayil.An ƙirƙiri fayil ɗin akan uwar garken uwar garken, don haka dole ne ku sami izinin FILE don amfani da wannan haɗin gwiwa.
• Fitarwa ba zai iya zama fayil ɗin da ke wanzu ba.Hana bayanan fayil daga lalata dasu.
• Kuna buƙatar samun asusu don shiga uwar garken don dawo da fayiloli.In ba haka ba KYAUTA ... CIKIN WANDA ba zai yi tasiri ba.
• A cikin UNIX, an ƙirƙiri fayil ɗin don a iya karantawa, kuma izini na uwar garken MySQL ne.Wannan yana nufin cewa yayin da za ku iya karanta fayil ɗin, ƙila ba za ku iya goge shi ba.

Teburin fitarwa azaman danyen bayanai

Ya fi samar da rubutun SQL mai ɗauke da umarni CREATE TABLE INSERT da sauransu da ake buƙata don sake ƙirƙirar bayanan bayanai daga karce.

Don fitar da bayanai ta amfani da mysqldump, kuna buƙatar amfani da zaɓin –tab don tantance kundin adireshi da fayil ɗin fitarwa ya kayyade, kuma dole ne a rubuta manufa.

Misali mai zuwa yana fitar da teburin bayanai chenweiliang_tbl zuwa ga /tmp directory:

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

Fitar da bayanai a cikin tsarin SQL

Fitar da bayanai a cikin tsarin SQL zuwa ƙayyadadden fayil, kamar haka:

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

Abin da ke cikin fayil ɗin da umarnin da ke sama ya ƙirƙira shine kamar haka:

- 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' ）;

Idan kana buƙatar fitar da bayanan dukkan bayanan, za ka iya amfani da umarni mai zuwa:

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

Idan kana buƙatar yin ajiyar duk bayanan bayanai, zaka iya amfani da umarni mai zuwa:

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

An ƙara zaɓin --all-databases a cikin MySQL 3.23.12 kuma daga baya.

Ana iya amfani da wannan hanyar don aiwatar da dabarun adana bayanai.

Kwafi tebur bayanai da ma'ajin bayanai zuwa wasu runduna

Idan kuna buƙatar kwafin bayanai zuwa wani uwar garken MySQL, zaku iya saka sunan bayanan da tebur a cikin umarnin mysqldump.

Yi umarni mai zuwa akan mai watsa shiri don adana bayanan zuwa fayil ɗin dump.txt:

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

Idan kun tanadi bayananku gaba ɗaya, ba kwa buƙatar amfani da takamaiman sunan tebur.

Idan kana buƙatar shigo da bayanan ajiya zuwa uwar garken MySQL, zaku iya amfani da umarni mai zuwa, ta amfani da umarni mai zuwa kuna buƙatar tabbatar da cewa an ƙirƙiri bayanan:

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

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

Umurnin da ke sama yana amfani da bututu don shigo da bayanan da aka fitar zuwa keɓaɓɓen mai watsa shiri mai nisa.