有个项目采用mysql 数据库,因历史原因库中有编码为latin1的表,现需要统一转换为utf8并保留原表内容。单纯修改表编码无法满足需求,结合shell 可高效完成此任务。以下本人所用的方法及部份实现代码--有更好办法请留言。个人直接用dump出来再转utf8会乱码。
采用into outfile 倒出表记录
select * from table into outfile '/tmp/file';
结合shell 获取latin1编码的表:
mysql -h$dbhost -u$dbuser -p$dbpass -D${dbbash_u} <table_char
show table status;
SHOW
#取出表,字符编码
awk '{print$1,$16}' table_char >char
#取出latin
grep 'latin1' char >table_latin1
接着对属于latin1表进行for处理
count_latin1=`wc -l table_latin1 |awk '{print$1}'`
for (( i=1;i<=${count_latin1};i++ ));
do
echo $i
l_table=`sed -n ''$i'p' table_latin1 |awk '{print$1}'`
echo "开始导$l_table表"
mysql -h$dbhost -u$dbuser -p$dbpass -D${dbbash_u} <
select * from $l_table into outfile '/tmp/outfile/latin/${l_table}_outfile_latin1';
INTO
done
接着:将latin1格式文件转换成utf8:
最后:将转换后的数据批量导回对应的表:
for (( i=1;i<=${count_latin1};i++ ));
do
echo $i
l_table=`sed -n ''$i'p' table_latin1 |awk '{print$1}'`
echo "开始导$l_table表"
mysql -P3307 -h$dbhost_u -u$dbuser -p$dbpass -D${dbbash_u} <
delete from $l_table;
load data infile '/tmp/outfile/latin/${l_table}_outfile_utf8' into table ${l_table};
INTO
done
echo "latin导入完成"
阅读(4242) | 评论(0) | 转发(0) |