MySQL 資料匯入

標籤：mysql 資料匯入 mysqlimport load data

100001,胡一,北京,[email protected]
100002,胡二,上海,[email protected]
100003,胡十,廣州,[email protected]
100004,胡百,深圳,[email protected]
100005,胡千,珠海,[email protected]
100006,胡萬,杭州,[email protected]


create table ld_csv1(id int not null default ‘0‘,
 username varchar(5) not null,
 city varchar(6) not null,
 email varchar(50) not null) engine=innodb default charset=utf8;

注意mysql5.7要先設定my.cnf的變數：
secure_file_priv=""

然後重啟資料庫，然後執行mysqlimport命令：
[[email protected] soft]# mysqlimport -uroot -p123456 -h192.168.11.31 test --default-character-set=utf8 --fields-terminated-by=‘,‘ /tmp/ld_csv1


--default-character-set= 設定預設字元
--fields-terminated-by 指定每資料行分隔符號
-i，--ignore 如果插入記錄有重複鍵，那麼該條記錄不處理
-r，--replace：如果插入記錄發現重複，則覆蓋舊記錄
-L，--local 從執行mysqlimport命令的用戶端本地讀檔案。
-l，--lock-tables：匯入時鎖定表
-S，--silent：靜默模式匯入
--ignore-lines= 跳過檔案的前n行
--use-threads= 以並行方式載入
-d，--delete 匯入資料前先刪除舊資料。
--fields-enclosed-by=xx指定括住列值的符號
--fields-optionally-enclosed-by=xxx  指定列限定符，如果沒有限定符就忽略。

假設下面資料：
100007#“胡#萬萬”#“杭州”#“[email protected]”
該資料使用者名稱中間用一個#符號，可以用下面命令進行匯入：
mysqlimport -uroot -p123456 -h192.168.11.31 test --default-character-set=utf8 --fields-terminated-by=‘#‘
--fields-enclosed-by=\" /tmp/ld_csv1

當一條記錄包含在多行時，以三個#號結束一條記錄：
100006#"胡萬"#"杭
州"#"[email protected]"###
100007#"胡#
萬萬"#"杭州"#"[email protected]"###

可以使用:
--lines-terminated-by= 指定文中記錄行的結束符，預設是分行符號號，命令如下：
[[email protected] tmp]# mysqlimport -uroot -p123456 -h192.168.11.31 -d test --default-character-set=utf8 --fields-terminated-by=‘#‘ --fields-enclosed-by=\" --lines-terminated-by=‘###\n‘ /tmp/ld_csv1

mysqlimport僅僅使用資料比較規範的匯入。


load data infile

文法：
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name‘
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY ‘string‘]
        [[OPTIONALLY] ENCLOSED BY ‘char‘]
        [ESCAPED BY ‘char‘]
    ]
    [LINES
        [STARTING BY ‘string‘]
        [TERMINATED BY ‘string‘]
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]
    
利用之前的資料，建立下面表：


create table ldsql(
id int(11) default 0,
username varchar(5),
city varchar(6),
email varchar(50)) engine=innodb charset=utf8;

[[email protected] tmp]# cat cvs2
100001#"胡一"#"北京"#"[email protected]"
100002#"胡二"#"上海"#"[email protected]"
100003#"胡十"#"廣州"#"[email protected]"
100004#"胡百"#"深圳"#"[email protected]"
100005#"胡千"#"珠海"#"[email protected]"
100006#"胡萬"#"杭州"#"[email protected]"
100007#"胡#萬萬"#"杭州"#"[email protected]"

mysql> load data infile ‘/tmp/cvs2‘ into table ldsql charset utf8 FIELDS TERMINATED BY ‘#‘;

資料成功匯入.
FIELDS OPTIONALLY ENCLOSED BY,LINES TERMINATED BY等處理文法都與mysqlimport相同。

處理資料檔案前N行記錄不匯入：
可以使用[IGNORE number LINES]文法。
mysql> truncate ldsql;
Query OK, 0 rows affected (0.01 sec)
mysql> load data LOCAL infile ‘/tmp/cvs2‘ into table ldsql charset utf8 FIELDS TERMINATED BY ‘#‘ IGNORE 2 LINES;

處理行列問題,有以下資料：

Junsansi"abc",1
WHere is Junsansi"def",2
"ghi",3
匯入資料過程中過濾Junsansi字元，建立下面測試表：
create table test_starting(v1 varchar(20),id int);

LOAD DATA INFILE ‘/tmp/test_starting‘ INTO TABLE test_starting FIELDS TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘ LINES STARTING BY "Junsansi";
LINES STARTING BY ‘prefix_string‘ 希望跳過包含指定字元的首碼及首碼之前的所有字元。

有一個csv格式檔案，內容如下：
1,192.168.11.25,Centos5.4,serverGroup1-2,0.11
2,192.168.11.79,WINDOWS SERVER 2003,serverGroup2-3,0.14
3,192.168.11.31,Centos57,serverGroup3-4,0.15
建立表：

create table ld_t1(id int not null auto_increment primary key,
ip varchar(15),
os varchar(20),
prcname varchar(20),
res varchar(10)) charset utf8;
匯入語句：
load data infile ‘/tmp/ld_t1‘ into table ld_t1 FIELDS TERMINATED BY ‘,‘ IGNORE 1 LINES;

本文出自 “技術部落格” 部落格，請務必保留此出處http://raytech.blog.51cto.com/7602157/1771600

MySQL 資料匯入