shell - awk && gsub 的一些使用备忘

#!/bin/bash
#desc:   insert time into database
#date:   2018-01-15
#auth:   cai

HOSTNAME="localhost"
USERNAME="user"
PASSWORD="password"
DBNAME="dbftp"
TABLENAME="ftpfile"
TABLENAME_rec="ftptable"

#show tables of database
#show_db="show databases"
#mysql -h${HOSTNAME} -u${USERNAME} -p${PASSWORD} -e "${show_db}"

#create a temp table
#table_tmp=${TABLENAME}_`date "+%Y%m%d"`
create_table_sql="drop table if exists ${TABLENAME};create table IF NOT EXISTS ${TABLENAME}(id int(10) not null auto_increment primary key,date varchar(40),channel varchar(40),time varchar(40),program varchar(40),record int)"
mysql -h${HOSTNAME} -u${USERNAME} -p${PASSWORD} -D ${DBNAME} -e "${create_table_sql}"

#scanf the Folder files and insert  the date and channel into the temp table

function ReadTables()
{
cd '/root/ftp filefolder'      ##进入需要处理的文件所在的文件夹

for i in `ls *.txt`
do 
    if [-r "$i" ];
    then ReadTables "$i"
    else
        #dt=`cat $i | awk 'gsub(/\//,""){print}'`
        #var=$i 
        #nm_date=${var:15:22}
        dt=`cat $i | awk 'NR==3{if (gsub(/\//,"")) print $1}'`         #2018/01/08-20180108 ，“/”->“ ”
        #if [ "$nm_date" != "$dt" ]; then
        #rm -f $i
        #break 
        #fi   
        channel=`cat $i | awk 'NR==1{if (gsub(/-*(高清)?[a-z]|-/,"")) print $2; else print $2}'`   ##把“高清”和字母去掉    
        rownm=`cat $i | awk 'END{print NR}'`
        let rownm_s=${rownm}-1        ##let it be a number      
        #insert date, channel, time and program into table
        for p in `seq 5 ${rownm_s}`
        do
        tm=`cat $i | awk '{if (NR=='$p') print $1}'`
        pro=`cat $i | awk '{if (NR=='$p') print $2}'` 
        rec=0
        insert_table_sql="insert into ${TABLENAME}(date,channel,time,program,record) values('${dt}','${channel}','${tm}','${pro}','${rec}')"
        mysql -h${HOSTNAME} -u${USERNAME} -p${PASSWORD} -D ${DBNAME} -e "${insert_table_sql}" 
        done    
     fi
done
}
ReadTables ftpfile folder


#update data turn record value 0 to 1
update_sql="update ${TABLENAME} set record=1 where program in (select recordprogram from ${TABLENAME_rec}) and channel in (select recordchannel from ${TABLENAME_rec}) and time between '09:00:00' and '22:00:00'"
mysql -h${HOSTNAME} -u${USERNAME} -p${PASSWORD} -D ${DBNAME} -e "${update_sql}"

#update name1 name2 record value 0 to 1
update_sql1="update ${TABLENAME} set record=1 where channel in ('name1','name2')"

mysql -h${HOSTNAME} -u${USERNAME} -p${PASSWORD} -D ${DBNAME} -e "${update_sql1}"

注：如果需要用shell 处理txt文档，那么awk是很普遍的处理工具。 gsub(/**/,"**")是实现字符串替换的一个工具。

/ /中间是需要查找的字符串，可以使用正则表达式语法。

“ ”里面是需要替换成的内容。