未来星开发团队的官网

【shell mysql 导出数据到csv脚本,完美解决乱码转义符等问题】-费元星

#!/bin/bash
#@author:feiyuanxing 【既然笨到家,就要努力到家】
#@date:2017-12-05
#@E-Mail:feiyuanxing@gmail.com
#@TARGET:一键导出mysql数据到 csv
#@CopyRight:本脚本遵守 未来星开源协议(http://feiyuanxing.com/kaiyuanxieyi/kaiyuanxieyi.html)
#####################################################################################
#### 常量池 ####
IP=127.0.0.1
user=root
database=msyql
passwd=root
port=3306

#导出路径,默认取【费元星版权Q:9715234】当前路径下tmp
basepath=$(cd `dirname $0`; pwd)
data_path=${basepath}/tmp
mkdir -p ${data_path} && cd ${data_path}

#编码
unicode=utf8
#分隔符
separator="|"
#转【费元星版权Q:9715234】义符- 谨记注:能不该不要改
escape_character="\\"
#####################################################################################
MYSQL=`which mysql`
istar=

function baktable(){
statement="use $database;set names ${unicode};select * from $1;"
echo "下载转换$database:$1 ..."
$MYSQL -h"${IP}" -u"${user}" -p"${passwd}" -P"${port}" -e "${statement}" > 1.log

cat 1.log|sed 's/\t/|/g' > $database"_"$1.csv
if [ ""x = ${istar}x ]; then
tar -zcf "$database"_"$1.csv.tar.gz" "$database"_"$1.csv"
rm -rf $database"_"$1.csv

fi

}

function main(){
#show databases in mysql
echo "正在导出库:$database"
if [ -z $database ] ; then
echo "database in mysql:"
echo "*******************"
$MYSQL -h"${IP}" -u"${user}" -p"${passwd}" -P"${port}" -e "set names ${unicode};show databases;"
echo "*******************"
#choose a database
read -t 60 -p "您未定义需要导出的数据库,请在上表选择一个数据库:" database
fi

#show tables in the database

database_tables=`$MYSQL -h"${IP}" -u"${user}" -p"${passwd}" -P"${port}" -e "use ${database};show tables;"`
#echo "test:"$MYSQL -h"${IP}" -u"${user}" -p"${passwd}" -P"${port}" -e "use ${database};show tables;"
echo "*******************"

#choose a table
read -t 60 -p "请选择一个表,默认为导出全部表【点击回车】:" table
read -t 60 -p "是否需要压缩,默认压缩【点击回车】:" istar

if [ -z $table ] ; then
tables_tmp=`echo "${database_tables}" |tail -n +3|sed 's/^[ \t|]*//g' `

for line in `echo ${tables_tmp}`
do
#echo "正在导出表:${line} "
baktable ${line}
done
#baktable
else
baktable ${table}

fi


}

main
#baktable tb_company_base


echo "【费元星版权Q:9715234】Done successfully!Please check the file!"

 

posted @ 2018-01-25 15:38 费元星的博客 阅读(...) 评论(...) 编辑 收藏
未来星开发团队的官网