mysql 数据导入导出命令详解_mysql导入导出命令

一、导入导出场景及简单用法

都是基于文本文件

导入：

mysqlimport -usystem -p -S /usr/local/mysql/data/mysql.sock test --fields-terminated-by=',' /usr/local/mysql/tt3.txt

适用简单导入场景，导入文件名必须与对象名一致

load data infile ‘***file_name’ into table ***  fields terminated by ',';

适用复杂导入场景

导出：select * from tt4 into outfile '/tmp/***file_name.txt'  FIELDS terminated by ','  ;

二、MySQL 使用CSV存储引擎导入加载数据

1、查看csv文本文件中的内容

[root@localhost fire]# cat /tmp/ry.txt

10,ACCOUNTING,NEW YORK

20,RESEARCH,DALLAS

30,SALES,CHICAGO

40,OPERATIONS,BOSTON

2、--根据文本文件的格式创建CSV表

mysql>CREATE TABLE ry(

   deptno int(11) NOT NULL DEFAULT '0',

   dname varchar(15) NOT NULL,

   loc varchar(15) NOT NULL)

   ENGINE=CSV;

Query OK, 0 rows affected (0.06 sec)

3、将csv文本文件中的内容导入到创建表后生成的CSV文件中

[root@localhost fire]# more /tmp/dept.txt > ry.CSV

[root@localhost fire]# cat ry.CSV

10,ACCOUNTING,NEW YORK

20,RESEARCH,DALLAS

30,SALES,CHICAGO

40,OPERATIONS,BOSTON

4、查看导入到表中的数据

mysql> flush table ry; ---刷新表查询缓存，不然查询不到数据

mysql> select * from ry;

+--------+------------+----------+

| deptno | dname      | loc      |

+--------+------------+----------+

|     10 | ACCOUNTING | NEW YORK |

|     20 | RESEARCH   | DALLAS   |

|     30 | SALES      | CHICAGO  |

|     40 | OPERATIONS | BOSTON   |

+--------+------------+----------+

三、mysqlimport 命令行工具导入数据：(类似oralce Sql*Loader)  处理格式的数据比较方便

1、创建表

​

2、上传txt文件，命名与对象相同的名称

mv 1.txt tt3.txt

3、导入数据(test 为导入表在的库名)

mysqlimport -usystem -p -S /usr/local/mysql/data/mysql.sock test --fields-terminated-by=',' /usr/local/mysql/tt3.txt

4、查询数据

select * from tt3;

5、参数用法详解

输出定界格式文件，通过参数指定SQL格式输出，并且输出为实际数据的.txt文件和对象结构的.sql 文件

--fileds-terminated-by：指定列值的分隔符，默认值Tab符

--fields-enclosed-by：指定列值的包括符，默认值没有（对于字符中包含列分隔符的直接包括起来）例如：12#kk#2#4，2与4间包括了#，使用包括符后，”2#4“

--fields-optionally-enclosed-by: 指定非数字列的包括符，默认值没有（有就使用，没有就不使用）

--fields-escped-by：指定转义符，默认值转义符\

--lines-terminated-by:指定行结束符，默认值就是换行符

-d:导入前删除对象中数据

6、导入案例：

mysqlimport -usystem -p -S /usr/local/mysql/data/mysql.sock test -d --fields-terminated-by='#' /usr/local/mysql/tt3.txt

1#IN#北京#KK科技有限公司#郭三#22245

mysqlimport -usystem -p -S /usr/local/mysql/data/mysql.sock test -d --fields-terminated-by='#'  -fields-enclosed-by=\" /usr/local/mysql/tt3.txt

1#"IN"#"北京#KK科技有限公司"#"郭三"#"22245"

​​

mysqlimport -usystem -p -S /usr/local/mysql/data/mysql.sock test -d --fields-terminated-by='#'  -fields-enclosed-by=\"  --lines-terminated-by='###\n' /usr/local/mysql/tt3.txt

1#"IN"#"北京#KK科技有限公司"#"郭三"#"22245"###

2#"IN"#"北京#KK科技有限公司"#"郭四"#"22246"###

​​

四、使用LOAD DATA  (使用本地编辑的csv格式上传到服务器上，必须转换成unix格式，才能导入)

语法：

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,...]

注意：load data 需要有处理文件的权限， GRANT FILE ON . TO user@host;

1、案例分析

CREATE TABLE tt14 (

 id int(11) NOT NULL,

 id_tye varchar(10) NOT NULL,

 zt_name varchar(60) NOT NULL,

 zdr_name varchar(20) NOT NULL,

 zdr_id varchar(20) NOT NULL

) ;

设置安全文件为空，不然导入文件必须在安全目录下

​​

mysql>load data infile '/usr/local/mysql/data/test/4.txt'  into table tt14 fields terminated by ',';

1#"IN"#"北京#KK科技有限公司"#"郭三"#"22245"###

2#"IN"#"北京#KK科技有限公司"#"郭四"#"22246"###

​​

关于字符集：数据文件的字符集与数据库字符集设置不同，导入会乱码。

解决方法：导入时指定处理数据的字符集（或者更改数据库字符集与数据文件一致set character_set_database=utf8mb4）

load data infile '/usr/local/mysql/data/test/4.txt' into table tt14  CHARACTER SET  utf8mb4 fields terminated by ',';

2、远程客户端导入

在客户端导入使用local参数，文件存放到客户端上

查看客户端 local_infile的状态，未开启的为OFF，利用set global local_infile=on;开启即可

​​

mysql> load data local infile '/home/mysql/tt15.txt' into table tt15 fields terminated by ',';

3、冲突处理方式(有唯一约束或者主键的情况下)

[REPLACE | IGNORE]

指定 REPLACE ，出现重复会替换当前存在的记录

指定IGNORE ,出现重复跳过重复的记录

都未指定，处理依赖是否指定了LOCAL ，没有指定LOCAL，出现重复报错、指定LOCAL，处理行为与IGNORE一致。

mysql> load data local infile '/home/mysql/tt15.txt' ignore into table tt15 fields terminated by ',';

Query OK, 0 rows affected, 31 warnings (0.00 sec)

Records: 31  Deleted: 0  Skipped: 31  Warnings: 31

直接跳过

mysql> load data local infile '/home/mysql/tt15.txt' REPLACE into table tt15 fields terminated by ',';

Query OK, 31 rows affected (0.00 sec)

Records: 31  Deleted: 0  Skipped: 0  Warnings: 0

删除，重新插入

4、文件前N行不处理（GNORE N LINES;）

load data infile '/usr/local/mysql/data/test/3.csv' into table tt16 fields terminated by ',' IGNORE 1 LINES;

5、行和列的精确处理

[{FIELDS | COLUMNS}

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

输出定界格式文件，通过参数指定SQL格式输出，并且输出为实际数据的.txt文件和对象结构的.sql 文件

fileds terminated by：指定列值的分隔符，默认值Tab符（\t）

fields enclosed by：指定列值的包括符，默认值没有（对于字符中包含列分隔符的直接包括起来）例如：12#kk#2#4，2与4间包括了#，使用包括符后，”2#4“

fields escped by：指定转义符，默认值转义符\

lines terminated by:指定行结束符，默认值就是换行符(\n)

starting by :指定每行的开始位置(与字符有关)

（windows系统那么使用'\r\n' 表示换行）

案例：

5.1 字段与字段的分隔符不是’Tab‘ 处理

load data infile '/usr/local/mysql/data/test/3.csv' into table tt16 fields terminated by '#' ;

27#OUT#辽宁途途网约车运营服务有限公司#尚颖迪#D21361

5.2 数据行的行头包括的某些字符不希望导入

> 28,OUT,辽宁KK服务有限公司,尚XX,21361

> 29,OUT,辽宁KK服务有限公司,尚XX,21361

> 30,OUT,辽宁KK服务有限公司,SYSADMIN,sysadmin

> 31,OUT,辽宁KK服务有限公司,SYSADMIN,sysadmin

load data infile '/usr/local/mysql/data/test/3.csv' into table tt16 fields terminated by ',' LINES STARTING BY '>' ;

​​

5.3 EXCEL 导入，先转换成CSV文件(CSV 默认分隔符',')

load data infile '/usr/local/mysql/data/test/3.csv' into table tt16 fields terminated by ',' ;

5.4列值中有特殊符号(使用包括符)

load data infile '/usr/local/mysql/data/test/3.csv' into table tt16 fields terminated by ','  enclosed by '"' ;

1#"IN"#"北京#KK科技有限公司"#"郭三"#"22245"

5.4 列值中包含包括符(处理方式：1、直接删除列值中的包括符2、使用转义符)

load data infile '/usr/local/mysql/data/test/3.csv' into table tt16 fields terminated by ','  enclosed by '"'  escped by ’|‘ ;

1#"IN"#"北京#KK科技|限公司"#"郭三"#"22245"

5.5 特殊关注符(windows文件使用\r\n 作为换行符)

windows上创建3.CSV 打包上传到linux服务器

load data infile '/usr/local/mysql/data/test/3.csv' into table tt16 fields terminated by ','  enclosed by '"'  escped by ’|‘  lines terminated by ’\r\n‘;

1#“IN”#“北京快#桔安|“运科|”技有限公司”#“郭薄”#“D22245”

5.6 列值中包含换行符

直接处理，只要被指定的包括符包含着，不管中间出现什么都不受影响（转义和包括符除外）

6、对象结构与数据文件不符

6.1表对象中的列比数据文件中的列少

Create Table: CREATE TABLE `tt17` (

 `id` int(11) NOT NULL,

 `id_tye` varchar(10) NOT NULL,

 `zt_name` varchar(60) NOT NULL,

 `zdr_name` varchar(20) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据文件

1,IN,k,北京KKB公司,郭X,22245

2,IN,i,北京KKb公司,flpbot1_v,V098823

3,IN,b,深圳KK有限公司,flpbab,V004234

4,OUT,c,深圳市KK有限公司,汪X,22235

5,OUT,d,深圳市KK有限公司,汪X,22235

6,OUT,f,深圳市KK有限公司,汪X,22235

7,OUT,e,深圳市KK有限公司,汪X,22235

使用自定义变量代替不存在的字段(字段的顺序根据表顺序调整)

mysql>load data infile '/usr/local/mysql/data/test/7.txt' into table tt17 fields terminated by ','  (id,id_tye,@temp,zt_name,zdr_name,@tmp);

6.2 表对象中的列比数据文件中的列多

CREATE TABLE tt18 (

 id int NOT NULL  ,

 id_tye varchar(10) NOT NULL,

 zt_name varchar(60) NOT NULL,

 zdr_name varchar(20) NOT NULL,

 zdr_id varchar(20) NOT NULL,

 zdr_v varchar(20) NOT NULL

) ;

1,IN,,北京KKB有限公司,郭X,22245

2,IN,北京沃KKB有限公司,flpbot1_v,V00456

3,IN,深圳KKC有限公司,flpbot1_v,V00456

4,OUT,深圳市KK有限公司,汪X,22235

5,OUT,深圳市KK有限公司,汪X,22235

6,OUT,深圳市KK有限公司,汪X,22235

7,OUT,深圳市KK有限公司,汪X,22235

想导入几列就导入几列

mysql>load data infile '/usr/local/mysql/data/test/4.txt' into table tt18 fields terminated by ','  (id,id_tye,zt_name,zdr_name,zdr_id);

6.3 导入可以赋予其他的字段值

mysql>load data infile '/usr/local/mysql/data/test/4.txt' REPLACE into table tt18 fields terminated by ','  (id,id_tye,zt_name,zdr_name,zdr_id) set zdr_v='alan';

6.4 列值中字符类型不符

CREATE TABLE tt19 (

 id int NOT NULL  ,

 id_tye tinyint(10) NOT NULL,

 zt_name varchar(60) NOT NULL,

 zdr_name varchar(20) NOT NULL,

 zdr_id varchar(20) NOT NULL

) ;

1,IN,k,北京KKB公司,郭薄,D22245

2,IN,i,北京KKC有限公司,flpbot1_v,V00456

3,IN,b,深圳KKD有限公司,flpbot1_v,V00456

4,OUT,c,深圳市KK有限公司,汪X,22235

5,OUT,d,深圳市KK有限公司,汪X,22235

6,OUT,f,深圳市KK有限公司,汪X,22235

7,OUT,e,深圳市KK有限公司,汪X,22235

mysql>load data infile '/usr/local/mysql/data/test/7.txt'  into table tt19 fields terminated by ','  (id,@tmp_id_tye,@tmp,zt_name,zdr_name,zdr_id)  

set id_tye=if(@tmp_id_tye='IN',0,1);

导入直接处理，通过SET 子句，直接给某列赋值，在赋值时通过IF 判断前面变量的值，并返回不同的结果。

五、数据导出

SELECT ... INTO OUTFILE 'file_name'

       [CHARACTER SET charset_name]

       [export_options]

export_options:

   [{FIELDS | COLUMNS}

       [TERMINATED BY 'string']

       [[OPTIONALLY] ENCLOSED BY 'char']

       [ESCAPED BY 'char']

   ]

   [LINES

       [STARTING BY 'string']

       [TERMINATED BY 'string']

   ]

| INTO DUMPFILE 'file_name'

| INTO var_name[,var_name]

select * from tt19 into outfile '/tmp/tt19.txt';

select * from tt19 into outfile '/tmp/tt1901.txt' FIELDS terminated by ','  ;