mysql 大表拆分成csv導出

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