如何用 MySQL 导出 CSV 文件
简介
我们知道,只有极少数的应用可以直接读取 MySQL 的表,相对而言,CSV 文件则是一个更通用的选择。CSV 是一种标准的数据库格式,它用逗号来区分不同的数据行和列,而且它还有个额外的好处就是它具有良好的可阅读性。很多时候你可能需要用 MySQL 导出数据至 csv,然后分享给其它同事,或者处理不同的分隔符,以便其它人可以将 csv 导入至 excel 之类应用。
如果你刚好在尝试构建搜索功能,可以尝试卡拉搜索,无需配置维护即可实现毫秒级搜索,后台可轻松控制排序算法让运营和产品可以轻松调整,降低开发负担
本篇教程将教你如何把一个 MySQL 数据库导出成一个 CSV 文件
准备工作
- 一个可以使用的命令行工具(shell 或 putty 等)
- root 用户或者拥有 sudu 权限用户
- 一个拥有root权限的 MySQL 账号
- 配置好的 phpMyAdmin 账号(可选)
用 phpMyAdmin 从 MySQL 导出 CSV
phpMyAdmin 工具提供了可视化界面来操作你的 MySQL 数据库。你可以用它把任何它所管理的数据库导出成 CSV 文件。步骤如下:
- 登录到 phpMyAdmin 中
- 然后,点击左上角的 Databases 按钮
- 在数据库列表中,选择你想要导出的数据库。在本教程中,我们选择 user 数据库。
- 点击之后,就会展示这个数据库里所有的表,你需要勾选你想要导出的表。
- 点击下方的 Export 按钮
- 不需要改 Export method 选项,把 Format 选项改成 CSV,然后点击 Go。
- 然后会出现一个对话框要求你指定 CSV 文件的保存位置。
用命令行从 Mysql 导出 CSV
你也可以选择表中所有的数据,并指定导出的位置来完成一个简单的导出。
首先打开 MySQL 命令行,并选择到你想要导出的数据库。
输入下列语句:
SELECT * FROM myTable
INTO OUTFILE '\tmp\myExportFile.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
注意,要把 myTable
换成你真正要导出的表名,并且你可以把 \tmp\myExportFile.csv
替换成任何你想要的文件名或路径,但不要丢掉了 .csv
文件名后缀。
小贴士: 这个案例中使用的是一个 Linux 文件路径,如果你在使用 Windows,你可以使用例如 c:/folder/file.csv
作为你的文件路径。
更多导出姿势
如果你想要从表中导出指定的数据,你可以这样写:
SELECT column1, column2, column3, column4
FROM myTable
WHERE column2 = 'value';
把 column1
(以及其他列)换成你表中的实际你想导出的列名。使用 From
命令来指定你是从哪个表导出的。WHERE
语句是可选的,它让你能够只导出符合某些条件的行。最后,把 value
换成符合你要求条件的值。来看代码:
SELECT order_date, order_number, order_status
FROM current_orders
WHERE order_status='pending';
小贴士: WHERE
语句在过滤数据的时候非常好用。
导出并用时间戳标记 CSV 文件
你可以用下列代码来导出一个 CSV 文件,同时还能让文件创建时加一个时间戳标记:
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = '\tmp';
SET @PREFIX = 'myTable';
SET @EXT = '.csv';
SET @CMD = CONCAT("SELECT * FROM myTable INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
"' FIELDS ENCLOSED BY '\"'
TERMINATED BY ';'
ESCAPED BY '\"'","
LINES TERMINATED BY '\r\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;
和前面一样,记得把 myTable
换成你要导出的表的名字。
小贴士: 你可能发现了括号里的 SELECT * FROM
语句,我们把它封装在了一个添加时间戳的函数里了。
连着表头一起导出
可以使用一个 UNION
语句来把表头也导出到导出文件里:
(SELECT 'column1','column2','column3','column4')
UNION
(SELECT column1, column2, column3, column4
FROM myTable
INTO OUTFILE '\tmp\myExportFile.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n')
处理 NULL 值
如果数据库里有任何 null (空)值的时候,下面这些代码可以把这些空值替换成字母 N
,当然你可以替换成任何你想要的字符串:
SELECT column1, column2, IFNULL(column3, 'N/A')
FROM myTable INTO OUTFILE '\tmp\myExportFile.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"' LINES
TERMINATED BY '\r\n');
在这个案例中,IFNULL
语句会查找在 column3
里的所有空值,并把它们替换成字符串 N/A
。
用 mysqldump 从 Mysql 导出 CSV
你也可以使用 mysqldump 工具来把你的数据库导出成 CSV 文件。输入下列命令:
$ mysqldump ––tab=/var/lib/mysql–files/ ––fields–enclosed–by='"' ––fields–terminated–by=',' ––lines–terminated–by='\n' myTable
这条命令会创建一个 myTable
数据库的备份到 /var/lib/mysql-files
。
小贴士: 你的账号必须有访问存储 CSV 文件的目录的权限。虽然你可以随便存储到你想要的位置,但这个位置必须要是 MySQL 有权限访问的位置。关于账号权限请参考: 如何在MySQL中创建新用户并授权权限
用 CSV 引擎从 Mysql 导出 CSV
在某些情况下,你可以用 MySQL 中的 CSV 引擎来做导出。如果一张表已经有索引了,那么你不能这么搞,但是如果这个表用的是 自增主键 的话,那你可以使用这行语句:
ALTER TABLE myTable ENGINE=CSV;
这行语句会把数据库的格式改成 CSV,然后你就可以把 CSV 文件拷到任何系统里去了。
总结
你现在已经学会了多种把 MySQL 导出成 CSV 文件的方法。如果你有 phpMyAdmin 的话,可能更容易上手。
如果你更喜欢命令行的话,那么 SELECT INTO
语句是你最可靠的选择。在某些特定的配置下,使用 CSV 引擎或者 mysqldump 也可以帮你节约大量的时间。
在你的数据库上,如果你需要构建搜索功能的话,也可以尝试卡拉搜索,几行代码就可以接入,可以极大程序免去开发搜索功能和维护搜索服务器的烦恼。