数据导入利器：MySQL LOAD DATA LOCAL INFILE vs. source命令对比解析

简介

MySQL的LOAD DATA LOCAL INFILE是一个用于将本地文件数据加载到数据库表中的功能。

优点

1. 灵活性高：LOAD DATA LOCAL INFILE支持导入各种格式的文件，如CSV、文本文件等，使数据导入过程更加灵活。
2. 高效：相比使用INSERT语句逐行插入数据，LOAD DATA LOCAL INFILE可以实现批量导入数据，速度更快。
3. 内存占用低：使用LOAD DATA LOCAL INFILE导入数据时，数据直接从文件读取，不会在内存中暂存大量数据，因此对内存的需求较低。
4. 减少网络传输：当数据文件位于本地服务器上时，使用LOAD DATA LOCAL INFILE可以避免通过网络传输数据。

缺点

1. 安全风险：由于LOAD DATA LOCAL INFILE允许从本地文件系统中加载数据，可能存在安全风险。如果恶意用户能够执行该命令，可能会导致数据泄露或破坏。
2. 访问权限限制：默认情况下，MySQL不允许客户端使用LOAD DATA LOCAL INFILE命令，需要在启动时指定--local-infile选项或在配置文件中设置local-infile=1。这样可能会增加系统配置和维护的复杂性，并且需要考虑安全性。
3. 数据格式限制：LOAD DATA LOCAL INFILE需要确保导入的文件与表的列数和数据类型匹配，否则可能导致导入错误或截断数据。
4. 不支持高级操作：LOAD DATA LOCAL INFILE是一个基本的数据导入工具，不能执行复杂的数据转换和处理操作。

比较

LOAD DATA LOCAL INFILE 和 source 都是 MySQL 中用于导入数据的命令，但它们之间有一些区别。

1. 语法：

• LOAD DATA LOCAL INFILE：这是一个 SQL 语句，用于从本地文件系统加载数据到数据库表中。示例：LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE table_name
• source：这是 MySQL 客户端命令行工具中的一个命令，用于执行包含 SQL 语句的脚本文件。示例：source /path/to/script.sql

1. 使用场景：

• LOAD DATA LOCAL INFILE：适用于从本地文件系统导入大量的数据到数据库表中。通常用于批量导入数据，例如从 CSV 文件中导入数据到数据库表。
• source：适用于执行包含多条 SQL 语句的脚本文件。可以用于执行创建表、插入数据、更新数据等多个操作。

1. 文件路径：

• LOAD DATA LOCAL INFILE：需要指定完整的本地文件路径，并且 MySQL 服务器需要有权限读取该文件。
• source：需要指定脚本文件的路径，并且 MySQL 客户端需要有权限读取该文件。

需要注意的是，LOAD DATA LOCAL INFILE 默认是被禁用的，需要在 MySQL 配置文件中设置 local_infile=1 并重启 MySQL 服务后才能正常使用。

总结来说，LOAD DATA LOCAL INFILE 主要用于将本地文件中的数据导入到数据库表中，而 source 主要用于执行包含多条 SQL 语句的脚本文件。它们的使用场景和语法略有不同，需要根据具体需求选择适合的命令进行数据导入。

综上所述

LOAD DATA LOCAL INFILE在数据导入方面具有高效、灵活的优点，但需要注意安全性和访问权限等问题。在使用时需谨慎，并根据实际需求考虑其适用性。

Demo

修改配置

不改配置，会报以下错误： ERROR 1148 (42000): The used command is not allowed with this MySQL version

sudo sh -c "echo 'local-infile=1' >> /home/xj/software/docker/mysql/config/my.cnf"

创建表

-- 创建表
use tmp;

drop table if exists tmp_products;
CREATE TABLE tmp_products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10, 2)
);

创建数据表

我们先创建一个表csv的表，文本格式，后缀是csv

vim /home/xj/software/docker/mysql/log/bak/test.csv

id,name,price
1,Product A,10.99
2,Product B,19.99
3,Product C,5.99

导入数据

mysql -uroot -piPwd000000 --local-infile=1
use tmp;
select * from tmp_products;

LOAD DATA LOCAL INFILE '/var/log/mysql/bak/test.csv'
INTO TABLE tmp_products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

select * from tmp_products;