字符集gb2312转utf8(用spool)

最新推荐文章于 2026-06-24 10:47:26 发布

转载最新推荐文章于 2026-06-24 10:47:26 发布 · 1k 阅读

本文介绍了一个用于将数据库中特定表的数据从GB2312编码转换为UTF-8编码的Shell脚本，并通过SQL命令批量导出和导入数据的过程。该脚本首先使用SQL*Plus导出指定表的数据，再利用iconv工具进行字符集转换，最后使用SQL*Loader重新加载到数据库。

gb2312转到utf8

spool出来并用iconv转到utf8

#!/bin/bash

for i in `cat table.txt`

/opt/oracle10g/u01/bin/sqlplus -s guomei/xyzmn << EOF

set newpage 0

set linesize 10000

set pagesize 0

set und off

set trimspool on

set colsep,

set echo off

set feedback off

spool ${i}.txt;

select ID||'?t?='||username||'?t?='||NICKNAME||'?t?='||PASSWORD||'?t?='||REGTIME||'?t?='||DISABLED||'?t?='||MOBILE||'?t?='||IDCARD||'?t?='||IDCARD_NAME||'?t?='||ISSET||'?t?='||SERVICE_ENABLE||'?t?='||SERVICE_START_TIME||'?t?='||SERVICE_END_TIME||'?t?='||REALNAME||'?t?='||FACE||'?t?='||SEX||'?t?='||BORN||'?t?='||EMAIL||'?t?='||QUESTION||'?t?='||ANSWER||'?t?='||PROVINCE||'?t?='||CITY||'?t?='||'aaaa'||'?t?='||INFO_UPTIME||'?t?='||SAFE_CODE from ${i} order by id;

spool off;

EOF

iconv -c -f GBK -t UTF-8 ${i}.txt > ${i}_utf8.txt

done;

sqlldr进数据库:

#!/bin/bash

for i in `cat table.txt`

echo "UNRECOVERABLE

load data

infile '/opt/test/${i}_utf8.txt'

TRUNCATE into table ${i}

fields terminated by '?t?='

TRAILING NULLCOLS

(ID,username,NICKNAME,PASSWORD,REGTIME,DISABLED,MOBILE,IDCARD,IDCARD_NAME,ISSET,SERVICE_ENABLE,SERVICE_START_TIME,SERVICE_END_TIME,REALNAME,FACE,SEX,BORN,EMAIL,QUESTION,ANSWER,PROVINCE,CITY,INFO_CHKCODE,INFO_UPTIME,SAFE_CODE)" > ${i}.ctl

/opt/oracle10g/u01/bin/sqlldr guomei/abc123 control=${i}.ctl direct=ture errors=5000000 multithreading=ture readsize=102400000 streamsize=102400000 columnarrayrows=200000 bad=${i}.bad log=${i}.log;

done

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/230160/viewspace-611508/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/230160/viewspace-611508/

标签

#数据库 #操作系统