mysql load脚本_shell 脚本调用mysql load接口

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

$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 <

$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 <

$drop_table_sql;

$into_sql;

exit

EOF

countnum=`mysql -h${ip} -u${user} -p${pass} -Daccess <

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 <

${sql_log};

exit

EOF

rm ${temFile} #移除临时文件

rm ${temlog}