shell 读取数据表 并赋值给变量

利用shell下载数据

#!/bin/bash

HOST="127.0.0.1"
USER="root"
PASSWORD="password"
PORT=3306

MYSQL="mysql -h ${HOST} -u ${USER} -P ${PORT}  -p ${PASSWORD}"

# 方法一
while read -a row
do
    echo "${row[0]} == ${row[1]}"
done< <(echo "select id,url from table_name limit 30;" | ${MYSQL})

while read ID URL
do
    echo "${ID} == ${URL}"
done< <(echo "select id,url from table_name limit 30;" | ${MYSQL})

字符串MD5

#!/bin/bash
echo -n $1|md5sum|cut -d ' ' -f1

获取代理IP信息

#!/bin/bash
MYSQL="mysql -h127.0.0.1 -uroot -ppassword -P3306"
sql="select addres from table_name"
result="$($MYSQL -e "$sql")"
dump_data=./ip.log > $dump_data
echo -e "$result" > $dump_data
# 这里要额外注意，echo -e "$result" > $dump_data的时候一定要加上双引号，不让导出的数据会挤在一行

下载Shell

#!/bin/bash

LOG_FILE="down.log";
STATE_INFO=""
PASSWORDSS='password'
#MYSQL=`which mysql`
MYSQL="mysql -h127.0.0.1 -uroot -p${PASSWORDSS} -P3306"

if [[ $1 -lt 1 ]]; then
    echo "请输入ID开始值"; exit -1;
fi

if [[ $2 -lt 1 ]]; then
    echo "请输入ID结束值"; exit -1;
fi

# 获取代理IP
PROXY_LIST=[]
j=0
while read line
do
    PROXY_LIST[j]=${line}
    j=$(($j+1))
done < ip.log
PROXY_NUM=${#PROXY_LIST[@]}

# 循环下载数据
while read ID URL
do
    if [[ $ID -gt 0 ]]; then
        # 获取文件路径
        PATH_ID=$(printf "%08d" ${ID})
        PATH_0=${PATH_ID:0:2}
        PATH_1=${PATH_ID:2:2}
        PATH_2=${PATH_ID:4:2}

        # 获取原始文件扩展名
        FILE_NAME=$(basename "${URL}")
        EXTENSION="${FILE_NAME##*.}"

        # 获取md5文件名称
        PIC_MD5=$(source ./md5.sh ${PATH_ID})

        # 保存目录
        SAVE_PATH_DIR="pic/${PATH_0}/${PATH_1}/${PATH_2}/"

        # 创建文件夹
        mkdir -p ${SAVE_PATH_DIR}

        # 新文件名
        NEW_FILE_NAME="${ID}-${PIC_MD5}.${EXTENSION}"

        # 数据库保存名
        DB_PATH_DIR="${PATH_0}/${PATH_1}/${PATH_2}/${NEW_FILE_NAME}"

        # 随即获取代理IP
        PROXY_KEY=$(($RANDOM%${PROXY_NUM}))
        PROXY=${PROXY_LIST[${PROXY_KEY}]}

        # 下载图片保存到本地
        wget -q -e "http_proxy=http://${PROXY}" -O ${SAVE_PATH_DIR}${NEW_FILE_NAME} ${URL} --no-check-certificate

        # 下载成功更新数据库信息
        if [[ $? -eq 0 ]]; then
            # 更新数据库
            echo "update table_name set url='${DB_PATH_DIR}' where id=${ID};" | ${MYSQL}

            STATE_INFO="Success"
         else
            STATE_INFO="Fail"
        fi

        # 保存日志信息
        echo "ID:${ID} ${STATE_INFO}"
        echo $(date +%Y"."%m"."%d" "%k":"%M":"%S) ${ID} ${STATE_INFO} >> ${LOG_FILE}
    fi
done< <(echo "select id,url from table_name where id between $1 and $2 order by id asc" | ${MYSQL})