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文件
导入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》
