Shell脚本实现数据父子关系定义——目标：将目标数据批量导入MySQL数据库

最新推荐文章于 2023-07-05 14:56:09 发布

原创最新推荐文章于 2023-07-05 14:56:09 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#awk #mysql #shell

Shell脚本专栏收录该内容

3 篇文章

订阅专栏

本文介绍了如何使用Shell脚本处理原始数据，整理父子关系，并将整理后的数据批量导入到MySQL数据库中。通过示例展示了TD_CLASSIFY、TD_ATTRIBUTE_KEY和TD_ATTRIBUTE_VALUE表的结构，以及具体的Shell脚本实现和数据导入过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、原数据raw data

二、目标表格式

三、父子关系整理

四、Shell脚本实现

五、Load data导入到MySQL

一、原数据raw data

文件名：c1.txt

文件内容：

文件名：c3.txt

文件内容：

二、目标表格式

TD_CLASSIFY表：

TD_ATTRIBUTE_KEY表：

TD_ATTRIBUTE_VALUE表：

三、父子关系整理

以第六种分类为例：

四、Shell脚本实现

#!/bin/bash


#======begin 全局变量定义======#
start_n=1
end_n=3
fifthc_id=1
key_id=1
value_id=1
pre_fifth_n=999
#======end 全局变量定义======#


#======begin 函数定义======#


###文件拆分###
File_split()
{
for i in `seq ${start_n} ${end_n}`
do
	if [ -f c${i}.txt ];then
	grep -v -e "五级分类\|属性" c${i}.txt > ${i}.txt
	grep 五级分类    c${i}.txt  >> fifth_classification.txt
	grep 属性      c${i}.txt  >> attribute.txt
	else
	echo -e "\033[31;49;1m第c${i}.txt分类文件不存在！\033[39;39;0m"
	echo "+===================================+"
	continue
	fi
done
}


###五级分类顺序递增处理###
fifth_classification_processing()
{
awk -F'——'  '{print $2,"——",$3}' fifth_classification.txt  | sed "s/ //g" >> end.txt


n=`grep  五级分类  fifth_classification.txt -c`
for i in `seq 1 ${n}`
do
echo 五级分类${i}——   >> front.txt
done


paste front.txt end.txt |sed 's/\t//g' >> fifth_classification_new.txt
rm -f front.txt end.txt fifth_classification.txt
}


###属性顺序递增处理###
attribute_processing()
{
awk -F'——'  '{print $2,"——",$3,"——",$4}'    attribute.txt |sed "s/ //g"      >> end.txt


n=`grep 属性   attribute.txt -c`
for i in `seq 1 ${n}`
do
echo 属性${i}——   >> front.txt
done


paste front.txt end.txt |sed 's/\t//g' >> attribute_new.txt
rm -f front.txt end.txt attribute.txt
}


###文件处理整合###
file_processing()
{
for i in `seq ${start_n} ${end_n}`
do
if [ -f ${i}.txt  ] ;then
rm -f ${i}.txt
fi
done


for i in  'fifth_classification_new.txt attribute_new.txt'
do
rm -f ${i}
done


File_split;
fifth_classification_processing;
attribute_processing;
}


###一级分类###
First_classification()
{
CLASSIFY_NAME=`grep -w  一级分类  $1.txt|awk -F'——' '{print $2}'`
echo -e "${n}\t${CLASSIFY_NAME}\t1\t0"  >> TD_CLASSIFY.txt
}


###二级分类###
Second_classification()
{
a=(`grep 二级分类   $1.txt |awk -F'——' '{print $2}'|sed "s/、/ /g"` )
n1=`echo ${#a[*]}`


for i in `seq -f %02g 1 ${n1}`
do
        j=$((10#${i}-1))
        echo -e "${n}${i}\t${a[${j}]}\t2\t${n}"  >> TD_CLASSIFY.txt 
done
}


###三级分类###
Third_classification()
{
n1=`grep '三级分类'  ${raw_file}.txt|wc -l`


for i in `seq 1 ${n1}`
do
        two=`grep -w "三级分类${i}"  ${raw_file}.txt|awk -F'——'  '{print $2}' `
        PARENT_ID=`grep -w "$two" TD_CLASSIFY.txt |awk '{if ($3==2) {print $1}}' `
        a=(`grep -w "三级分类${i}"  ${raw_file}.txt|awk -F'——'  '{print $3}' |sed "s/、/ /g"`)
        n2=`echo ${#a[*]}`
        for j in `seq -f %02g 1 ${n2}`
        do
        let n3=10#${j}-1
        echo -e "${PARENT_ID}${j}\t${a[${n3}]}\t3\t${PARENT_ID}" >> TD_CLASSIFY.txt
        done
done
}


###四级分类###
Fourth_clssification()
{
n1=`grep '四级分类'  ${raw_file}.txt|wc -l`


for i in `seq 1 ${n1}`
do
        three=`grep -w "四级分类${i}"  ${raw_file}.txt|awk -F'——'  '{print $2}' `
        PARENT_ID=`grep -w "$three" TD_CLASSIFY.txt |awk '{if ($3==3) {print $1}}' `
        a=(`grep -w "四级分类${i}"  ${raw_file}.txt|awk -F'——'  '{print $3}' |sed "s/、/ /g"`)
        n2=`echo ${#a[*]}`
        for j in `seq -f %02g 1 ${n2}`
        do
        let n3=10#${j}-1
        echo -e "${PARENT_ID}${j}\t${a[${n3}]}\t4\t${PARENT_ID}" >> TD_CLASSIFY.txt
        done
done
}


###五级分类###
Fifth_classification()
{
n1=`grep  五级分类  fifth_classification_new.txt -c`


for i in `seq 1 ${n1}`
do
        four=`grep -w "五级分类${i}"  fifth_classification_new.txt|awk -F'——'  '{print $2}' `
        PARENT_ID=`grep -w "$four" TD_CLASSIFY.txt |awk '{if ($3==4) {print $1}}' `
        for a in `grep -w "五级分类${i}"  fifth_classification_new.txt|awk -F'——'  '{print $3}' |sed "s/、/ /g"`
        do
        echo -e "${pre_fifth_n}`seq -f %05g ${fifthc_id} ${fifthc_id}`\t${a}\t5\t${PARENT_ID}"  >> TD_CLASSIFY.txt
        let fifthc_id=${fifthc_id}+1
        done
done
}


###检查分类是否有重复###
check_classification_repeated()
{
pra=$1
awk '{print $2}' TD_CLASSIFY.txt |sort|uniq -c|grep -v 1 > /tmp/Repeated_classification_list
if [ $? -eq 0 ];
then
	if [ ${pra} -eq ${pre_fifth_n} ];
	then
	echo -e "\033[31;49;1m五级分类有重复分类，详情如下： \033[39;49;0m\n"
        echo -e "\033[31;49;1m出现次数 分类名称\033[39;49;0m"
        cat /tmp/Repeated_classification_list
        exit
	else
	echo -e "\033[31;49;1m第${pra}分类有重复分类，详情如下： \033[39;49;0m\n"
	echo -e "\033[31;49;1m出现次数 分类名称\033[39;49;0m"
	cat /tmp/Repeated_classification_list
	exit
	fi
else
	if [ ${pra} -eq ${pre_fifth_n} ];then
	echo -e "五级分类没有重复类问题 ^_^\n"
	else
	echo -e "第${pra}分类没有重复类问题 ^_^\n"
	fi
fi
}


###检查分类是否有父类###
check_classification_parentid()
{
pra=$1
n1=`wc -l TD_CLASSIFY.txt |cut -d' ' -f1`
n2=`awk 'BEGIN {n=0} {if (length($4)!=0) n+=1} END {print n} ' TD_CLASSIFY.txt `
if [ ${n1} -ne ${n2} ];
then
	if  [ ${pra} -eq ${pre_fifth_n} ];
	then
	echo -e "\033[31;49;1m五级分类对应的上联分类不存在\033[39;49;0m"
        awk '{if (length($4)==0) {print "分类"$1,$2}}' TD_CLASSIFY.txt
        exit
	else
	echo -e "\033[31;49;1m第${pra}分类对应的上联分类不存在\033[39;49;0m"
	awk '{if (length($4)==0) {print "分类"$1,$2}}' TD_CLASSIFY.txt
	exit
	fi
else
	if  [ ${pra} -eq ${pre_fifth_n} ];
        then
	echo -e "五级分类对应的上联分类均存在 ^_^\n"
	echo "+===================================+"
	else
	echo -e "第${pra}分类对应的上联分类均存在 ^_^\n"
	echo "+===================================+"
	fi
fi
}


###获取属性的KEY值###
get_attribute_key()
{
if [ -f TD_ATTRIBUTE_KEY.txt ] ;then
rm -f TD_ATTRIBUTE_KEY.txt
fi


n1=$((`grep '属性'  attribute_new.txt|wc -l`-1))


a=(`grep "属性" attribute_new.txt | awk -F'——'   '{print $3}'  `)


for i in `seq -f %02g 0 ${n1}`
do
        CLASSIFY_NAME=`grep -w "属性${key_id}" attribute_new.txt|awk -F'——'  '{print $2}'`
        CLASSIFY_ID=`grep -w "${CLASSIFY_NAME}" TD_CLASSIFY.txt|awk '{print $1}'`
        echo -e "${key_id}\t${a[10#${i}]}\t${CLASSIFY_ID}"  >> TD_ATTRIBUTE_KEY.txt
        key_id=$((${key_id}+1))
done
}


###获取属性的VALUE值###
get_attribute_value()
{
if [ -f TD_ATTRIBUTE_VALUE.txt ];then
rm -f TD_ATTRIBUTE_VALUE.txt
fi


n1=`grep '属性'  attribute_new.txt|wc -l`


for i in `seq 1 ${n1}`
do
        CLASSIFY_NAME=`grep -w "属性${i}" attribute_new.txt|awk -F'——'  '{print $2}'`
        KEY_NAME=`grep -w "属性${i}" attribute_new.txt|awk -F'——'  '{print $3}'`
        CLASSIFY_ID=`grep -w "${CLASSIFY_NAME}" TD_CLASSIFY.txt|awk '{print $1}'`
        combined_index=`echo -e "${KEY_NAME}\t${CLASSIFY_ID}"`
        KEY_ID=`grep -w "${combined_index}" TD_ATTRIBUTE_KEY.txt|awk '{print $1}'`
        a=(`grep -w "属性${i}" attribute_new.txt | awk -F'——'   '{print $4}'|sed "s/、/ /g" `)
        n2=$((`echo ${#a[*]}`-1))
        for tt in `seq 0 ${n2}`
        do
        echo -e "${value_id}\t${a[10#${tt}]}\t${KEY_ID}" >> TD_ATTRIBUTE_VALUE.txt
        let value_id=${value_id}+1
        done
done
}


###检查属性key对应的上联分类是否存在###
check_attribute_key()
{
n1=`wc -l TD_ATTRIBUTE_KEY.txt |cut -d' ' -f1`
n2=`awk 'BEGIN {n=0} {if (length($3)!=0) n+=1} END {print n}' TD_ATTRIBUTE_KEY.txt`
if [ ${n1} -ne ${n2} ];then
echo "下列属性KEY对应的上联分类不存在"
awk '{if (length($3)==0) {print "属性KEY"$1,$2}}' TD_ATTRIBUTE_KEY.txt
exit
else
echo -e "TD_ATTRIBUTE_KEY is ok ^_^\n"
echo "+===================================+"
fi
}


###检查属性value对应的上联属性key是否存在###
check_attribute_value()
{
n1=`wc -l TD_ATTRIBUTE_VALUE.txt |cut -d' ' -f1`
n2=`awk 'BEGIN {n=0} {if (length($3)!=0) n+=1} END {print n}' TD_ATTRIBUTE_VALUE.txt`
if [ ${n1} -ne ${n2} ];then
echo "下列属性VALUE对应的上联属性KEY不存在"
awk '{if (length($3)==0) {print "属性VALUE_ID"$1,$2}}' TD_ATTRIBUTE_VALUE.txt
exit
else
echo -e "TD_ATTRIBUTE_VALUE is ok ^_^\n"
fi
}


###一级&二级&三级&四级分类###
First_to_Fourth_classification()
{
if [ -f TD_CLASSIFY.txt ];then
rm -f TD_CLASSIFY.txt
fi


for raw_file in `seq ${start_n} ${end_n}`
do
	###获得第几个分类###
	if [ -f ${raw_file}.txt ];then
	n=`grep -w 第几大分类  ${raw_file}.txt|cut -d'：' -f2`


	First_classification ${raw_file};
	Second_classification ${raw_file};
	Third_classification ${raw_file};
	Fourth_clssification ${raw_file};
        check_classification_repeated  ${n};
        check_classification_parentid  ${n};
	else
	continue
	fi


done
}


###单独第五级分类###
single_fifth_classification()
{
Fifth_classification;
check_classification_repeated ${pre_fifth_n};
check_classification_parentid ${pre_fifth_n};
}


###属性KEY###
attribute_key()
{
get_attribute_key;
check_attribute_key;
}


###属性VALUE###
attribute_value()
{
get_attribute_value;
check_attribute_value
}
#======end 函数定义======#




#======begin 函数调用======#
file_processing;
First_to_Fourth_classification;
single_fifth_classification;
attribute_key;
attribute_value
#======end 函数调用======#

五、load data导入到MySQL

以TD_CLASSIFY为例：

mysql> load data infile '/home/xxm/11111/TD_CLASSIFY.txt' into table yy.TD_CLASSIFY fields terminated by '\t';
Query OK, 50 rows affected (0.00 sec)
Records: 50 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from yy.TD_CLASSIFY limit 3;
+-----+------------------+----------------+-----------+
| ID | CLASSIFY_NAME | CLASSIFY_LEVEL | PARENT_ID |
+-----+------------------+----------------+-----------+
| 6 | first_classify | 1 | 0 |
| 601 | second_classify1 | 2 | 6 |
| 602 | second_classify2 | 2 | 6 |
+-----+------------------+----------------+-----------+
3 rows in set (0.00 sec)