写一个Shell脚本 操作数据库mysql

运行脚本的时候 传入三个参数 源语缩写 目标语缩写 服务器名称

查询数据库，根据缩写 获得对应语言的id 然后 用id 和引擎名称 查询数据库；如果没有目标引擎，则将该数据插入数据库，新建一个引擎，然后获得该引擎id ，根据引擎id新增节点，如果有目标引擎，直接用该引擎id 新增节点。

#!/bin/bash

#需要按顺序传参 源语 目标语 引擎服务名称
#数据库配置
Host="39.100.224.xxx"
Port="80xx"
User="root"
Pw="123456"
##参数配置
DBNAME=localdeployment_server
INFO_TABLENAME=translation_service_info_copy
NODE_TABLENAME=translation_service_node_copy
LANGUAGE_TABLENAME=language
MYSQL=$(which mysql)
update=$(date +'%Y-%m-%d %H:%M:%S')
#源语 目标语 参数
source_language=${1}
target_language=${2}
#info表参数
source_language_id=""
target_language_id=""
langs="${source_language}2${target_language}"
image_name="yun"
images_alias=$3
is_delete=0
# node表参数
ip_address="127.0.0.1"
user_name="yxx"
pasSWord="yxxxxx"
info_id="0"
ssh_port="22"
is_increment="0"
is_used="1"
#判断传入的所需参数有没有空值，如果有返回error信息并退出脚本
if [ -z  "$1" -o -z "$2" -o -z "$3"  ]
then
echo "请按顺序传参:源语缩写 目标语缩写 引擎名称"
exit
fi

if [ "$1" = "$2" ]
then
echo "输入的源语目标语一样!" 
exit
fi
##远程连接
#select 根据输入信息从language表查询语言id
SELECT_FIELD=" language_abbreviation='${source_language}'"
select_id="`$MYSQL -h${Host} -P${Port} -u${User} -p${Pw} ${DBNAME}<<EOF
select id from $LANGUAGE_TABLENAME where ${SELECT_FIELD};
EOF`"
array=(${select_id})
if [ ! -n "$array" ]
then
echo "源语输入有误！请重新输入"
exit
fi
source_language_id="${array[-1]}"
#查询两遍 防止出现bug 例如 用or拼接查询 当一个语言错误的时候 查询出来只有一个id 此时两个语言id一样就出现bug了 
SELECT_FIELD=" language_abbreviation='${target_language}'"
select_id="`$MYSQL -h${Host} -P${Port} -u${User} -p${Pw} ${DBNAME}<<EOF
select id from $LANGUAGE_TABLENAME where ${SELECT_FIELD};
EOF`"
array=(${select_id})
if [ ! -n "$array" ]
then
echo "目标语输入有误！请重新输入"
exit
fi
target_language_id="${array[-1]}"

if [ "$source_language_id" = "$target_language_id" ]
then
echo "出现BUG了:查询到的源语id和目标语id一样!" 
exit
fi


#select 根据输入信息从数据库里查询 
SELECT_FIELD="source_language_id=${source_language_id} and target_language_id=${target_language_id} and langs='${langs}' and image_name='${image_name}' and images_alias='${images_alias}' and is_delete=0"
select_id="`$MYSQL -h${Host} -P${Port} -u${User} -p${Pw} ${DBNAME}<<EOF
select id from $INFO_TABLENAME where ${SELECT_FIELD};
EOF`"
array=(${select_id})
# 根据查询的结果进行判断  如果查询的结果为空 插入info 再插入node  不为空直接插入 node
if [ -n "$array" ]
then
echo "成功查询到引擎!"
info_id="${array[-1]}"
#insert 不为空 直接插入node
INSERT_FIELD="'$ip_address','$user_name','$password','$info_id',$is_increment,'$update',$is_used,'$update',0,$ssh_port"
insert_node="
`$MYSQL -h${Host} -P${Port} -u${User} -p${Pw} ${DBNAME}<<EOF
insert into $NODE_TABLENAME(ip_address,user_name,password,info_id,is_increment,start_time,is_used,create_time,is_delete,ssh_port) 
 value($INSERT_FIELD);
commit; 
EOF`"
echo "节点插入成功!"
else
echo "未查询到目标引擎!"
echo "开始新增引擎!"
#insert 查询为空 先插入 info 再插入 node
INSERT_FIELD="NULL,${source_language_id},${target_language_id},'${langs}','${image_name}','${images_alias}','$update',NULL,NULL,NULL,${is_delete}"
insert_info="`$MYSQL -h${Host} -P${Port} -u${User} -p${Pw} ${DBNAME}<<EOF
insert into $INFO_TABLENAME(id,source_language_id,target_language_id,langs,image_name,images_alias,create_time,create_name,update_time,update_name,is_delete) value($INSERT_FIELD);
commit; 
EOF`"
echo "引擎添加成功!"
#select  
SELECT_FIELD="source_language_id=${source_language_id} and target_language_id=${target_language_id} and langs='${langs}' and image_name='${image_name}' and images_alias='${images_alias}' and is_delete=0"
select_id="`$MYSQL -h${Host} -P${Port} -u${User} -p${Pw} ${DBNAME}<<EOF
select id from $INFO_TABLENAME where ${SELECT_FIELD};
EOF`"
array=(${select_id})

info_id="${array[-1]}"
#insert 插入node
INSERT_FIELD="'$ip_address','$user_name','$password','$info_id',$is_increment,'$update',$is_used,'$update',0,$ssh_port"
insert_node="
`$MYSQL -h${Host} -P${Port} -u${User} -p${Pw} ${DBNAME}<<EOF
insert into $NODE_TABLENAME(ip_address,user_name,password,info_id,is_increment,start_time,is_used,create_time,is_delete,ssh_port) 
 value($INSERT_FIELD);
commit; 
EOF`"
echo "节点插入成功!"
fi