如何在 MySQL 中导入和导出 CSV / Excel 文件

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
最近更新 2022年01月24日

如何在 MySQL 中导入和导出 CSV / Excel 文件

当你的 MySQL 数据库需要和其他系统协同分享数据时,除了直连数据库外,还有就是使用 Excel、CSV 这种通用数据格式来进行数据共享。本文讲解如何使用命令行直接导入导出 Excel、CSV 文件。在某些场景下,直接使用命令行来操作会更加灵活快捷。

本教程目录

1. MySQL 数据库导出 CSV 文件

使用 into outfile 命令导出数据至 CSV / Excel

2. MySQL 数据库导入 CSV 文件

使用 load data infile 命令导入 CSV / Excel 数据至数据库

3. 使用「卡拉云」制作自己的数据库工具,导出数据

如何使用卡拉云,5分钟搭建一套适应自己工作流的一键导入导出数据库系统。卡拉云无需部署,即插即用,可根据需求灵活调配,适用于后端工程师快速搭建企业内部系统、数据产品经理查看分析数据,数据分析师根据需求快速搭建数据共享平台分享给组内同学协同查看等应用场景。点这里看详情。

1. MySQL 数据库导出 CSV 文件

使用 into outfile 命令导出 MySQL / MariaDB 数据至 CSV / Excel,有时我们需要将数据导出给运营或产品进行数据分析,这时导出 CSV 文件会更加方便使用。

select * from users into outfile '/var/lib/mysql-files/users.csv' FIELDS TERMINATED BY ',';
  • '/var/lib/mysql-files/users.csv' 导出数据保持的文件目录。
  • FIELDS TERMINATED BY ',' 数据以 , 进行分隔。

首先我们登录 MySQL shell,选择需要导出的数据库 ,本教程以use kalacloud_database为例。然后执行导出命令。

MySQL 数据库导出 CSV 文件

导出后会显示成功提示,CD 到导出目录可看到 CSV 文件已导出。

特别提示:into outfile 常见报错 ERROR 1290 错误:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这是因为你的 MySQL 配置了--secure-file-priv 限制了导出文件的存放位置。

你可以使用以下命令来查看具体配置信息

show global variables like '%secure_file_priv%';

secure_file_priv

secure_file_priv 为 NULL 时,表示不允许导入或导出。 secure_file_priv 为路径时(/var/lib/mysql-files/ )时,表示只允许在路径目录中执行。 secure_file_priv 没有值时,表示可在任意目录的导入导出。

你可以打开 my.cnf 或 my.ini,添加以下语句,重启 MySQL server 即可

secure_file_priv=''

扩展阅读:有关把 MySQL 查询出来的结果保存到文件可看我写的这篇《如何在 MySQL 中保存查询结果到文件》教程。

2. MySQL 数据库导入 CSV 文件

有时,我们会碰到其他系统导出的数据文件是 CSV 文件,我们要将这类文件合并到 MySQL 数据库中。下面我们来实践如何使用命令行导入 CSV 文件。

(1)CREATE DATABASEUSE 需要导入的数据库

我们先进入 MySQL Shell,创建一个新数据库,接下来,我们把 CSV 文件中的数据导入到这个空数据库中

root@kalacloud.com:~ $ mysql -uroot -p
mysql> CREATE DATABASE kalacloud_new_database;

(2)新建空表

与导入 *.sql 不同,导入 CSV 文件需要先创建空「表」,我们要根据 CSV 文件中包含的列,使用CREATE TABLE 创建表。

CREATE TABLE `users` (
  `id` VARCHAR(255) NULL,
  `name` VARCHAR(255) NULL,
  `phone` VARCHAR(255) NULL,
  `states` VARCHAR(255) NULL,
  `file_size` VARCHAR(255) NULL,
  `sale` VARCHAR(255) NULL,
  `copyright` VARCHAR(255) NULL,
  `homepage` VARCHAR(255),
  `complaint` VARCHAR(255) NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建议所有字段都设为接纳 NULL 值,也暂时不要设置主键。因为我们并不知道即将导入的 CSV 文件中的数据是否完整和规范,即便是数字,也先使用VARCHAR字段,以防止文件中的数据格式不正确导致的奇怪错误。我们可以在数据导入后,在对数据库进行验证、清理和修正。

创建数据库

上文中我们从 kalacloud_database 中导出了表 users 存放到了/var/lib/mysql-files/users.csv 里,下面我们使用 LOAD DATA INFILE SQL 语句把这个 CSV 文件导入新建的表中。

load data infile '/var/lib/mysql-files/users.csv'
into table users
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

字段使用逗号分隔,字符串用双引号括起来。如果你的 CSV 第一行是标题而非数据,那么还可以添加 IGNORE 1 ROWS; 导入时,忽略第一行。

导入 csv

导入成功后,使用 select * from users; 初步检查表中数据是否正确。

扩展阅读:我们也可以使用 Workbench 这种免费的 MySQL 图形管理工具来操作,了解更多可看我写的这篇《MySQL Workbench 中文指南》教程。

3. 使用「卡拉云」搭建数据协同看板,一键分享或导出数据

一般使用 CSV / Excel 格式导出数据是为了更高的可读性,以及不同平台之间的数据交换。当然你也可以直接使用卡拉云搭建一套数据分享后台工具。卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。

卡拉云快速拖拽生成组件,迅速搭建企业内部工具

卡拉云不仅可以像命令行一样灵活,还可根据自己的工作流,定制开发。无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天,欢迎免费试用卡拉云

卡拉云可一键接入常见的数据库及 API

卡拉云可一键接入常见的数据库及 API

kalacloud 优惠券发放核销 上图为使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台,仅需要简单拖拽即可快速生成前端组件,只要会写 SQL,便可搭建一套趁手的数据库工具。

如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。更多数据库相关教程可访问 卡拉云 查看。

卡拉云联合创始人蒋川

有关 MySQL 教程,可继续拓展学习: