#!/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!"
