mysql-大批量插入数据的三种方式和使用场景

1.批量插入三种方式

1. INSERT INTO … SELECT
2. INSERT INTO … VALUES (…)
3. LOAD DATA INFILE ‘/path/to/datafile.csv’ INTO TABLE table_name

2.批量插入

2.1 INSERT INTO … SELECT

用途：从另一个表中选择数据并插入到目标表中。
语法示例：

INSERT INTO target_table (column1, column2)
SELECT column1, column2 FROM source_table WHERE condition;

性能特点：

1. 非常适合批量数据复制或迁移任务。
2. 可以一次性处理大量数据，减少了客户端与服务器之间的通信次数。
3. 在某些情况下，MySQL可以在服务器内部优化整个操作，避免不必要的数据传输。
4. 如果SELECT查询本身很复杂（如涉及多个JOIN、子查询等），则可能会影响整体性能。

2.2 INSERT INTO … VALUES (…)

用途：直接插入明确指定的数据值。
语法示例：

INSERT INTO table_name (column1, column/XMLSchema)
VALUES (value1, value2);

性能特点：

1. 对于少量数据插入非常高效。
2. 因为值是预先知道的，所以数据库可以优化这个操作。
3. 当需要插入大量独立的行时，可能需要多次调用该语句，这会导致较高的网络延迟和事务开销。

2.3 LOAD DATA INFILE [path] INTO TABLE table_name

用途：如果数据已经在服务器上作为一个文件存在，或者可以通过网络传输到服务器上，那么使用LOAD DATA INFILE通常是最快的方法之一。
语法示例：

LOAD DATA INFILE '/path/to/datafile.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

优点：
相比其他方法，LOAD DATA INFILE通常要快得多，因为它直接读取文件内容并加载到表中，绕过了标准的SQL解析器。
支持多种格式化选项来匹配输入文件结构。
缺点：
需要确保文件位于服务器本地或通过安全的方式传输至服务器。
可能需要调整MySQL的安全设置以允许LOAD DATA LOCAL INFILE。

3. LOAD DATA INFILE基本语法解释

基本语法解释

LOAD DATA INFILE '/path/to/datafile.csv': 指定要导入的数据文件的位置。请注意，这里的路径是相对于服务器端的文件系统路径。
INTO TABLE table_name: 指定要将数据导入的目标表。
FIELDS TERMINATED BY ',': 定义字段之间的分隔符。在这个例子中，字段是以逗号 , 分隔的。
LINES TERMINATED BY '\n': 定义行之间的终止符。这里每一行以换行符 \n 结束。

进一步的配置选项
根据你的具体需求，可能还需要对命令进行一些额外的配置：
忽略某些行：
IGNORE 1 LINES; 是 LOAD DATA INFILE 命令中的一个选项，用于指示 MySQL 在导入数据时跳过文件的前几行
如果 CSV 文件包含标题行，可以使用 IGNORE number LINES 来跳过这些行。

LOAD DATA INFILE '/path/to/datafile.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

指定列映射：
如果 CSV 文件中的列顺序与目标表不匹配，或者你想只导入部分列，可以指定列名(column1, column2, column3);：

LOAD DATA INFILE '/path/to/datafile.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
(column1, column2, column3);

处理字符编码：CHARACTER SET utf8mb4
如果你的文件不是 UTF-8 编码，可以指定字符集：

LOAD DATA INFILE '/path/to/datafile.csv'
INTO TABLE table_name
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';