mysql与oracle通过文件数据互导

最近做一个项目，需要将mysql数据库中的数据导入oracle数据库中，然后从oracle其他表中关联取到数据再导入另一个mysql数据库。因数据量较大（百万级），需要用到文件方式。顺便整理了一下两个数据库文件导入和导出方式。

mysql文件导出

先通过mysql命令行登陆

mysql -uroot -p

然后执行导出查询语句

use db1;
select * from tb1 into outfile 'E:/opt/file/tb1.txt' fields terminated by '|';

如果这时候报错：
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

通过show variables查看secure_file_priv配置：

mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)

这里显示是空，表名不允许导出。

这时候需要修改my.ini，为[mysqld]增加选项：
secure_file_priv=’’
增加后重启mysql，再次查看secure_file_priv配置：

mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)

为空说明允许执行。

secure_file_priv=null 不允许导入导出
secure_file_priv=具体文件路径 只允许从这个路径导入导出
secure_file_priv=空 可以从任意路径导入导出。

设置参数后，再次执行导出查询语句成功。

oracle文件导入

新建ctrl文件，保存到/home/oracle/20210809/in.ctl：

load data
characterset 'AL32UTF8'
infile '/home/oracle/20210809/in_data.txt'
truncate into table tb1
fields terminated by "|"
trailing nullcols
(
	col1,
	col2,
	col3
)

使用oracle用户执行导出命令：

sqlldr dbuser1 control=/home/oracle/20210809/in.ctl log=/home/oracle/20210809/in_data.log data=/home/oracle/20210809/in_data.txt

执行后查看日志文件是否有导入错误的记录。

oracle文件导出

通过spool功能导出。
新建sql文件out_data.sql：

set line 3000
set pagesize 0
set echo off
set feedback off
set heading off
set trimspool off
set trims on
set colsep '|'
set termout on
spool /home/oracle/20210809/out_data.txt --指定导出数据保存的文本文件
select col1||'|'||col2||'|'||col3 from tb2;
spool off
exit

注意：如果导出时直接使用colsep设置的分隔符，导出的文件每个字段都会带空格，处理起来很麻烦，所以最好自己写SQL来定制导出格式。

使用oracle用户执行以上sql：

sqlplus / as sysdba
> @out_data.sql

mysql文件导入

同导出一样，先通过mysql命令行登陆

mysql -uroot -p

然后执行导出查询语句

load data local infile 'E:/opt/tb1.txt' into table tb1 character set utf8 fields terminated by '|' (col1, col2, col3);

mysql文件导入同样需配置secure_file_priv参数。