最近公司有一個幾千萬行的大表需要按照城市的id字段拆分成不同的csv文件。
寫了一個自動化的shell腳本
在/home/hdh 下面
linux-xud0:/home/hdh # ll
total 16
-rwxrwxrwx 1 root root 902 Dec 28 07:47 cf.sh
-rwxrwxrwx 1 root root 6 Dec 28 07:47 id_1.txt
-rwxrwxrwx 1 root root 6 Dec 27 20:00 log
vim cf.sh
#!/bin/bash
DATE=$(date +'%Y%m%d')
cd /home/hdh
rm -r /home/hdh/file #刪除上次執行shell 生成的file 文件夾
mkdir /home/hdh/file #創建一個file文件夾用於存放生成的csv 文件
chmod 777 /home/hdh/file -R
echo `mysql -uroot -proot -e "SELECT DISTINCT id FROM xizang.test;" |grep -Evi "id"` > /home/hdh/id_1.txt #把該表的id 字段的值拿到存到 /home/hdh/id_1.txt
#cat id_1.txt |while read line
for line in $(<id_1.txt);
do
# echo ${line} >> id_2.txt
# echo `mysql -uroot -proot -e "SELECT DISTINCT id FROM xizang.test where id='${line}';"` >>id_2.txt
# mysql -uroot -proot -e "SELECT DISTINCT id FROM xizang.test where id='${line}' into outfile '/home/hdh/c.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ;"
mysql -uroot -proot <<EOF
SELECT * FROM xizang.test where id='${line}' into outfile '/home/hdh/file/${line}_c.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ; #以id 為where 的條件，生成到對應的csv 中。
exit
EOF
done
http://xxxxxx/Linuxjc/1182898.html TechArticle
