Oracle导出CSV到MySQL：用存储过程+LOAD DATA搭一条可复用同步链路

1. 为什么跨库同步总在 CSV 这一步翻车Oracle 导出 CSV 到 MySQL听起来就是「导一下、灌一下」两件事但真到生产环境里跑十有八九会卡在某个环节Oracle 侧 UTL_FILE 目录没权限、CSV 字符集不是 UTF-8、MySQL 的 secure_file_priv 没配、LOAD DATA 报 1261 列数不匹配、日期字段灌进去变成 0000-00-00。这些问题单独看都不难凑在一起就变成一条断断续续的链路每次同步都要重新踩一遍。这篇要解决的就是把这条链路做成可复用的Oracle 侧用一个存储过程把任意查询结果落成 CSVMySQL 侧用固定的 LOAD DATA 语句灌进去中间靠字段映射和字符集约定把两边对齐。适合做跨库数据同步、报表数据搬运、异构库迁移的开发和运维同学尤其是需要定期跑、不想每次手写导出脚本的场景。核心检索词先摆出来Oracle 存储过程导出 CSV、MySQL LOAD DATA 导入、字段映射、字符集 UTF-8、secure_file_priv、sql_mode 1261 报错。下面按「Oracle 导出 → 文件落地 → MySQL 导入 → 验证 → 排障」的顺序走一遍每一步都给可复制的命令和参数。2. 前置准备目录、权限与 TaoToken 接入位Oracle 侧要落地 CSV绕不开 DIRECTORY 对象。它本质是给数据库进程一个「可写的服务器目录」别名UTL_FILE 只能往这个别名指向的物理路径写文件。所以第一步是在 Oracle 服务器上建目录、给权限再在库里建 DIRECTORY。-- 在 Oracle 服务器上先建物理目录用 oracle 用户或 root 均可注意属主 -- mkdir -p /opt/dump chown oracle:oinstall /opt/dump -- 在 Oracle 库内创建 DIRECTORY 对象 CREATE OR REPLACE DIRECTORY DUMP AS /opt/dump; GRANT READ, WRITE ON DIRECTORY DUMP TO your_user;MySQL 侧要确认secure_file_priv它决定了 LOAD DATA 能读哪个目录的文件。默认可能是 NULL禁止或某个固定路径必须显式配成你放 CSV 的目录。# /etc/my.cnf 的 [mysqld] 段 secure_file_priv /opt改完重启 MySQL再查一次确认生效SHOW GLOBAL VARIABLES LIKE %secure%;如果这条链路里你还想让模型帮你生成字段映射、写 LOAD DATA 语句、或者排查报错可以顺手把 TaoToken 的模型对话接进来当辅助工具它支持多模型切换适合边写边问。地址是 https://taotoken.net/api 模型对话入口在 https://taotoken.net/models?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentmodel_chat 。需要长期跑同步脚本、写 Agent 自动化的可以看 Coding Plan https://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcoding_plan 。API Key 在控制台生成 https://taotoken.net/console/api-keys?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentapi_keys 接入文档在 https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentdoc 。这些只是辅助位真正干活的是下面的存储过程和 LOAD DATA。3. Oracle 侧用存储过程把查询结果落成 CSV3.1 存储过程 SQL_TO_CSV 完整脚本这个存储过程接收三个参数查询语句、DIRECTORY 名、文件名。内部用 DBMS_SQL 动态解析查询、DESCRIBE 出列数再逐行 FETCH 写文件。关键点是把每个字段用双引号包起来、内部双引号转义成两个双引号这样字段里出现逗号也不会串列。CREATE OR REPLACE PROCEDURE SQL_TO_CSV ( P_QUERY IN VARCHAR2, P_DIR IN VARCHAR2, P_FILENAME IN VARCHAR2 ) 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 L_OUTPUT : UTL_FILE.FOPEN(P_DIR, P_FILENAME, W, P_MAX_LINESIZE); EXECUTE IMMEDIATE ALTER SESSION SET NLS_DATE_FORMATYYYY-MM-DD HH24:MI:SS; DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE); DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL); FOR I IN 1 .. L_COLCNT LOOP DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000); L_SEPARATOR : ,; END LOOP; UTL_FILE.NEW_LINE(L_OUTPUT); L_STATUS : DBMS_SQL.EXECUTE(L_THECURSOR); 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; DBMS_SQL.CLOSE_CURSOR(L_THECURSOR); UTL_FILE.FCLOSE(L_OUTPUT); EXCEPTION WHEN OTHERS THEN RAISE; END; /注意这里没有写表头行第一行直接是数据。如果你希望 CSV 带列名可以在DESCRIBE_COLUMNS之后加一段循环把L_DESCTBL(I).COL_NAME写进去但那样 MySQL 侧就要用IGNORE 1 LINES跳过。两种都行关键是两边约定一致。3.2 调用存储过程导出示例建好 DIRECTORY 后直接 exec 调用把查询语句、目录名、文件名传进去SET SERVEROUTPUT ON; EXEC SQL_TO_CSV( SELECT attachguid, attachfilename, contenttype, documenttype, cliengtag, cliengguid, categoryguid, tenantguid FROM frame_attachstorage WHERE attachguid 005a1d80-08c7-4cd1-8fc7-9a4aa739b788, DUMP, aa.CSV );跑完去/opt/dump下看文件用file -i确认字符集file -i /opt/dump/aa.CSV # 期望输出charsetutf-8如果显示charsetunknown或iso-8859-1说明 Oracle 会话的字符集和文件写入编码不一致需要在导出前确认数据库字符集或者用 SQL Developer 导出时手动选 UTF-8。字符集不对后面 LOAD DATA 灌进去就是乱码。3.3 字段映射Oracle 类型到 MySQL 类型跨库同步最容易出问题的就是类型映射。Oracle 的 DATE 到 MySQL 建议先落成 varchar避免时区和格式问题VARCHAR2 统一放宽到 varchar因为 MySQL 的 varchar 长度语义和 Oracle 不完全一样。下面是一张对照表Oracle 类型MySQL 类型说明VARCHAR2(32)varchar(200)放宽长度避免截断VARCHAR2(256)varchar(256)保持原长度VARCHAR2(1000)varchar(1000)保持原长度DATEvarchar(50)导出时已格式化为字符串NUMBERint / decimal按精度选CHAR(1)varchar(1)状态位以原 Oracle 表DZ_ZZ为例适配到 MySQL 的建表语句如下CREATE TABLE dz_zz_test ( certificate_information_id varchar(200) NOT NULL, bm_dz_id varchar(200) DEFAULT NULL, dz_zz_num varchar(256) DEFAULT NULL, zz_num varchar(200) DEFAULT NULL, certified_time varchar(50) DEFAULT NULL, effective_starttime varchar(50) DEFAULT NULL, effective_endtime varchar(50) DEFAULT NULL, certified_units varchar(200) DEFAULT NULL, holder varchar(1000) DEFAULT NULL, holder_type varchar(200) DEFAULT NULL, holder_certificate_type varchar(200) DEFAULT NULL, holder_number varchar(200) DEFAULT NULL, licenses_classification varchar(200) DEFAULT NULL, creat_time varchar(50) DEFAULT NULL, jhpt_update_time varchar(50) DEFAULT NULL, state varchar(1) DEFAULT NULL, picture_url varchar(500) DEFAULT NULL, PRIMARY KEY (certificate_information_id) );4. MySQL 侧LOAD DATA 导入与参数详解4.1 把 CSV 放到 secure_file_priv 目录MySQL 的 LOAD DATA 只能读secure_file_priv指定的目录。假设配的是/opt就把 Oracle 导出的 CSV 拷过去cp /opt/dump/DZ_ZZ.csv /opt/DZ_ZZ.csv chown mysql:mysql /opt/DZ_ZZ.csv权限要给到 mysql 用户否则会报ERROR 13 (HY000): Cant get stat of ...。4.2 LOAD DATA 完整语句LOAD DATA INFILE /opt/DZ_ZZ.csv INTO TABLE dz_zz_test CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \r\n IGNORE 1 LINES (CERTIFICATE_INFORMATION_ID, BM_DZ_ID, DZ_ZZ_NUM, ZZ_NUM, CERTIFIED_TIME, EFFECTIVE_STARTTIME, EFFECTIVE_ENDTIME, CERTIFIED_UNITS, HOLDER, HOLDER_TYPE, HOLDER_CERTIFICATE_TYPE, HOLDER_NUMBER, LICENSES_CLASSIFICATION, CREAT_TIME, JHPT_UPDATE_TIME, STATE, PICTURE_URL);参数逐个说清楚CHARACTER SET utf8mb4告诉 MySQL 文件是 UTF-8 编码和 Oracle 侧导出的字符集对齐。FIELDS TERMINATED BY ,字段以逗号分隔。OPTIONALLY ENCLOSED BY 字段被双引号包裹时去掉引号存储过程导出时每个字段都加了双引号这里必须配上。LINES TERMINATED BY \r\n行分隔符。Windows 风格是\r\nLinux 风格是\n要看导出文件实际是什么。用file命令或cat -A能看出来。IGNORE 1 LINES跳过第一行。如果 CSV 没有表头这行要去掉否则会丢一条数据。最后的列名列表顺序必须和 CSV 里的字段顺序完全一致这是字段映射的核心。4.3 行分隔符的坑LINES TERMINATED BY \r\n和\n搞错表现是整行数据灌进一个字段或者只灌进去第一行。判断方法cat -A /opt/DZ_ZZ.csv | head -3 # 行尾显示 ^M$ 说明是 \r\n # 行尾显示 $ 说明是 \nOracle 的 UTL_FILE.NEW_LINE 在 Linux 上写出来通常是\n但如果你中间用 SQL Developer 导出或者经过 Windows 中转就可能变成\r\n。以实际文件为准别照抄。5. 端到端验证一次导入动作与结果确认链路搭好后跑一次完整验证。步骤是Oracle 导出 → 拷贝文件 → MySQL 导入 → 查行数和抽样。先在 Oracle 侧执行导出EXEC SQL_TO_CSV( SELECT certificate_information_id, bm_dz_id, dz_zz_num, zz_num, certified_time, effective_starttime, effective_endtime, certified_units, holder, holder_type, holder_certificate_type, holder_number, licenses_classification, creat_time, jhpt_update_time, state, picture_url FROM DZ_ZZ, DUMP, DZ_ZZ.csv );拷贝到 MySQL 的 secure_file_priv 目录然后导入LOAD DATA INFILE /opt/DZ_ZZ.csv INTO TABLE dz_zz_test CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n (certificate_information_id, bm_dz_id, dz_zz_num, zz_num, certified_time, effective_starttime, effective_endtime, certified_units, holder, holder_type, holder_certificate_type, holder_number, licenses_classification, creat_time, jhpt_update_time, state, picture_url);导入后确认SELECT COUNT(*) FROM dz_zz_test; SELECT * FROM dz_zz_test LIMIT 3;如果行数和 Oracle 源表一致、抽样字段没有乱码、日期格式正常这条链路就算通了。之后每次同步只需要改存储过程里的查询语句和文件名LOAD DATA 语句基本不用动。6. 常见报错排查1261、字符集、权限6.1 ERROR 1261 Row doesnt contain data for all columns这个报错的意思是某一行字段数和列名列表对不上。常见原因有三个CSV 里有字段包含逗号但没被双引号包住、行分隔符不对导致整行被当成一个字段、或者列名列表顺序和 CSV 不一致。先查 sql_modeSHOW VARIABLES LIKE sql_mode;如果 sql_mode 里有STRICT_TRANS_TABLES字段数不匹配会直接报错。临时关掉可以验证是不是这个原因SET sql_mode ;但生产环境不建议长期关正确做法是修 CSV 或修列名列表。存储过程里已经用双引号包字段、内部双引号转义所以逗号问题基本能避免剩下的就是行分隔符和列顺序。6.2 字符集乱码表现是中文变成问号或乱码。检查三处Oracle 导出文件的字符集file -i、LOAD DATA 里的CHARACTER SET、MySQL 表的字符集。三处都统一成 utf8mb4 最稳。SHOW CREATE TABLE dz_zz_test; -- 确认 DEFAULT CHARSETutf8mb46.3 权限与目录问题ORA-29280: invalid directory pathDIRECTORY 对象没建或路径不对。ORA-29283: invalid file operationOracle 进程对物理目录没写权限。ERROR 13 (HY000): Cant get stat of ...MySQL 读不到文件检查 secure_file_priv 和文件属主。ERROR 29 (HY000): File ... not found文件不在 secure_file_priv 目录下。6.4 日期字段灌进去变成 0000-00-00因为 MySQL 表里日期字段建成了 date 类型而 CSV 里是字符串。把 MySQL 侧日期字段改成 varchar 就能避免这也是前面字段映射表里建议 DATE → varchar 的原因。如果一定要用 date 类型就要保证 CSV 里的格式是YYYY-MM-DD并且 sql_mode 允许。7. 把这条链路固定下来到这里Oracle 存储过程导出 CSV、MySQL LOAD DATA 导入的完整链路就跑通了。可复用的关键是把三样东西固定存储过程 SQL_TO_CSV 不动、LOAD DATA 语句的列名列表不动、字符集和行分隔符约定不动。每次同步只改查询语句和文件名其余照搬。如果后续想让模型帮你根据新表自动生成字段映射和 LOAD DATA 语句或者排查新的报错可以用 TaoToken 的模型对话 https://taotoken.net/models?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentmodel_chat 。要写自动化同步脚本、接 Agent 定时跑的看 Coding Plan https://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentcoding_plan 。API Key 在 https://taotoken.net/console/api-keys?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentapi_keys 接入细节看文档 https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_contentdoc 。链路本身不依赖这些但辅助排查和生成脚本能省不少时间。