shell编程系列24--shell操作数据库实战之利用shell脚本将文本数据导入到mysql中
利用shell脚本将文本数据导入到mysql中
需求1：处理文本中的数据，将文本中的数据插入到mysql中
1010 jerry 1991-12-13 male
1011 mike 1991-12-13 female
1012 tracy 1991-12-13 male
1013 kobe 1991-12-13 male
1014 allen 1991-12-13 female
1015 curry 1991-12-13 male
1016 tom 1991-12-13 female
# 创建表结构和student一样结构的student1表
MariaDB [school]> create table student1 like student;
[root@localhost shell]# cat data.txt
1010 jerry 1991-12-13 male
1011 mike 1991-12-13 female
1012 tracy 1991-12-13 male
1013 kobe 1991-12-13 male
1014 allen 1991-12-13 female
1015 curry 1991-12-13 male
1016 tom 1991-12-13 female
# 编写导入数据脚本
[root@localhost shell]# cat import_mysql.sh
#!/bin/bash
#
user="dbuser"
password="123456"
host="10.11.0.215"
mysql_conn="mysql -h"$host" -u"$user" -p"$password""
cat data.txt | while read id name birth sex
do
$mysql_conn -e "INSERT INTO school.student1 values('$id','$name','$birth','$sex')"
done
[root@localhost shell]#
[root@localhost shell]# sh import_mysql.sh
[root@localhost shell]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 53
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from school.student1;
+------+--------+------------+--------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+--------+
| 1010 | jerry | 1991-12-13 | male |
| 1011 | mike | 1991-12-13 | female |
| 1012 | tracy | 1991-12-13 | male |
| 1013 | kobe | 1991-12-13 | male |
| 1014 | allen | 1991-12-13 | female |
| 1015 | curry | 1991-12-13 | male |
| 1016 | tom | 1991-12-13 | female |
+------+--------+------------+--------+
# 导入数据可以用load，有时候有一些特殊需求比如插入s_id大于1014的行，这个时候就需要使用 shell 语句进行过滤了
[root@localhost shell]# cat import_mysql.sh
#!/bin/bash
#
user="dbuser"
password="123456"
host="10.11.0.215"
mysql_conn="mysql -h"$host" -u"$user" -p"$password""
cat data.txt | while read id name birth sex
do
# 有插入条件
if [ $id -gt 1014 ];then
$mysql_conn -e "INSERT INTO school.student1 values('$id','$name','$birth','$sex')"
fi
done
[root@localhost shell]# sh import_mysql.sh
[root@localhost shell]# sh operate_mysql.sh school "select * from student1"
s_id s_name s_birth s_sex
1015 curry 1991-12-13 male
1016 tom 1991-12-13 female
需求2：
2021|hao|1989-12-21|male
2022|zhang|1989-12-21|male
2023|ouyang|1989-12-21|male
2024|li|1989-12-21|female
[root@localhost shell]# cat import_mysql-2.sh
#!/bin/bash
#
user="dbuser"
password="123456"
host="10.11.0.215"
# IFS是系统自带的变量，分隔符 input filre saperator
IFS="|"
cat data2.txt | while read id name birth sex
do
# 注意，当使用|类似这种特殊符号时，需要将mysql命令不写成命令，否则会报错
mysql -u"$user" -p"$password" -h"$host" -e "INSERT INTO school.student2 values('$id','$name','$birth','$sex')"
done
#
# 使用冒号: 分隔也没有问题
[root@localhost shell]# cat data3.txt
2025:hao:1989-12-21:male
2026:zhang:1989-12-21:male
2027:ouyang:1989-12-21:male
2028:li:1989-12-21:female
[root@localhost shell]# sh operate_mysql.sh school "select * from student2"
+------+--------+------------+--------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+--------+
| 2025 | hao | 1989-12-21 | male |
| 2026 | zhang | 1989-12-21 | male |
| 2027 | ouyang | 1989-12-21 | male |
| 2028 | li | 1989-12-21 | female |
+------+--------+------------+--------+
[root@localhost shell]# cat import_mysql-2.sh
#!/bin/bash
#
user="dbuser"
password="123456"
host="10.11.0.215"
#mysql_conn="mysql -h"$host" -u"$user" -p"$password""
# IFS是系统自带的变量，分隔符 input filre saperator
IFS=":"
cat data3.txt | while read id name birth sex
do
mysql -u"$user" -p"$password" -h"$host" -e "INSERT INTO school.student2 values('$id','$name','$birth','$sex')"
done
