shell read mysql_shell mysql 处理数据小结

shell mysql 处理数据小结，shell中子进行变量的传递看了好久..

学习一下另一种思维方式

[root@ccsafe cc]# cat productinfo.sh

#!/bin/bash

#stat data for check product system#cc@ccsafe.com

#2008-10-15

readonly -a g_DBConf=("-hlocalhost" "-uuser" "-ppwd")

readonly -a g_DBConf_remote=("-hccsafe" "-uuser2" "-ppwd2")

readonly g_resultFile="/usr/local/product_info_svr/dbshell/check_url/stat_result.txt"

readonly g_LogFile="/home/saferoot/cc/productinfo.log"

readonly g_tmp="/tmp/tmpurl"

g_checkday=0

g_checkedday=0

g_productday=0

g_productdomain=0

g_producturls=0

g_urldomain=""

function stat_day_domain_num()

{

cat $g_tmp |awk -F '/' '{print $3}'|sort |uniq -c |sort -nr |head -20|while read LINE

#while read -r LINE

do

g_producturls=$(echo $LINE |awk '{print $1}')

g_urldomain=$(echo $LINE |awk '{print $2}')

echo "$g_producturls $g_urldomain"

local sql="INSERT INTO xlstat.webdomainstat(urldomain,producturls) VALUES('$g_urldomain','$g_producturls');"

#echo $sql

g_sql=`printf "%s%s" "$g_sql" "$sql"`

#sub process!!

echo "$g_sql">/tmp/tmpsql

#echo "$sql" |mysql ${g_DBConf_remote[@]}

done

read g_g_sql

# echo $g_g_sql

echo "$g_g_sql" |mysql ${g_DBConf_remote[@]}

}

function export_day_product_url()

{

(( $# != 1 )) && {

echo "function stat_day_import_num() parameter[$@] error"

return 1;

}

#local -r date=$(date -d"-$n day" +"%Y%m%d")

local -r date=$(date -d"-1 day" +"%Y%m%d")

if [ -f "$g_tmp" ];then

rm -rf $g_tmp

fi

#local -r sql="select page_url from url_src.gougou_problem_$date into outfile '$g_tmp'"

local sql="select page_url from url_src.gougou_problem_$date into outfile '/tmp/tmpurl'"

echo "$sql"

echo "$sql" |mysql ${g_DBConf[@]}

}

function stat_day_product_num()

{

g_productdomain=$(cat $g_tmp |awk -F '/' '{print $3}'|sort |uniq -c |wc -l)

g_checkday=$(tail -1 $g_resultFile |awk '{print $3}')

g_checkedday=$(tail -1 $g_resultFile |awk '{print $4}')

g_productday=$(tail -1 $g_resultFile |awk '{print $5}')

echo "$g_checkday $g_checkedday $g_productday $g_productdomain"

local sql="INSERT INTO xlstat.webproductstat(checkday,checkedday,productday,productdomain) VALUES('$g_checkday','$g_checkedday','$g_productday','$g_productdomain')"

echo "$sql" |mysql ${g_DBConf_remote[@]}

}

function main()

{

local start_t=`date +"%s"`

local end_t=0

echo ""

echo "------------------`date +"[%Y%m%d %T]"`"

echo "`date +"[%Y%m%d %T]"` shell $0 start at.."

local -r n=${1:-1}

export_day_product_url $n

stat_day_product_num

stat_day_domain_num

echo "$(date +"[%Y%m%d %H:%M:%S]") over" >> ${g_LogFile}

wait

end_t=`date +"%s"`

echo "`date +"[%Y%m%d %T]"` shell end. use-time:$(( end_t-start_t ))sec. "

}

main $@

[root@ccsafe cc]#