How Can I Bulk Export All MySQL Tables to CSV Files Using `mysql` and `sed`?

Bulk CSV Export with 'mysqldump'

In the realm of MySQL data management, exporting table contents to CSV format can be a crucial task. While 'mysqldump' is a powerful utility for database backups, it lacks an option to exclusively export every table as CSV. This article explores an alternative approach to accomplish this using a combination of 'mysql' and post-processing.

'mysql' with '-B' Parameter

The crux of our solution lies in leveraging the '-B' parameter in the 'mysql' utility. This parameter instructs the program to display results in a tab-delimited format. By piping the output through 'sed', we can further manipulate the data to create a CSV file with headers.

Bash Script Iteration

To export all tables in a database, we can utilize a Bash script to loop through the list of table names. One approach is to combine the following commands:

mysql -u username -ppassword dbname -sN -e "SHOW TABLES;" | \
for tb in $(mysql -u username -ppassword dbname -sN -e "SHOW TABLES;"); do
    mysql -B -u username -p password database -h dbhost -e "SELECT * FROM $tb;" \
    | sed “s/”/\””/g;s/'/\'/;s/\t/\”,\”/g;s/^/\”/;s/$/\”/;s/\n//g” > "$tb.csv"
done

This script first retrieves the list of tables using 'SHOW TABLES' and stores it in the variable 'tb'. It then iterates over each table name and executes a 'SELECT *' query with the '-B' parameter. The resulting output is processed by 'sed' and stored in a CSV file named after the table.

Conclusion

By combining the '-B' parameter of 'mysql' and 'sed' post-processing, we can effectively export all tables in a MySQL database to CSV format. This approach circumvents the limitations of 'mysqldump' and provides greater flexibility in manipulating the output data.

The above is the detailed content of How Can I Bulk Export All MySQL Tables to CSV Files Using `mysql` and `sed`?. For more information, please follow other related articles on the PHP Chinese website!