mysql 匯入資料

mysqlimport樣本mysqlimport -uroot -p123456 test /tmp/mytbl.txt;約定：檔案名稱的最後一部分為表名，以上語句匯入到表mytblmysqlimport必須指定資料庫，以上語句資料庫為test

 

 匯入csv

mysqlimport -uroot -p --local --lines-terminated-by="\r\n" --fields-terminated-by="," --fields-enclosed-by="\"" test /tmp/mytbl.csv

 

load data樣本mysql> load data  infile '/tmp/mytbl.txt' into table mytblload data可以不指定資料庫以上語句中，mysql必須有/tmp/的讀許可權匯入csvmysql> load data  infile '/tmp/mytbl.csv' into table mytbl fields terminated by ',' enclosed by '"' lines terminated by '\r\n'

 

處理重複主鍵替換已有值mysql> load data infile '/tmp/mytbl.txt' replace into table mytbl fields terminated by '\t' lines terminated by '\n'表中已有則不匯入mysql> load data infile '/tmp/mytbl.csv' ignore into table mytbl fields terminated by ',' enclosed by '"' lines terminated by '\r\n' 跳過檔案行以下樣本為跳過第一行mysql> load data infile '/tmp/mytbl.txt' into table mytbl ignore 1 lines; 預先處理檔案data.txt內容：DateTimeNameWeightState2006-09-0112:00:00Bill Wills200Nevada2006-09-0209:00:00Jeff Deft150Oklahoma2006-09-0403:00:00Bob Hobbs225Utah2006-09-0708:00:00Hank Banks175Texas檔案必須被載入入如下的表create table tbl(dt datetime,last_name char(10),first_name char(10),weight_kg float,st_abbrev char(2))create table states(name varchar(20),shortname char(2))states 表中內容:name shortnameNevada NVOklahoma OKUtah UTTexas TXinsert into statesvalues('Nevada','NV'),('Oklahoma','OK'),('Utah', 'UT'),('Texas', 'TX')匯入：load data infile '/tmp/data.txt' into table tblignore 1 lines(@date,@time,@name,@weight_lb,@state)set dt=concat(@date,' ',@time),     first_name=substring_index(@name,' ',1),     last_name=substring_index(@name,' ',-1),     weight_kg=@weight_lb * .454,     st_abbrev = (select shortname from states where name=@state); 結果：mysql> select * from tbl;                                                                                                                                               +---------------------+-----------+------------+-----------+-----------+| dt                  | last_name | first_name | weight_kg | st_abbrev |+---------------------+-----------+------------+-----------+-----------+| 2006-09-01 12:00:00 | Wills     | Bill       |      90.8 | NV        || 2006-09-02 09:00:00 | Deft      | Jeff       |      68.1 | OK        || 2006-09-04 03:00:00 | Hobbs     | Bob        |    102.15 | UT        || 2006-09-07 08:00:00 | Banks     | Hank       |     79.45 | TX        |+---------------------+-----------+------------+-----------+-----------+ 將windows本地檔案匯入到linux下的mysql資料庫，加local

load data local infile 'C:/mytbl.txt' into table mytbl fields terminated by '\t' lines terminated by '\r\n'