Export from MySQL to CSV

最新推荐文章于 2025-01-15 00:49:33 发布

hqmanwangkai

最新推荐文章于 2025-01-15 00:49:33 发布

阅读量141

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： MySQL

本文链接：https://blog.youkuaiyun.com/hqmanwangkai/article/details/83292978

mysql 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一种使用命令行工具将MySQL数据库中的数据导出为CSV文件的方法。通过组合使用mysql命令和sed命令，可以轻松地将表格数据转换为逗号分隔格式，便于进一步的数据处理或迁移。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Submitted by bawdo2001 on Tue, 2006-04-18 11:54.

Using command line tools to export data from a MySQL database into a CSV file is quite easy. Here's how:

mysql -uexampleuser -pletmein exampledb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Here is some sample output of the above:

"id","username","group","password"
"1","tux","admin","5f4dcc3b5aa765d61d8327deb882cf99"
"2","tlugian","admin","5f4dcc3b5aa765d61d8327deb882cf99"
"3","saiyuki","admin","5f4dcc3b5aa765d61d8327deb882cf99"
"4","fred","staff","5f4dcc3b5aa765d61d8327deb882cf99"
"5","barney","staff","5f4dcc3b5aa765d61d8327deb882cf99"
"6","wilma","admin","5f4dcc3b5aa765d61d8327deb882cf99"

And now for the explanation:

Starting with the MySQL command. I wont explain the -u and -p options they are straight forward (if in doubt man mysql). The -B option will delimit the data using tabs and each row will appear on a new line. The -e option denotes the command to run once you have logged into the database. In this case we are using a simple SELECT statement.

Onto sed. The command used here contains three seperate sed scripts:

s/\t/","/g;s/^/"/ <--- this will search and replace all occurences of 'tabs' and replace them with a ",".

;s/$/"/; <--- This will place a " at the start of the line.

s/\n//g <---- This will place a " at the end of the line.

After running the result set through sed we redirect the output to a file with a .csv extension.

Regards, Bawdo2001