一、导入导出场景及简单用法
都是基于文本文件
导入：
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、创建表
CREATE TABLE tt3 ( id int, id_tye varchar(10) , zt_name varchar(40) , zdr_name varchar(20) , zdr_id varchar(10) ); |
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
mysql>select * from tt3; +------+----------+--------------------------------------------+------------------------------+----------+ | id | id_tye | zt_name | zdr_name | zdr_id | | 1 | "IN" | "北京 | KK科技有限公司" | "郭三" | +------+----------+--------------------------------------------+------------------------------+----------+ |
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"
mysql>是elect * from tt3; +------+----------+--------------------------------------------+------------------------------+----------+ | id | id_tye | zt_name | zdr_name | zdr_id | +------+----------+--------------------------------------------+------------------------------+----------+ | 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"###
mysql>select * from tt3; +------+--------+-----------------------------+----------+--------+ | id | id_tye | zt_name | zdr_name | zdr_id | +------+--------+-----------------------------+----------+--------+ | 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> SHOW variables like '%secure%' ; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | require_secure_transport | OFF | | secure_file_priv | | +--------------------------+-------+ |
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"###
mysql> select * from tt14; +----+--------+---------+-----------------------+----------+ | id | id_tye | zt_name | zdr_name | zdr_id | +----+--------+---------+-----------------------+----------+ | 1 | "IN" | "北京 | KK科技有限公司" | "郭三" | | 2 | "IN" | "北京 | KK科技有限公司" | "郭四" | +----+--------+---------+-----------------------+----------+ |
关于字符集：数据文件的字符集与数据库字符集设置不同，导入会乱码。
解决方法：导入时指定处理数据的字符集（或者更改数据库字符集与数据文件一致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> show variables like'%local_infile%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | 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 '>' ;
mysql> select * from tt8; +----+--------+----------------------------+----------+----------+ | id | id_tye | zt_name | zdr_name | zdr_id | +----+--------+----------------------------+----------+----------+ | 28 | OUT | 辽宁KK服务有限公司 | 尚XX | 21361 | | 29 | OUT | 辽宁KK服务有限公司 | 尚XX | 21361 | | 30 | OUT | 辽宁KK服务有限公司 | SYSADMIN | sysadmin | | 31 | OUT | 辽宁KK服务有限公司 | SYSADMIN | sysadmin | +----+--------+----------------------------+----------+----------+ |
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 ',' ;