Shell write script to insert data into mysql

Shell inserts data into mysql

database

In the specified position to insert the column

Insert column 2 after column 1

alter table 表 add 列2 after 列1;

Let the new column be the first column

alter table 表 add new列 first;

Import and export databases

Import: use database; source /.sql
export: mysqldump -u root -p abc> abc.sql abc is the database

Insert picture description here


Insert picture description here


Reference article: mysql export and import sql files under linux

index

Create an index in the first eight fields of the phone

create index phoneindex on 表(phone(8));

Joint Index: (name,phone)
view index

show index from 表;

Delete index

drop index 索引 on 表;

Shell inserts data into mysql

#!/bin/bash -x

#host="127.0.0.1"  
#port="3306"
user="root"  
passwd="123456"  
dbname="myest1"  
tablename="tb_pod"

#mysql -h${host} -P${port} -u${user} -p${passwd}

#创建数据库
create_db_sql="create database if not exists ${dbname};"
mysql -u${user} -e "${create_db_sql}"
mysql -u${user} -e "show databases;"
mysql -u${user} -e "select database();"
mysql -u${user} -e "use ${dbname};"

#创建表
create_table_sql="create table if not exists ${tablename} (
	namespace varchar(255),
    podname varchar(500),
    num varchar(125), 
    status varchar(125),
    red int(11),
    date varchar(125),
    pod_ip varchar(32),
    node_ip varchar(32),
    primary key(podname)
    );"
mysql -u${user} ${dbname} -e "${create_table_sql}"
mysql -u${user} ${dbname} -e "show tables;"
mysql -u${user} ${dbname} -e "desc ${tablename};"
#exit //防止你建表失败,还执行下面的插入数据

echo "Please enter some paramters:"
read opera

#插入数据
if [ $opera == "i" ];then
    cat inserpods.txt | while read line
    do
        podname=`echo $line | awk '{print$2}'`
        caninsert_sql="select * from ${tablename} where podname='${podname}';"
        caninsert=`mysql -u${user} ${dbname} -e "${caninsert_sql}"`
        echo "$caninsert"//执行成功,返回查询列表,执行失败,返回空
        if [[ $caninsert == "" ]] ; then
                namespace=`echo $line | awk '{print$1}'`
                podname=`echo $line | awk '{print$2}'`    
                num=`echo $line | awk '{print$3}'`        
                statu=`echo $line | awk '{print$4}'`            
                red=`echo $line | awk '{print$5}'`                
                date=`echo $line | awk '{print$6}'`                    
                pod_ip=`echo $line | awk '{print$7}'`                        
                node_ip=`echo $line | awk '{print$8}'`                            
                #insert_sql="insert into tb_pod values('aPP','aaaa','${num}','${statu}','${red}','${date}','lll','222');"                                
                insert_sql="insert into tb_pod values('$namespace','$podname','$num','$statu','$red','$date','$pod_ip','$node_ip');"                                    
                mysql -u${user} ${dbname} -e "${insert_sql}"
		fi
	done
fi

Description

Because mysql does not allow the password to be displayed in plain text, I gave up the following -p${passwd} method.

mysql -u${user} -p${passwd} -e "use database ${dbname};"

I used this method. You can insert data into the table by writing a mysql statement in a script. In fact, if you remove the -p, it will also report an error, because you omitted the password. I tried many ways to modify etc/profile and write database users into it. The password, mysqlps=123456, is fine.

mysql -u${user} -e "select database();"

To get the return value of the mysql statement?

caninsert=`mysql -u${user} ${dbname} -e "${caninsert_sql}"`
echo "$caninsert"

I just want to debug the previous piece of code?

Add exit after the previous code