MySQL 导入和导出操作

简介

在这个实验中，你将学习如何执行 MySQL 的导入和导出操作。具体来说，你将探索如何使用 LOAD DATA INFILE 语句将数据从 CSV 文件导入到 MySQL 表中。这包括创建一个名为 employees.csv 的包含员工数据的 CSV 文件、连接到 MySQL 服务器、创建数据库和表，然后使用 LOAD DATA INFILE 命令导入数据。

本实验将引导你完成以下过程：创建 employees.csv 文件、以 root 用户身份连接到 MySQL 服务器、创建 company 数据库和 employees 表，最后使用适当的参数执行 LOAD DATA INFILE 语句，将 CSV 文件中的数据导入到表中。本实验还简要介绍了 LOAD DATA INFILE 语句的组成部分。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/delete("Data Deletion") subgraph Lab Skills mysql/use_database -.-> lab-550909{{"MySQL 导入和导出操作"}} mysql/create_database -.-> lab-550909{{"MySQL 导入和导出操作"}} mysql/create_table -.-> lab-550909{{"MySQL 导入和导出操作"}} mysql/select -.-> lab-550909{{"MySQL 导入和导出操作"}} mysql/insert -.-> lab-550909{{"MySQL 导入和导出操作"}} mysql/delete -.-> lab-550909{{"MySQL 导入和导出操作"}} end

使用 LOAD DATA INFILE 导入 CSV 数据

在这一步中，你将学习如何使用 LOAD DATA INFILE 语句将数据从 CSV 文件导入到 MySQL 表中。这是一种将大量数据加载到数据库中的高效方法。

在开始之前，让我们创建一个名为 employees.csv 的简单 CSV 文件，其中包含员工数据。打开终端并使用 nano 创建该文件：

nano ~/project/employees.csv

现在，将以下数据粘贴到 employees.csv 文件中：

1,John,Doe,[email protected],Sales
2,Jane,Smith,[email protected],Marketing
3,Peter,Jones,[email protected],Engineering
4,Mary,Brown,[email protected],HR

按 Ctrl+X，然后按 Y，再按 Enter 保存文件。

接下来，我们需要连接到 MySQL 服务器。打开终端并执行以下命令，以 root 用户身份连接到 MySQL 服务器。系统可能会提示你输入 root 密码，在默认的 LabEx 虚拟机设置中，该密码通常为空。

mysql -u root -p

如果提示输入密码，而你尚未设置密码，只需按 Enter。

现在，让我们创建一个名为 company 的数据库，并在该数据库中创建一个名为 employees 的表。在 MySQL shell 中执行以下 SQL 语句：

CREATE DATABASE IF NOT EXISTS company;
USE company;

CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department VARCHAR(50)
);

现在我们已经设置好了数据库和表，可以使用 LOAD DATA INFILE 语句从 employees.csv 文件中导入数据。在 MySQL shell 中执行以下 SQL 语句：

LOAD DATA INFILE '/home/labex/project/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

让我们来分解一下这个语句：

LOAD DATA INFILE '/home/labex/project/employees.csv'：指定 CSV 文件的路径。重要提示： 使用文件的绝对路径。
INTO TABLE employees：指定要将数据导入的表。
FIELDS TERMINATED BY ','：指定 CSV 文件中的字段由逗号分隔。
ENCLOSED BY '"'：指定字段由双引号括起来（如果适用）。在我们的例子中，这不是严格必需的，但包含它是一个好习惯。
LINES TERMINATED BY '\n'：指定 CSV 文件中的每一行代表一个新行，并且行由换行符终止。
IGNORE 1 ROWS：如果你的 CSV 文件有标题行，这一点很重要。它告诉 MySQL 跳过文件的第一行。我们的 employees.csv 文件没有标题行，所以我们应该删除这一行。

让我们再次尝试 LOAD DATA INFILE 语句，这次不使用 IGNORE 1 ROWS 子句：

LOAD DATA INFILE '/home/labex/project/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

要验证数据是否已正确导入，请执行以下 SQL 语句：

SELECT * FROM employees;

你应该会在 MySQL shell 中看到 employees.csv 文件中的数据。

最后，退出 MySQL shell：

exit

将查询结果导出为 CSV 文件

在这一步中，你将学习如何将 MySQL 查询的结果导出为 CSV 文件。这对于生成报告、与其他应用程序共享数据或备份数据非常有用。

我们将使用 mysql 命令行客户端的 -e 选项来执行查询，并将输出重定向到一个文件。我们还将使用一些选项将输出格式化为 CSV 文件。

首先，让我们连接到 MySQL 服务器。打开终端并执行以下命令，以 root 用户身份连接到 MySQL 服务器：

mysql -u root -p

如果提示输入密码，而你尚未设置密码，只需按 Enter。

让我们向 employees 表中添加更多数据，以便有更多数据可导出。在 MySQL shell 中执行以下 SQL 语句：

USE company;
INSERT INTO employees (id, first_name, last_name, email, department) VALUES
(5, 'Alice', 'Johnson', '[email protected]', 'Sales'),
(6, 'Bob', 'Williams', '[email protected]', 'Marketing');

现在，退出 MySQL shell：

exit

现在，让我们将 employees 表中的所有数据导出到 ~/project 目录下一个名为 employees_export.csv 的 CSV 文件中。在终端中执行以下命令：

mysql -u root -p -e "USE company; SELECT * FROM employees;" \
  --batch --raw --skip-column-names \
  | sed 's/\t/","/g; s/^/"/; s/$/"/;' > ~/project/employees_export.csv

让我们来分解一下这个命令：

mysql -u root -p -e "USE company; SELECT * FROM employees;"：这部分针对 company 数据库执行 SQL 查询 SELECT * FROM employees。系统会提示你输入 MySQL root 密码。
--batch：此选项告诉 mysql 以批处理模式运行，适用于非交互式使用。
--raw：此选项告诉 mysql 输出数据时不进行任何格式化。
--skip-column-names：此选项告诉 mysql 在输出中跳过列名。
sed 's/\t/","/g; s/^/"/; s/$/"/;'：这部分使用 sed 命令将输出格式化为 CSV 文件。
- s/\t/","/g：将制表符（mysql 输出中的默认字段分隔符）替换为 ","。
- s/^/"/：在每行的开头添加一个双引号。
- s/$/"/：在每行的末尾添加一个双引号。
> ~/project/employees_export.csv：这将命令的输出重定向到 ~/project 目录下一个名为 employees_export.csv 的文件中。

现在，让我们查看 employees_export.csv 文件的内容。使用 nano 打开该文件：

nano ~/project/employees_export.csv

你应该会看到 employees 表中的数据，格式化为 CSV 文件，每个字段都用双引号括起来，并用逗号分隔。

按 Ctrl+X 退出 nano。

解决导入时的编码问题

在这一步中，你将学习在将 CSV 数据导入 MySQL 时如何处理编码问题。当 CSV 文件的字符编码与 MySQL 表的字符编码不匹配时，就会出现编码问题。这可能导致导入的数据出现乱码或错误。

首先，让我们创建一个包含一些可能导致编码问题的特殊字符的 CSV 文件。我们将使用一个带有重音符号的名字。打开终端并使用 nano 创建该文件：

nano ~/project/employees_encoding.csv

现在，将以下数据粘贴到 employees_encoding.csv 文件中：

1,René,Doe,[email protected],Sales
2,Jane,Smith,[email protected],Marketing

按 Ctrl+X，然后按 Y，再按 Enter 保存文件。

现在，让我们尝试在不指定任何编码的情况下将这些数据导入到我们的 employees 表中。首先，我们需要清除表中现有的数据。连接到 MySQL 服务器：

mysql -u root -p

如果提示输入密码，而你尚未设置密码，只需按 Enter。

在 MySQL shell 中执行以下 SQL 语句：

USE company;
TRUNCATE TABLE employees;

这将删除 employees 表中的所有现有行。

现在，退出 MySQL shell：

exit

现在，让我们使用之前使用过的相同的 LOAD DATA INFILE 命令尝试导入 employees_encoding.csv 文件：

mysql -u root -p -e "USE company; LOAD DATA INFILE '/home/labex/project/employees_encoding.csv' INTO TABLE employees FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

输入密码后，再次连接到 MySQL 服务器：

mysql -u root -p

并检查数据：

USE company;
SELECT * FROM employees;

你可能会看到第一个员工的 first_name 显示不正确。这是由于编码问题导致的。退出 MySQL shell：

exit

为了解决这个问题，我们在使用 LOAD DATA INFILE 时需要指定 CSV 文件的字符编码。一种常见的编码是 utf8。修改 LOAD DATA INFILE 语句，使其包含 CHARACTER SET utf8 子句：

mysql -u root -p -e "USE company; LOAD DATA INFILE '/home/labex/project/employees_encoding.csv' INTO TABLE employees CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

现在，再次连接到 MySQL 服务器：

mysql -u root -p

并检查数据：

USE company;
SELECT * FROM employees;

你现在应该会看到第一个员工的 first_name 正确显示为 "René"。

最后，退出 MySQL shell：

exit

在这个例子中，我们使用了 utf8。但是，根据你的 CSV 文件的编码，你可能需要使用不同的字符集。常见的编码包括 latin1、utf8mb4 和 gbk。

验证导入的数据

在这一步中，你将学习如何验证已导入到 MySQL 表中的数据。数据验证是确保数据准确性和完整性的重要步骤。我们将介绍一些使用 SQL 查询进行基本验证的技术。

首先，让我们向 employees 表中添加一些可能存在问题的数据。打开终端并使用 nano 创建文件：

nano ~/project/employees_validation.csv

现在，将以下数据粘贴到 employees_validation.csv 文件中。注意，最后一行的电子邮件格式无效，且部门信息缺失：

3,Invalid,Email,invalid_email,Sales
4,Missing,Department,[email protected],

按 Ctrl+X，然后按 Y，再按 Enter 保存文件。

现在，让我们将这些数据导入到 employees 表中。连接到 MySQL 服务器：

mysql -u root -p

如果提示输入密码，而你尚未设置密码，只需按 Enter。

在 MySQL shell 中执行以下 SQL 语句：

USE company;
LOAD DATA INFILE '/home/labex/project/employees_validation.csv'
INTO TABLE employees
CHARACTER SET utf8
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

现在数据已经导入，让我们进行一些验证检查。

1. 检查无效的电子邮件格式：

我们可以使用正则表达式来检查无效的电子邮件格式。执行以下 SQL 查询：

SELECT * FROM employees WHERE email NOT LIKE '%@%.%';

此查询将返回 email 列中不包含 @ 和 . 字符的所有行，这两个字符是有效电子邮件地址所必需的。你应该会在结果中看到包含 invalid_email 的行。

2. 检查缺失的部门信息：

我们可以通过查找 department 列中的空字符串来检查缺失的部门信息。执行以下 SQL 查询：

SELECT * FROM employees WHERE department = '';

此查询将返回 department 列为空的所有行。你应该会在结果中看到部门信息缺失的行。

3. 检查重复的 ID：

虽然在这个例子中我们没有引入重复的 ID，但这是一个常见的验证检查。你可以使用以下查询来查找重复的 ID：

SELECT id, COUNT(*) FROM employees GROUP BY id HAVING COUNT(*) > 1;

此查询将返回在 employees 表中出现次数超过一次的所有 ID。

4. 检查数据类型不匹配：

MySQL 通常会在导入过程中处理数据类型不匹配的问题，但进行检查仍然是个好主意。例如，如果你有一个应该只包含正值的数字列，你可以检查是否存在负值。

这些只是你如何使用 SQL 查询验证导入数据的几个示例。你需要执行的具体验证检查将取决于你的数据结构和内容。

最后，退出 MySQL shell：

exit

总结

在本次实验中，你学习了如何使用 LOAD DATA INFILE 语句将 CSV 文件中的数据导入到 MySQL 表中。这包括创建一个包含员工数据的 CSV 文件（employees.csv）、连接到 MySQL 服务器、创建一个数据库（company）和一个表（employees），然后使用 LOAD DATA INFILE 命令导入数据。该命令指定了文件路径、表名、字段和行分隔符，以及要忽略的行数。

本次实验还强调了指定正确文件路径的重要性，以及理解 LOAD DATA INFILE 语句中不同子句的重要性，以确保数据导入成功。