mysql 批量一对多插入

基础概念

MySQL 批量一对多插入是指在一个事务中，将多个主表记录和对应的多个子表记录一次性插入数据库。这种操作通常用于提高数据插入的效率，尤其是在处理大量数据时。

相关优势

1. 提高效率：批量插入可以减少网络传输和数据库操作的次数，从而显著提高数据插入的速度。
2. 减少锁竞争：通过事务控制，可以减少对数据库表的锁竞争，提高并发性能。
3. 简化代码：批量插入可以简化代码逻辑，减少循环插入的复杂度。

类型

1. 使用 INSERT INTO ... SELECT 语句：适用于从一个表中选择数据并插入到另一个表中。
2. 使用 INSERT INTO ... VALUES 语句：适用于直接插入多组数据。

应用场景

1. 数据迁移：将一个数据库的数据迁移到另一个数据库。
2. 批量导入数据：从文件或其他数据源批量导入数据到数据库。
3. 日志记录：记录大量日志数据时，批量插入可以显著提高效率。

示例代码

假设我们有两个表：users 和 orders，其中 users 是主表，orders 是子表，它们之间是一对多的关系。

-- 创建 users 表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- 创建 orders 表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product VARCHAR(255) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 批量插入数据
START TRANSACTION;

INSERT INTO users (name) VALUES
('Alice'),
('Bob'),
('Charlie');

INSERT INTO orders (user_id, product) VALUES
(1, 'Product A'),
(1, 'Product B'),
(2, 'Product C'),
(3, 'Product D');

COMMIT;

遇到的问题及解决方法

问题：批量插入时遇到 Duplicate entry 错误

原因：插入的数据中存在重复的主键或唯一键值。

解决方法：

1. 检查数据：确保插入的数据中没有重复的主键或唯一键值。
2. 使用 INSERT IGNORE 或 REPLACE INTO：

INSERT IGNORE INTO users (name) VALUES ('Alice'), ('Bob');

或者

REPLACE INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob');

问题：批量插入时遇到 Deadlock found when trying to get lock 错误

原因：多个事务相互等待对方释放锁，导致死锁。

解决方法：

1. 优化事务：尽量减少事务的范围，缩短事务的持有时间。
2. 调整事务隔离级别：根据业务需求调整事务隔离级别，减少锁的竞争。

问题：批量插入时性能不佳

原因：可能是由于网络传输、数据库配置或硬件性能限制。

解决方法：

1. 优化 SQL 语句：确保 SQL 语句高效，避免不必要的复杂操作。
2. 调整数据库配置：增加缓冲区大小，优化连接数等配置。
3. 使用批量插入工具：如 LOAD DATA INFILE 或第三方工具。

参考链接

• MySQL 批量插入文档
• MySQL 事务处理

希望这些信息对你有所帮助！如果有更多问题，请随时提问。