#!/bin/bash
#数据库用户名
USER="root"
DATABASE="jian"
#用户表
TABLE_PERSON="person"
#用户教育经历表
TABLE_EDUCATION="person_education_experience"
#用户工作经历表
TABLE_WORK="person_work_experience"
create_database_table(){
#创建数据库
mysql -u $USER << EOF 2>/dev/null
CREATE DATABASE $DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci
EOF
[ $? -eq 0 ] && echo "created DB" || echo "DB already exists"
#创建用户表
mysql -u $USER $DATABASE << EOF 2>/dev/null
CREATE TABLE $TABLE_PERSON(
id bigint(11) NOT NULL AUTO_INCREMENT,
name varchar(32) DEFAULT '' COMMENT '姓名',
sex int(4) DEFAULT '0' COMMENT '性别',
birthday varchar(10) DEFAULT '' COMMENT '生日',
degree int(11) DEFAULT '0' COMMENT '学历',
top_education_field varchar(32) DEFAULT '' COMMENT '最高学历专业',
marital_status int(11) DEFAULT '1' COMMENT '婚姻 1未婚',
current_country varchar(32) DEFAULT '' COMMENT '国家[居住]',
current_province varchar(32) DEFAULT '' COMMENT '省[居住]',
current_city varchar(32) DEFAULT '' COMMENT '市[居住]',
current_district varchar(32) DEFAULT '' COMMENT '区[居住]',
current_address varchar(32) DEFAULT '' COMMENT '详细地址[居住]',
household_registration varchar(32) DEFAULT '' COMMENT '户口',
id_card varchar(32) DEFAULT '' COMMENT '身份证',
mobile varchar(32) DEFAULT '' COMMENT '手机号',
email varchar(32) DEFAULT '' COMMENT 'email',
expected_country varchar(32) DEFAULT '' COMMENT '国家[期望]',
expected_province varchar(32) DEFAULT '' COMMENT '省[期望]',
expected_city varchar(32) DEFAULT '' COMMENT '市[期望]',
expected_district varchar(32) DEFAULT '' COMMENT '区[期望]',
expected_address varchar(32) DEFAULT '' COMMENT '详细地址[期望]',
expected_salary_min int(11) DEFAULT '0' COMMENT '最低薪资[期望]',
expected_salary_max int(11) DEFAULT '0' COMMENT '最高薪资[期望]',
current_situation varchar(64) DEFAULT '' COMMENT '目前状况',
standard_job_industry varchar(64) DEFAULT '' COMMENT '从事行业',
standard_job_function varchar(64) DEFAULT '' COMMENT '从事职业',
expected_job_nature varchar(32) DEFAULT '' COMMENT '工作性质[期望]',
expected_job_function varchar(64) DEFAULT '' COMMENT '期望从事职业',
expected_job_industry varchar(64) DEFAULT '' COMMENT '期望从事行业',
self_evaluation varchar(255) DEFAULT '' COMMENT '自我评价',
PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
EOF
[ $? -eq 0 ] && echo "Created table $TABLE_PERSON success" || echo "Table $TABLE_PERSON already exist"
#创建教育经历表
mysql -u $USER $DATABASE << EOF 2>/dev/null
CREATE TABLE $TABLE_EDUCATION(
id int(11) NOT NULL AUTO_INCREMENT,
person_id int(11) NOT NULL DEFAULT '0',
start_time varchar(10) NOT NULL DEFAULT '',
end_time varchar(10) NOT NULL DEFAULT '',
institution_name varchar(32) NOT NULL DEFAULT '',
education_field varchar(64) NOT NULL DEFAULT '',
education_description varchar(255) NOT NULL DEFAULT '',
education_level int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8
EOF
[ $? -eq 0 ] && echo "Created table $TABLE_EDUCATION success" || echo "Table $TABLE_EDUCATION already exist"
#创建工作经历表
mysql -u $USER $DATABASE << EOF 2>/dev/null
CREATE TABLE $TABLE_WORK(
id int(11) NOT NULL AUTO_INCREMENT,
person_id int(11) NOT NULL DEFAULT '0',
start_time varchar(10) NOT NULL DEFAULT '',
end_time varchar(10) NOT NULL DEFAULT '',
company_name varchar(32) NOT NULL DEFAULT '',
job_title varchar(64) NOT NULL DEFAULT '',
job_description varchar(255) NOT NULL DEFAULT '',
salary_min int(11) NOT NULL DEFAULT '0',
salary_max int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8
EOF
[ $? -eq 0 ] && echo "Created table $TABLE_WORK success" || echo "Table $TABLE_WORK already exist"
}
parse_json_file(){
#json文件名字
FILE=$1
#解析手机号
mobile=`cat $FILE|jq '.data.mobile'`
echo "mobile:$mobile"
if [ -z "$mobile" ]
then
return
fi
mobile=`echo $mobile | sed 's/\"//g'`
mobile=${mobile:3}
echo "================"
if [ ${#mobile} -lt 11 ];then
return
fi
len=${#mobile}
echo "========数组长度========"$len
#判断是否在数据库中已经存在,若存在就不插入了
key=$mobile
exists=`redis-cli -h 127.0.0.1 -p 6379 EXISTS ${key}`
if [ $exists -eq 1 ]
then
return
else
result=`redis-cli -h 127.0.0.1 -p 6379 set ${key} ${name}`
[ $? -eq 0 ] && echo "insert into redis success" || echo "insert into redis fail"
fi
#解析名字
name=`cat $FILE |jq '.data.name_chi'`
echo "name:$name"
#解析性别
sex=`cat $FILE |jq '.data.sex'`
echo "sex:$sex"
#解析生日
birthday=`cat $FILE |jq '.data.birthday'`
echo "birthday:$birthday"
#解析学历
degree=`cat $FILE |jq '.data.degree'`
echo "degree:$degree"
#解析专业
top_education_field=`cat $FILE |jq '.data.top_education_field_from_chi_cv'`
echo "top_eucation_field:$top_education_field"
#解析婚姻
marital_status=`cat $FILE |jq '.data.marital_status'`
echo "marital_status:$marital_status"
#解析当前所在国家、省、市、区、详细地址
current_country=`cat $FILE |jq '.data.chi_current_address_detail[0].country'`
current_province=`cat $FILE |jq '.data.chi_current_address_detail[0].province'`
current_city=`cat $FILE |jq '.data.chi_current_address_detail[0].city'`
current_district=`cat $FILE |jq '.data.chi_current_address_detail[0].district'`
current_address=`cat $FILE |jq '.data.chi_current_address_detail[0].address'`
echo "current_country:$current_country,current_province:$current_province,current_city:$current_city,current_district:$current_district,current_address:$current_address"
#解析户口
household_registration=`cat $FILE|jq '.data.household_registration_from_chi_cv'`
echo "household_registration:$household_registration"
#解析身份证
id_card=`cat $FILE|jq '.data.id_card'`
echo "id_card:$id_card"
#解析电子邮箱
email=`cat $FILE|jq '.data.email'`
echo "email:$email"
#解析期望的国家、省、市、区、详细地址
expected_country=`cat $FILE |jq '.data.chi_expected_address_detail[0].country'`
expected_province=`cat $FILE |jq '.data.chi_expected_address_detail[0].province'`
expected_city=`cat $FILE |jq '.data.chi_expected_address_detail[0].city'`
expected_district=`cat $FILE |jq '.data.chi_expected_address_detail[0].district'`
expected_address=`cat $FILE |jq '.data.chi_expected_address_detail[0].address'`
echo "expected_country:$expected_country,expected_province:$expected_province,expected_city:$expected_city,expected_district:$expected_district,expected_address:$expected_address"
#解析期望薪资最低、最高
expected_salary_min=`cat $FILE |jq '.data.expected_salary_min'`
expected_salary_max=`cat $FILE |jq '.data.expected_salary_max'`
echo "expected_salary_min:$expected_salary_min,expected_salary_max:$expected_salary_max"
#解析目前状况
current_situation=`cat $FILE |jq '.data.current_situation_from_chi_cv'`
echo "current_situation:$current_situation"
#解析从事行业
standard_job_industry=`cat $FILE |jq '.data.standard_job_industry_from_chi_cv'`
echo "standard_job_industry:$standard_job_industry"
#解析从事职业
standard_job_function=`cat $FILE |jq '.data.standard_job_function_from_chi_cv'`
echo "standard_job_function:$standard_job_function"
#解析期望从事行业
expected_job_industry=`cat $FILE |jq '.data.expected_job_industry_from_chi_cv'`
echo "expected_job_industry:$expected_job_industry"
#解析期望从事职业
expected_job_function=`cat $FILE |jq '.data.expected_job_function_from_chi_cv'`
echo "expected_job_function:$expected_job_function"
#解析自我评价
self_evaluation=`cat $FILE |jq '.data.chi_self_evaluation'`
echo "self_evaluation:$self_evaluation"
#将解析的数据插入到数据库中
result="`mysql -u $USER $DATABASE << EOF 2>/dev/null
INSERT INTO $TABLE_PERSON (name,sex,birthday,degree,top_education_field,marital_status,current_country,current_province,current_city,current_district,current_address,household_registration,id_card,mobile,email,expected_country,expected_province,expected_city,expected_district,expected_address,expected_salary_min,expected_salary_max,current_situation,standard_job_industry,standard_job_function,expected_job_industry,expected_job_function,self_evaluation) VALUES ($name,$sex,$birthday,$degree,$top_education_field,$marital_status,$current_country,$current_province,$current_city,$current_district,$current_address,$household_registration,$id_card,$mobile,$email,$expected_country,$expected_province,$expected_city,$expected_district,$expected_address,$expected_salary_min,$expected_salary_max,$current_situation,$standard_job_industry,$standard_job_function,$expected_job_industry,$expected_job_function,$self_evaluation);
SELECT LAST_INSERT_ID() id;
EOF`"
if [ $? -eq 0 ]
then
echo "Insert data success1"
id=`echo $result | awk '{print $2}'`
#解析教育经历
education_length=`cat $FILE |jq '.data.education_experience_chi|length'`
for((i=0;$i<$education_length;i=`expr $i+1`))
do
start_time=`cat $FILE | jq '.data.education_experience_chi['$i'].education_experience_start_time'`
end_time=`cat $FILE | jq '.data.education_experience_chi['$i'].education_experience_end_time'`
institution_name=`cat $FILE | jq '.data.education_experience_chi['$i'].institution_name'`
education_field=`cat $FILE | jq '.data.education_experience_chi['$i'].education_field'`
education_description=`cat $FILE | jq '.data.education_experience_chi['$i'].education_description'`
education_level=`cat $FILE | jq '.data.education_experience_chi['$i'].education_level'`
echo "start_time:$start_time,end_time:$end_time,institution_name:$institution_name,education_field:$education_field,education_description:$education_description,education_level:$education_level"
mysql -u $USER $DATABASE << EOF 2>/dev/null
INSERT INTO $TABLE_EDUCATION (person_id,start_time,end_time,institution_name,education_field,education_description,education_level) VALUES ($id,$start_time,$end_time,$institution_name,$education_field,$education_description,$education_level)
EOF
[ $? -eq 0 ] && echo "Insert education data success" || echo "Insert education data fail"
done
#解析教育经历
work_length=`cat $FILE |jq '.data.work_experience_chi|length'`
for((i=0;$i<work_length;i=`expr $i+1`))
do
start_time=`cat $FILE | jq '.data.work_experience_chi['$i'].work_experience_start_time'`
end_time=`cat $FILE | jq '.data.work_experience_chi['$i'].work_experience_end_time'`
company_name=`cat $FILE | jq '.data.work_experience_chi['$i'].company_name'`
job_title=`cat $FILE | jq '.data.work_experience_chi['$i'].job_title'`
job_description=`cat $FILE | jq '.data.work_experience_chi['$i'].job_description'`
salary_min=`cat $FILE | jq '.data.work_experience_chi['$i'].job_salary_min'`
salary_max=`cat $FILE | jq '.data.work_experience_chi['$i'].job_salary_max'`
echo "start_time:$start_time,end_time:$end_time,company_name:$company_name,job_title:$job_title,job_description:$job_description,salary_min:$salary_min,salary_max:$salary_max"
mysql -u $USER $DATABASE << EOF 2>/dev/null
INSERT INTO $TABLE_WORK (person_id,start_time,end_time,company_name,job_title,job_description,salary_min,salary_max) VALUES ($id,$start_time,$end_time,$company_name,$job_title,$job_description,$salary_min,$salary_max)
EOF
[ $? -eq 0 ] && echo "Insert work data success" || echo "Insert work data fail"
done
else
echo "Insert data fail"
fi
}
#调用生成数据库和表的方法
create_database_table
#调用执行解析的方法
#parse_json_file "_任瑞-807734-20180-807735-20180831180431-20180910171218.json"
read_dir(){
for file in `ls -a $1`
do
if [ -d $1"/"$file ]
then
if [[ $file != '.' && $file != '..' ]]
then
read_dir $1"/"$file
fi
else
parse_json_file $1"/"$file
fi
done
}
remove_blank(){
dir=$1
find $dir -type f -name "* *" -print |
while read name; do
na=$(echo $name | tr ' ' '_')
if [[ $name != $na ]]; then
mv "$name" $na
fi
done
echo "去空格完成"
}
remove_blank json_20180830
#测试目录 test
read_dir json_20180830