利用Shell将MySQL数据表导出为csv文件

矫情吗；* 2022-06-05 00:56 1514阅读 0赞

完整的Shell代码如下：

#!/bin/bash
    MYSQL=`which mysql`
    
    #show databases in mysql
    echo "database in mysql:"
    echo "*******************"
    $MYSQL -u root -p****** << EOF
    show databases;
    EOF
    echo "*******************"
    #choose a database 
    read -t 60 -p "choose a database:" database
    
    #show tables in the database
    echo "tables in $database"
    echo "*******************"
    $MYSQL -u root -p****** << EOF
    use $database
    show tables;
    EOF
    echo "*******************"
    
    #choose a table
    read -t 60 -p "choose a table:" table
    
    statement="use $database;select * from $table;"
    
    #write the table into 1.log file
    $MYSQL -u root -p****** >1.log << EOF
    $statement
    EOF
    
    echo "Downloading $table from $database in mysql..."
    sleep 1
    echo "now converting it to csv file..."
    sleep 1
    
    #cat the 1.log file and convert it to csv file
    cat 1.log | while read line
    do
    echo $line | tr " " ","
    done > $database"_"$table.csv
    
    sleep 1
    #remove the temporal file 1.log
    rm -rf 1.log
    
    #echo the infomation
    echo "Convert $table into $database"_"$table.csv."
    sleep 1
    echo "Done successfully!Please check the file!"

其中$MYSQL -u root -p\*\*\*\*\*\*的“\*\*\*\*\*\*”为mysql登陆密码。  
操作如下：  
![这里写图片描述][SouthEast]  
去文档中查看文件是否存在：  
![这里写图片描述][SouthEast 1]  
文件里面内容如下：  
![这里写图片描述][SouthEast 2]  
Bingo，操作成功！

--------------------

本次分享到此结束，欢迎大家批评与交流~~

[SouthEast]: /images/20220605/e3ca5b6782ff4a96bf7a1ab20584401f.png
[SouthEast 1]: /images/20220605/48979a195f0e4b95b3a2b8f79434ec9b.png
[SouthEast 2]: /images/20220605/b28cc33d62744a2d8fde41b30c055445.png