shell查询mysql到文件乱码_【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!"