mysql 导入数据

mysqlimport

示例
[root@localhost ~]# mysqlimport -uroot -p123456 test /tmp/mytbl.txt;

约定：文件名的最后一部分为表名，以上语句导入到表mytbl

mysqlimport必须指定数据库，以上语句数据库为test

 导入csv

[root@localhost ~]# 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 mytbl CHARACTER SET gbk;

load data可以不指定数据库

以上语句中，mysql必须有/tmp/的读权限

导入csv

mysql> 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;

将windows本地文件导入到linux下的mysql数据库，加local

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