MySQL批量插入数据与锁表
1. 引言
在开发过程中，我们经常需要从其他数据源（例如Excel、CSV文件或其他数据库）中将大量数据批量导入到MySQL数据库中。传统的单条插入方法效率低下，因此我们需要使用批量插入的方法来提高效率。然而，批量插入数据时可能会出现锁表的问题。本文将介绍如何使用MySQL进行批量插入数据以及如何处理可能出现的锁表问题。
2. 批量插入数据
2.1 单条插入方法
在介绍批量插入方法之前，我们先来了解一下传统的单条插入方法。通常，我们使用INSERT语句来插入单条数据。以下是一个示例：
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
这种方法的问题在于每次插入一条数据都会进行一次数据库操作，效率较低。
2.2 批量插入方法
批量插入方法可以一次性插入多条数据，从而提高插入效率。MySQL提供了INSERT INTO ... VALUES和INSERT INTO ... SELECT两种批量插入的语法。
2.2.1 INSERT INTO ... VALUES语法
INSERT INTO ... VALUES语法用于一次性插入多个固定值的记录。以下是一个示例：
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...),
(value1, value2, value3, ...),
(value1, value2, value3, ...),
...
其中，每个括号内的值代表一条记录的值。
2.2.2 INSERT INTO ... SELECT语法
INSERT INTO ... SELECT语法用于一次性插入多个查询结果的记录。以下是一个示例：
INSERT INTO table_name (column1, column2, column3, ...)
SELECT value1, value2, value3, ...
FROM source_table
WHERE condition;
其中，SELECT语句用于选择要插入的数据，FROM子句指定数据来源的表，WHERE子句可选。
2.3 批量插入示例
假设我们有一个名为students的表，包含三个字段：id、name和age。我们有一个包含多个学生信息的CSV文件，我们可以使用MySQL的批量插入方法将这些学生信息导入到students表中。
2.3.1 创建表
首先，我们需要创建students表：
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);
2.3.2 导入CSV文件
假设我们有一个名为students.csv的CSV文件，包含以下内容：
1,John,18
2,Mary,20
3,David,19
4,Lisa,21
我们可以使用以下SQL语句将CSV文件中的数据导入到students表中：
LOAD DATA INFILE '/path/to/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
2.4 批量插入的性能优化
在进行批量插入时，我们可以采取一些性能优化的措施来提高插入速度。
2.4.1 关闭事务自动提交
默认情况下，MySQL会在每次插入后自动提交事务。对于大量数据的批量插入，我们可以将事务自动提交功能关闭，以减少提交的次数。
SET AUTOCOMMIT = 0;
2.4.2 使用事务
在进行批量插入时，开启事务可以提高插入的效率。开启事务后，所有插入操作将在事务结束时一次性提交，而不是每次插入都进行提交。
START TRANSACTION;
-- 执行批量插入操作
COMMIT;
