txt存入mysql和csv_mysql导入数据方法(txt,csv)

本文章介绍了利用 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内容：

Date Time Name Weight State

2006-09-01 12:00:00 Bill Wills 200 Nevada

2006-09-02 09:00:00 Jeff Deft 150 Oklahoma

2006-09-04 03:00:00 Bob Hobbs 225 Utah

2006-09-07 08:00:00 Hank Banks 175 Texas

文件必须被加载入如下的表

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 shortname

Nevada NV

Oklahoma OK

Utah UT

Texas TX

insert into states

values('Nevada','NV')

,('Oklahoma','OK')

,('Utah', 'UT')

,('Texas', 'TX')

导入：

load data infile '/tmp/data.txt' into table tbl

ignore 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本地文件导入到下的mysql数据库，加local

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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！