Oracle、MySQL 数据导出到CSV方法对比

MySQL 数据库

版本:8.0.37

MySQL 将数据导出到CSV

mysql> create database cjc;
mysql> use cjc;
create table t1 (id int,name varchar(10),poetry varchar(30),primary key(id));
insert into t1 values(1,'a','别人笑我太疯癫，');
insert into t1 values(2,'b','我笑他人看不穿，');
insert into t1 values(3,'c','不见五陵豪杰墓，');
insert into t1 values(4,'d','无花无酒锄作田。');

mysql> select * from t1;
+----+------+--------------------------+
| id | name | poetry                   |
+----+------+--------------------------+
|  1 | a    | 别人笑我太疯癫，         |
|  2 | b    | 我笑他人看不穿，         |
|  3 | c    | 不见五陵豪杰墓，         |
|  4 | d    | 无花无酒锄作田。         |
+----+------+--------------------------+
4 rows in set (0.00 sec)

检查参数

mysql> show variables like 'secure%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /mysqldata/3308/file/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

导出

select id,name,poetry 
INTO OUTFILE '/mysqldata/3308/file/t1.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' 
from t1;
Query OK, 4 rows affected (0.00 sec)

默认没有表头

[mysql@cjc-db-01 file]$ cat t1.csv 
"1","a","别人笑我太疯癫，"
"2","b","我笑他人看不穿，"
"3","c","不见五陵豪杰墓，"
"4","d","无花无酒锄作田。"

添加表头

select 'id','name','poetry'
union all
select id,name,poetry 
INTO OUTFILE '/mysqldata/3308/file/t1_01.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' 
from t1;
Query OK, 5 rows affected, 1 warning (0.29 sec)

[mysql@cjc-db-01 file]$ cat t1_01.csv 
"id","name","poetry"
"1","a","别人笑我太疯癫，"
"2","b","我笑他人看不穿，"
"3","c","不见五陵豪杰墓，"
"4","d","无花无酒锄作田。"

查看csv文件

MySQL 导入CSV

mysql> create table t2 like t1;
mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

修改参数

set global local_infile=ON;

导入

[mysql@cjc-db-01 socket]$ mysql -ucjc -p --socket=/mysqldata/3308/socket/mysql.sock --local-infile
mysql> use cjc;
LOAD DATA LOCAL INFILE '/mysqldata/3308/file/t1.csv'
INTO TABLE t2
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 0 LINES;

Query OK, 4 rows affected (0.32 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

查看数据

mysql> select * from t2;
+----+------+--------------------------+
| id | name | poetry                   |
+----+------+--------------------------+
|  1 | a    | 别人笑我太疯癫，         |
|  2 | b    | 我笑他人看不穿，         |
|  3 | c    | 不见五陵豪杰墓，         |
|  4 | d    | 无花无酒锄作田。         |
+----+------+--------------------------+
4 rows in set (0.00 sec)

Oracle 数据库

版本:11.2.0.4.0

Oracle 将数据导出到CSV

sqlplus cjc/******
create table t1 (id int,name varchar(10),poetry varchar(30),primary key(id));
insert into t1 values(1,'a','别人笑我太疯癫，');
insert into t1 values(2,'b','我笑他人看不穿，');
insert into t1 values(3,'c','不见五陵豪杰墓，');
insert into t1 values(4,'d','无花无酒锄作田。');
commit;
SQL> select * from t1;

	ID NAME       POETRY
---------- ---------- ------------------------------
         1 a          别人笑我太疯癫，
         2 b          我笑他人看不穿，
         3 c          不见五陵豪杰墓，
         4 d          无花无酒锄作田。

导出 t1 表到 CSV

方法一:使用||连接

SQL> SELECT '"'||ID||'"'||','||'"'||NAME||'"'||','||'"'||POETRY||'"' CSV FROM T1;

CSV
--------------------------------------------------------------------------------
"1","a","别人笑我太疯癫，"
"2","b","我笑他人看不穿，"
"3","c","不见五陵豪杰墓，"
"4","d","无花无酒锄作田。"

添加表头

SELECT '"'||'ID'||'"'||','||'"'||'NAME'||'"'||','||'"'||'POETRY'||'"' CSV FROM DUAL
UNION ALL
SELECT '"'||ID||'"'||','||'"'||NAME||'"'||','||'"'||POETRY||'"' CSV FROM T1;

CSV
--------------------------------------------------------------------------------
"ID","NAME","POETRY"
"1","a","别人笑我太疯癫，"
"2","b","我笑他人看不穿，"
"3","c","不见五陵豪杰墓，"
"4","d","无花无酒锄作田。"

导出到文件

set heading off
spool /home/oracle/t1.csv
SELECT '"'||'ID'||'"'||','||'"'||'NAME'||'"'||','||'"'||'POETRY'||'"' CSV FROM DUAL
UNION ALL
SELECT '"'||ID||'"'||','||'"'||NAME||'"'||','||'"'||POETRY||'"' CSV FROM T1;
exit;

编辑文件，去掉开头SQL语句和结尾exit等部分

vi /home/oracle/t1.csv

查看最终文件

cat /home/oracle/t1.csv
"ID","NAME","POETRY"                                                            
"1","a","别人笑我太疯癫，"                                                           
"2","b","我笑他人看不穿，"                                                          
"3","c","不见五陵豪杰墓，"                                                          
"4","d","无花无酒锄作田。"  

方法二:使用自定义存储过程

说明:下面内容参考自：
捉到一只萤火虫—《oracle导出数据到 csv文件方法整理》

https://blog.csdn.net/qq_34902590/article/details/81777900

创建导出目录

select * from dba_directories;
create or replace directory cjc_dir as '/home/oracle/tmp';
grant read,write,execute on directory cjc_dir to cjc;

创建存储过程:

CREATE OR REPLACE PROCEDURE SQL_TO_CSV
(
 P_QUERY IN VARCHAR2, -- PLSQL文
 P_DIR IN VARCHAR2, -- 导出的文件放置目录
 P_FILENAME IN VARCHAR2 -- CSV名
 )
 IS
  L_OUTPUT UTL_FILE.FILE_TYPE;
  L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
  L_COLUMNVALUE VARCHAR2(4000);
  L_STATUS INTEGER;
  L_COLCNT NUMBER := 0;
  L_SEPARATOR VARCHAR2(1);
  L_DESCTBL DBMS_SQL.DESC_TAB;
  P_MAX_LINESIZE NUMBER := 32000;
BEGIN
  --OPEN FILE
  L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);
  --DEFINE DATE FORMAT
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
  --OPEN CURSOR
  DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL);
  --DUMP TABLE COLUMN NAME
  FOR I IN 1 .. L_COLCNT LOOP
    UTL_FILE.PUT(L_OUTPUT,L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"'); --输出表字段
    DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000);
    L_SEPARATOR := ',';
  END LOOP;
  UTL_FILE.NEW_LINE(L_OUTPUT); --输出表字段
  --EXECUTE THE QUERY STATEMENT
  L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
 
  --DUMP TABLE COLUMN VALUE
  WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP
    L_SEPARATOR := '';
    FOR I IN 1 .. L_COLCNT LOOP
      DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);
      UTL_FILE.PUT(L_OUTPUT,
                  L_SEPARATOR || '"' ||
                  TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE, '"', '""')) || '"');
      L_SEPARATOR := ',';
    END LOOP;
    UTL_FILE.NEW_LINE(L_OUTPUT);
  END LOOP;
  --CLOSE CURSOR
  DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
  --CLOSE FILE
  UTL_FILE.FCLOSE(L_OUTPUT);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
/

导出

SQL> EXEC SQL_TO_CSV('SELECT ID,NAME,POETRY FROM CJC.T1','CJC_DIR','T1_01.CSV');
PL/SQL procedure successfully completed.

查看导出数据:

[oracle@cjc-db-01 tmp]$ cat T1_01.CSV 
"ID","NAME","POETRY"
"1","a","别人笑我太疯癫，"
"2","b","我笑他人看不穿，"
"3","c","不见五陵豪杰墓，"
"4","d","无花无酒锄作田。"

导入CSV

vi t1.ctl
LOAD DATA
CHARACTERSET UTF8          
INFILE 'T1_01.CSV' 
BADFILE 't.bad'
DISCARDFILE 't.dsc'
INTO TABLE t3
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(ID,NAME,POETRY)

sqlplus cjc/******
create table t3 as select * from t1 where 1=2;
alter table t3 modify poetry varchar2(50);
select * from t3;

导入

[oracle@cjc-db-01 tmp]$ sqlldr userid=cjc/a control=/home/oracle/tmp/t1.ctl log=/home/oracle/tmp/t1.log

SQL*Loader: Release 11.2.0.4.0 - Production on Sat Jul 20 13:51:13 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5

乱码了?

SQL> select * from t3;

	ID NAME       POETRY
---------- ---------- --------------------------------------------------
         1 a          锟斤拷锟叫?拷太锟斤拷锟斤拷
         2 b          锟叫?拷锟斤拷锟斤拷锟斤拷锟斤拷锟
         3 c          锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
         4 d          锟斤拷锟斤拷锟狡筹拷锟斤拷锟斤拷

总结:
可以看到，不使用图形化工具的前提下，MySQL更容易实现CSV的导出等操作。
参考:
捉到一只萤火虫—《oracle导出数据到 csv文件方法整理》

https://blog.csdn.net/qq_34902590/article/details/81777900

###chenjuchao 20240720###
欢迎关注我的公众号《IT小Chen》