本文章介绍了利用 mysqlimport命令来实现数据导入方法,包括txt,csv,.sql的文件的操作,有需要了解的同学可参考一下.
mysqlimport
示例:mysqlimport -uroot -p123456 test /tmp/mytbl.txt;
约定:文件名的最后一部分为表名,以上语句导入到表mytbl.
mysqlimport必须指定数据库,以上语句数据库为test.
导入csv:mysqlimport -uroot -p --local --lines-terminated-by="rn" --fields-terminated-by="," --fields-enclosed-by=""" test /tmp/mytbl.csv
load data
示例:mysql> load data infile '/tmp/mytbl.txt' into table mytbl
load data可以不指定数据库,以上语句中,mysql必须有/tmp/的读权限.
导入csv:mysql> load data infile '/tmp/mytbl.csv' into table mytbl fields terminated by ',' enclosed by '"' lines terminated by 'rn'
处理重复主键,替换已有值:
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 'rn'
跳过文件行,以下示例为跳过第一行:
mysql> load data infile '/tmp/mytbl.txt' into table mytbl ignore 1 lines;
预处理,文件data.txt内容:
- DateTimeNameWeightState
- 2006-09-0112:00:00BillWills200Nevada
- 2006-09-0209:00:00JeffDeft150Oklahoma
- 2006-09-0403:00:00BobHobbs225Utah
- 2006-09-0708:00:00HankBanks175Texas
文件必须被加载入如下的表:
- createtabletbl
- (
- dtdatetime,
- last_namechar(10),
- first_namechar(10),
- weight_kgfloat,
- st_abbrevchar(2)
- )
- createtablestates
- (
- namevarchar(20),
- shortnamechar(2)
- )
- --states表中内容:
- nameshortname
- NevadaNV
- OklahomaOK
- UtahUT
- TexasTX
- insertintostates
- values('Nevada','NV')
- ,('Oklahoma','OK')
- ,('Utah','UT')
- ,('Texas','TX')
- --导入：
- loaddatainfile'/tmp/data.txt'intotabletbl
- ignore1lines
- (@date,@time,@name,@weight_lb,@state)
- setdt=concat(@date,'',@time),
- first_name=substring_index(@name,'',1),
- last_name=substring_index(@name,'',-1),
- weight_kg=@weight_lb*.454,
- st_abbrev=(selectshortnamefromstateswherename=@state);
- --结果：
- mysql>select*fromtbl;+---------------------+-----------+------------+-----------+-----------+--phpfensi.com
- |dt|last_name|first_name|weight_kg|st_abbrev|
- +---------------------+-----------+------------+-----------+-----------+
- |2006-09-0112:00:00|Wills|Bill|90.8|NV|
- |2006-09-0209:00:00|Deft|Jeff|68.1|OK|
- |2006-09-0403:00:00|Hobbs|Bob|102.15|UT|
- |2006-09-0708: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 'rn'
