添加便签

笔记

Section 1 / 6

导入/导出 CSV、JSON、XML

了解如何使用 CSV、JSON 和 XML 格式在 MySQL 中导入和导出数据，以及如何处理文件权限、编码和数据类型不匹配等常见挑战。

检查点

导入/导出 CSV、JSON、XML

了解如何使用 CSV、JSON 和 XML 格式在 MySQL 中导入和导出数据，以及如何处理文件权限、编码和数据类型不匹配等常见挑战。

代码示例

1SupplierName,Email,Phone,Address
2"GadgetPros Inc.","contact@gadgetpros.com","555-0011","100 Tech Road"
3"Fashion Hub","info@fashionhub.com","555-0022","200 Style Street"

代码示例

1{
2  "CustomerName": "Eda Engin",
3  "Email": "eda.engin@example.com",
4  "Phone": "555-1234",
5  "Address": "789 AI Lane",
6  "CustomerTier": "New"
7}

代码示例

1[
2  {
3    "ProductName": "Smart Lightbulb",
4    "CategoryID": 1,
5    "Price": 19.99,
6    "Stock": 150
7  },
8  {
9    "ProductName": "Yoga Book",
10    "CategoryID": 2,
11    "Price": 24.50,
12    "Stock": 80
13  }
14]

代码示例

1<products>
2  <product ProductID="1">
3    <ProductName>Laptop</ProductName>
4    <CategoryID>1</CategoryID>
5    <Price>1200.00</Price>
6    <Stock>50</Stock>
7  </product>
8  <product ProductID="2">
9    <ProductName>Smartphone</ProductName>
10    <CategoryID>1</CategoryID>
11    <Price>800.00</Price>
12    <Stock>30</Stock>
13  </product>
14</products>

代码示例

1LOAD DATA INFILE 'file_path/input_file.csv'
2INTO TABLE table_name
3FIELDS TERMINATED BY ','        -- 指定字段分隔符
4OPTIONALLY ENCLOSED BY '"'    -- 指定包围字符串值的字符
5LINES TERMINATED BY '\n'        -- 指定行结束标记
6IGNORE 1 ROWS;                  -- 跳过标题行（如果存在）

代码示例

1CategoryName
2"Outdoor Gear"
3"Pet Supplies"

代码示例

1-- 确保 input_file.csv 在 MySQL 可访问的目录中（检查 secure_file_priv 变量）
2-- 示例：/var/lib/mysql-files/new_categories.csv 或 C:/mysql_uploads/new_categories.csv
3
4-- new_categories.csv 内容：
5-- CategoryName
6-- "Outdoor Gear"
7-- "Pet Supplies"
8
9LOAD DATA INFILE '/path/to/file/new_categories.csv' -- 修改此路径
10INTO TABLE Categories
11FIELDS TERMINATED BY ','
12OPTIONALLY ENCLOSED BY '"'
13LINES TERMINATED BY '\n'  -- 如需 Windows 风格换行符，请使用 '\r\n'
14IGNORE 1 ROWS             -- 跳过标题行
15(CategoryName);           -- 指定要加载数据的表列
16
17-- 验证导入
18SELECT * FROM Categories WHERE CategoryName IN ('Outdoor Gear', 'Pet Supplies');

代码示例

1sudo nano /var/lib/mysql-files/new_categories.csv

代码示例

1CategoryName
2"Outdoor Gear"
3"Pet Supplies"

代码示例

1sudo service mysql start; mysql

代码示例

1USE OnlineStore;

代码示例

1LOAD DATA INFILE '/var/lib/mysql-files/new_categories.csv'
2INTO TABLE Categories
3FIELDS TERMINATED BY ','
4OPTIONALLY ENCLOSED BY '"'
5LINES TERMINATED BY '\n'
6IGNORE 1 ROWS
7(CategoryName);

代码示例

1SELECT * FROM Categories WHERE CategoryName IN ('Outdoor Gear', 'Pet Supplies');

Terminal

代码示例

1LOAD XML INFILE 'file_path/input_file.xml'
2INTO TABLE table_name
3ROWS IDENTIFIED BY '<row_tag>'; -- 指定包裹每行数据的 XML 标签

代码示例

1<suppliers_data>
2  <supplier>
3    <SupplierName>Office Solutions Ltd.</SupplierName>
4    <Email>sales@officesolutions.com</Email>
5    <Phone>555-0033</Phone>
6    <Address>300 Business Park</Address>
7    <OnTimeDeliveryRate>97.5</OnTimeDeliveryRate>
8    <AvgLeadTime>4</AvgLeadTime>
9  </supplier>
10  <supplier>
11    <SupplierName>Green Planet Goods</SupplierName>
12    <Email>info@greenplanet.com</Email>
13    <Phone>555-0044</Phone>
14    <Address>400 Eco Avenue</Address>
15    <OnTimeDeliveryRate>99.0</OnTimeDeliveryRate>
16    <AvgLeadTime>3</AvgLeadTime>
17  </supplier>
18</suppliers_data>

代码示例

1-- new_suppliers.xml 内容如上所述
2
3LOAD XML INFILE '/path/to/file/new_suppliers.xml' -- 修改此路径
4INTO TABLE Suppliers
5ROWS IDENTIFIED BY '<supplier>'; -- 标签 <supplier> 包裹每个记录
6
7-- 验证导入
8SELECT * FROM Suppliers WHERE SupplierName LIKE '%Solutions Ltd.' OR SupplierName LIKE '%Planet Goods';

1sudo nano /var/lib/mysql-files/new_suppliers.xml

代码示例

1<suppliers_data>
2  <supplier>
3    <SupplierName>Office Solutions Ltd.</SupplierName>
4    <Email>sales@officesolutions.com</Email>
5    <Phone>555-0033</Phone>
6    <Address>300 Business Park</Address>
7    <OnTimeDeliveryRate>97.5</OnTimeDeliveryRate>
8    <AvgLeadTime>4</AvgLeadTime>
9  </supplier>
10  <supplier>
11    <SupplierName>Green Planet Goods</SupplierName>
12    <Email>info@greenplanet.com</Email>
13    <Phone>555-0044</Phone>
14    <Address>400 Eco Avenue</Address>
15    <OnTimeDeliveryRate>99.0</OnTimeDeliveryRate>
16    <AvgLeadTime>3</AvgLeadTime>
17  </supplier>
18</suppliers_data>

代码示例

1sudo service mysql start; mysql

代码示例

1USE OnlineStore;

代码示例

1LOAD XML INFILE '/var/lib/mysql-files/new_suppliers.xml'
2INTO TABLE Suppliers
3ROWS IDENTIFIED BY '<supplier>';

代码示例

1SELECT * FROM Suppliers WHERE SupplierName LIKE '%Solutions Ltd.' OR SupplierName LIKE '%Planet Goods';

Terminal

代码示例

1{"ProductName": "Eco Water Bottle", "CategoryID": 4, "Price": 15.99, "Stock": 120, "LastRestockDate": "2025-05-10"}
2{"ProductName": "Gaming Mouse", "CategoryID": 1, "Price": 45.00, "Stock": 75, "LastRestockDate": "2025-05-12"}

1-- new_products_ld.json 内容如上所述
2
3-- 步骤 1：创建临时表以存储原始 JSON 数据
4CREATE TEMPORARY TABLE TempJsonImport (
5    json_data JSON
6);
7
8-- 步骤 2：将 JSON 文件中的每一行加载到临时表的 JSON 列中
9LOAD DATA INFILE '/path/to/file/new_products_ld.json' -- 修改此路径
10INTO TABLE TempJsonImport
11LINES TERMINATED BY '\n' -- 每行是一个完整的 JSON 对象
12(json_data);
13
14-- 步骤 3：解析 JSON 数据并插入到 Products 表中
15INSERT INTO Products (ProductName, CategoryID, Price, Stock, LastRestockDate)
16SELECT
17    json_data->>'$.ProductName' AS ProductName, -- ->> 提取标量值
18    json_data->>'$.CategoryID' AS CategoryID,
19    json_data->>'$.Price' AS Price,
20    json_data->>'$.Stock' AS Stock,
21    json_data->>'$.LastRestockDate' AS LastRestockDate
22FROM TempJsonImport;
23
24-- 步骤 4：清理临时表
25DROP TEMPORARY TABLE TempJsonImport;
26
27-- 验证导入
28SELECT * FROM Products WHERE ProductName IN ('Eco Water Bottle', 'Gaming Mouse');

代码示例

1sudo nano /var/lib/mysql-files/new_products_ld.json

代码示例

1{"ProductName": "Eco Water Bottle", "CategoryID": 4, "Price": 15.99, "Stock": 120, "LastRestockDate": "2025-05-10"}
2{"ProductName": "Gaming Mouse", "CategoryID": 1, "Price": 45.00, "Stock": 75, "LastRestockDate": "2025-05-12"}

代码示例

1sudo service mysql start; mysql

代码示例

1USE OnlineStore;

代码示例

1-- new_products_ld.json 内容如上所述
2
3-- 步骤 1：创建临时表以存储原始 JSON 数据
4CREATE TEMPORARY TABLE TempJsonImport (
5    json_data JSON
6);
7
8-- 步骤 2：将 JSON 文件中的每一行加载到临时表的 JSON 列中
9LOAD DATA INFILE '/var/lib/mysql-files/new_products_ld.json' -- 修改此路径
10INTO TABLE TempJsonImport
11LINES TERMINATED BY '\n' -- 每行是一个完整的 JSON 对象
12(json_data);
13
14-- 步骤 3：解析 JSON 数据并插入到 Products 表中
15INSERT INTO Products (ProductName, CategoryID, Price, Stock, LastRestockDate)
16SELECT
17    json_data->>'$.ProductName' AS ProductName, -- ->> 提取标量值
18    json_data->>'$.CategoryID' AS CategoryID,
19    json_data->>'$.Price' AS Price,
20    json_data->>'$.Stock' AS Stock,
21    json_data->>'$.LastRestockDate' AS LastRestockDate
22FROM TempJsonImport;
23
24-- 步骤 4：清理临时表
25DROP TEMPORARY TABLE TempJsonImport;

代码示例

1SELECT * FROM Products WHERE ProductName IN ('Eco Water Bottle', 'Gaming Mouse');

Terminal

1SELECT column1, column2, ...
2FROM table_name
3WHERE condition -- 可选
4INTO OUTFILE 'file_path/output_file.csv'
5FIELDS TERMINATED BY ','
6OPTIONALLY ENCLOSED BY '"'
7LINES TERMINATED BY '\n';

1-- 这将在 secure_file_priv 指定的目录中创建 'electronics_products.csv'
2SELECT ProductID, ProductName, Price, Stock
3FROM Products
4WHERE CategoryID = 1 -- 假设 CategoryID 1 是 'Electronics'
5INTO OUTFILE '/path/to/file/electronics_products.csv' -- 修改此路径
6FIELDS TERMINATED BY ','
7OPTIONALLY ENCLOSED BY '"'
8LINES TERMINATED BY '\n';
9
10-- 注意：要包含标题，可能需要使用 UNION ALL 技巧或手动/程序化方式添加。
11-- 带标题的示例（稍复杂）：
12/*
13(SELECT 'ProductID', 'ProductName', 'Price', 'Stock')
14UNION ALL
15(SELECT ProductID, ProductName, Price, Stock
16FROM Products
17WHERE CategoryID = 1
18INTO OUTFILE '/path/to/file/electronics_products_with_headers.csv'
19FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n');
20*/
21-- 上述使用 UNION ALL 添加标题与 INTO OUTFILE 可能难以直接支持，
22-- 且可能导致标题被当作数据一样加引号。
23-- 通常做法是导出无标题的数据，如有需要再单独添加标题，
24-- 或使用可处理标题生成的客户端工具进行导出。
25-- 本课重点放在直接数据导出。

代码示例

1sudo service mysql start; mysql

代码示例

1USE OnlineStore;

代码示例

1SELECT ProductID, ProductName, Price, Stock
2FROM Products
3WHERE CategoryID = 1 -- 假设 CategoryID 1 是 'Electronics'
4INTO OUTFILE '/var/lib/mysql-files/electronics_products.csv' 
5FIELDS TERMINATED BY ','
6OPTIONALLY ENCLOSED BY '"'
7LINES TERMINATED BY '\n';

1exit

1cat /var/lib/mysql-files/electronics_products.csv

Terminal

代码示例

1SELECT JSON_ARRAYAGG(
2    JSON_OBJECT(
3        'key1', column1,
4        'key2', column2,
5        ...
6    )
7)
8FROM table_name
9WHERE condition -- 可选
10INTO OUTFILE 'file_path/output_file.json';

1SELECT JSON_ARRAYAGG( -- 将所有结果 JSON 对象聚合为单个 JSON 数组
2    JSON_OBJECT(      -- 为每个客户创建一个 JSON 对象
3        'customerName', CustomerName,
4        'emailAddress', Email,
5        'tier', CustomerTier
6    )
7)
8FROM Customers
9WHERE CustomerTier = 'VIP'
10INTO OUTFILE '/path/to/file/vip_customers.json' -- 修改此路径
11LINES TERMINATED BY ''; -- 通常对于单个 JSON blob，我们不希望在 JSON 内部有额外换行。
12
13-- 为了更易读（每行一个 JSON 对象，形成 JSON Lines 格式）：
14/*
15SELECT JSON_OBJECT(
16        'customerName', CustomerName,
17        'emailAddress', Email,
18        'tier', CustomerTier
19    )
20FROM Customers
21WHERE CustomerTier = 'VIP'
22INTO OUTFILE '/path/to/file/vip_customers_lines.jsonl' -- 使用 .jsonl 扩展名
23LINES TERMINATED BY '\n';
24*/

代码示例

1sudo service mysql start; mysql

代码示例

1USE OnlineStore;

代码示例

1SELECT JSON_ARRAYAGG( -- 将所有结果 JSON 对象聚合为单个 JSON 数组
2    JSON_OBJECT(      -- 为每个客户创建一个 JSON 对象
3        'customerName', CustomerName,
4        'emailAddress', Email,
5        'tier', CustomerTier
6    )
7)
8FROM Customers
9WHERE CustomerTier = 'VIP'
10INTO OUTFILE '/var/lib/mysql-files/vip_customers.json' -- 修改此路径
11LINES TERMINATED BY ''; -- 通常对于单个 JSON blob，我们不希望在 JSON 内部有额外换行。

1exit

1cat /var/lib/mysql-files/vip_customers.json

Terminal

1-- 这会创建一个非常基础的类似 XML 的结构。
2-- 要生成带有根元素和特殊字符处理的正确 XML，
3-- 通常需要更复杂的 CONCAT 逻辑或客户端处理。
4SELECT CONCAT(
5    '',
6    '', ProductID, '',
7    '', REPLACE(REPLACE(ProductName, '&', '&'), '<', '<'), '', -- 基础 XML 转义
8    '', Price, '',
9    ''
10)
11FROM Products
12LIMIT 5
13INTO OUTFILE '/path/to/file/simple_products.xml' -- 修改此路径
14LINES TERMINATED BY '\n';
15
16-- 要获得单个根元素，可以这样做：
17/*
18SELECT CONCAT(
19    '',
20    GROUP_CONCAT(
21        CONCAT(
22            '',
23            '', ProductID, '',
24            '', REPLACE(REPLACE(ProductName, '&', '&'), '<', '<'), '',
25            '', Price, '',
26            ''
27        ) SEPARATOR ''
28    ),
29    ''
30)
31FROM (SELECT ProductID, ProductName, Price FROM Products LIMIT 5) AS P
32INTO OUTFILE '/path/to/file/products_root.xml'
33LINES TERMINATED BY '';
34*/
35-- 注意：GROUP_CONCAT 有默认长度限制（max_allowed_packet, group_concat_max_len），
36-- 可能截断非常大的 XML 输出。

代码示例

1sudo service mysql start; mysql

代码示例

1USE OnlineStore;

代码示例

1SELECT CONCAT(
2    '<product>',
3    '<ProductID>', ProductID, '</ProductID>',
4    '<ProductName>', REPLACE(REPLACE(ProductName, '&', '&amp;'), '<', '&lt;'), '</ProductName>', -- 基础 XML 转义
5    '<Price>', Price, '</Price>',
6    '</product>'
7)
8FROM Products
9LIMIT 5
10INTO OUTFILE '/var/lib/mysql-files/simple_products.xml' -- 修改此路径
11LINES TERMINATED BY '\n';

1exit

1cat /var/lib/mysql-files/simple_products.xml

Terminal

代码示例

1SHOW VARIABLES LIKE 'secure_file_priv';

代码示例

1LOAD DATA INFILE '/path/to/file/file.csv'
2INTO TABLE YourTable
3CHARACTER SET utf8mb4  -- 指定输入文件的字符集
4FIELDS TERMINATED BY ','
5...

代码示例

1LOAD DATA INFILE '/path/to/file/data.csv'
2INTO TABLE YourTable
3FIELDS TERMINATED BY ','
4(column1_csv, @date_var, column3_csv) -- 将日期加载到变量中
5SET column2_table = STR_TO_DATE(@date_var, '%Y-%m-%d'); -- 转换并赋值

代码示例

1SHOW WARNINGS;

Quiz

单选题

哪个 SQL 命令主要用于将数据从 CSV 文件导入 MySQL 表中？

A.`INSERT INTO ... VALUES ...`B.`LOAD DATA INFILE`C.`SELECT ... INTO OUTFILE`D.`BACKUP DATABASE`

1 / 3

备份与恢复

SQL Dumps