最近公司有一个几千万行的大表需要按照城市的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