欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

shell 脚本调用mysql load接口

程序员文章站 2022-06-09 22:41:11
...
#!/bin/sh
ip="ip"
user="user"
pass="password"
begintime=$(date '+%Y-%m-%d %H:%M:%S') #脚本开始执行时间
temlog="temlog.txt"             #日志记录
if [ ! -f "$temlog" ]; then 
touch ${temlog}
else
rm ${temlog}
touch ${temlog}
fi
if [ ! -n "$1" ]; then      #判断code参数是否为空
echo "code param is null!">>${temlog}
fi
if [ ! -n "$2" ]; then      #判断日期时间参数是否为空
echo "date param is null!">>${temlog}
fi
select_sql="select * from tablename where id='"$1"'"
result_info=`mysql -h${ip} -u${user} -p${pass} -Daccess <<EOF | tail -n +2
$select_sql;
exit
EOF`
FILE_NAME=`echo $result_info |cut -d " " -f1`    #对应的文件名前缀
TABLE_NAME=`echo $result_info |cut -d " " -f2`   #对应的表名前缀
restablename=`echo $result_info |cut -d " " -f3`   #对应的原始表名
newtablename=${TABLE_NAME}${2}            #对应表名
filename=${FILE_NAME}${2}.txt             #对应文件名
myFile="path"${filename}         
if [ ! -f "$myFile" ]; then               #判断对应文件是否存在
echo "ERROR:"${myFile}" not found!">>${temlog}
fi
temFile=tem${filename}
if [ ! -f "$temFile" ]; then
touch ${temFile}
else
rm ${temFile}
touch ${temFile}
fi
error_info=`cat ${temlog}`
if [ ! -z "${error_info}" ]; then      #判断是否异常
endtime=$(date '+%Y-%m-%d %H:%M:%S')
sql_log="insert into tablename(t1,t2,t3) values('"${begintime}"','"${endtime}"','"${error_info}"');"
mysql -h${ip} -u${user} -p${pass} -Daccess <<EOF
 $sql_log;
 exit
EOF
rm ${temFile}            #移除临时文件
rm ${temlog}
exit 0
fi
iconv 2>>${temlog} -f gbk -t utf-8 ${filename} > ${temFile}   #文件编码转换
drop_table_sql="drop table if exists ${newtablename};"
create_table_sql="create table if not exists ${newtablename} as select * from ${restablename} where 1=2;"
into_sql="LOAD DATA LOCAL INFILE '${temFile}' REPLACE INTO TABLE ${newtablename} character set utf8 FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' IGNORE 1 LINES"
mysql 2>>${temlog} -h${ip} -u${user} -p${pass} -Daccess <<EOF
$drop_table_sql;
$into_sql;
exit
EOF
countnum=`mysql -h${ip} -u${user} -p${pass} -Daccess <<EOF | tail -n +2
select count(1) from ${newtablename};
exit
EOF`
error_info=`cat ${temlog}`
if [ -z "${error_info}" ]; then
error_info="OK"
fi
endtime=$(date '+%Y-%m-%d %H:%M:%S')
sql_log="insert into tablename(t1,t2,t3) values('"${begintime}"','"${endtime}"',""${error_info}"\");"
mysql -h${ip} -u${user} -p${pass} -Daccess <<EOF
 ${sql_log};
 exit
EOF
rm ${temFile}            #移除临时文件
rm ${temlog}