shell编程系列24--shell操作数据库实战之利用shell脚本将文本数据导入到mysql中

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