Mysql large tables are split into csv files for export, and mysql is split into csv files for export.

Mysql large tables are split into csv files for export, and mysql is split into csv files for export.

Recently, the company has tens of millions of rows of large tables that need to be split into different csv files according to the city id field.

I wrote an automated shell script.

Under/home/hdh

Linux-xud0:/home/hdh # ll
Total 16
-Rwxrwxrwx 1 root 902 Dec 28 07:47 cf. sh
-Rwxrwxrwx 1 root 6 Dec 28 07:47 id_1.txt
-Rwxrwxrwx 1 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 # delete the file folder generated by the last shell execution
Mkdir/home/hdh/file # create a file folder to store the generated csv file
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 # Save the value of the id field of the table to/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 ', 'enabledby' "'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 ', 'enabledby' "'Lines TERMINATED by' \ r \ n'; # generate the condition where id is used to the corresponding csv file.
Exit
EOF
Done