MYSQL使用記錄之:infile/outfile

qqmengxue發表於2010-11-03
MySql

由於現有的東家要求使用MYSQL資料庫，所以最近也開始了對MYSQL資料庫的進一步的研究，首先介入的當然是一些正常的應用了：

outfile/infile的靈活：

[@more@]

今天接觸了下mysql下的outfile和infile感覺和實際使用很貼切，不像ORACLE那麼商業化，任何操作都要封裝成自己的介面來實現、操作，MYSQL直接透過一個簡單的語句就完成了資料的匯入、匯出，滿足了日常的靈活性與可控性。

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]
demo：
示例檔案：
e.txt
===========================
1 2
1 2
1 2
3 4
1 2
3 4
3 4
==============================
ab
==============================
1,2;3,4
==============================
mysql> load data infile 'd:e.txt' into table test1;
Query OK, 7 rows affected (0.13 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0
mysql> load data infile 'd:e.txt' replace into table test1;
Query OK, 7 rows affected (0.06 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0
mysql> load data infile 'd:ab' replace into table test1 FIELDS TERMINATED  by ',' LINES TERMINATED  by ';';
Query OK, 2 rows affected (0.09 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
mysql> load data infile 'd:e.txt' replace into table test1 FIELDS TERMINATED  by ',' LINES TERMINATED  by ';' ignore 2
Query OK, 0 rows affected (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
outfile
mysql> select * into outfile 'd:out1.txt' from test1;
Query OK, 23 rows affected (0.00 sec)
mysql> select * into outfile 'd:out2.txt' FIELDS TERMINATED by ',' from test1
    -> ;
Query OK, 23 rows affected (0.00 sec)
mysql> select * into outfile 'd:out3.txt' FIELDS TERMINATED by ',' lines terminated by ';'  from test1
    -> ;
Query OK, 23 rows affected (0.02 sec)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10130206/viewspace-1040832/，如需轉載，請註明出處，否則將追究法律責任。

相關文章