MySQL批量插入数据并关联主键ID的最佳实践

使用MySQL事务和循环语句批量插入数据并关联主键ID

本文将详细介绍如何使用MySQL事务和循环语句将数据从一个表批量插入到两个关联表中，并确保主键ID与外键ID的对应关系。

场景描述:

假设我们有两个表：ask_questions 和 ask_answers，其中 ask_questions 表存储问题信息，ask_answers 表存储问题答案，两表通过 question_id 关联。现在我们有一个名为 C 的表，其中包含 question_title 和 question_answer 两个字段，需要将 C 表的数据批量插入到 ask_questions 和 ask_answers 表中，并保证 ask_answers 表的 question_id 字段与 ask_questions 表的主键 id 一一对应。

解决方案:

可以使用一个循环来逐条插入数据，并且在每次插入后获取对应的主键 ID 作为 ask_answers 表中的 question_id。具体操作步骤如下:

开启事务: 使用 START TRANSACTION; 开启一个事务，以保证数据一致性。
声明变量: 使用 DECLARE 声明必要的变量，包括循环计数器 i、临时变量 c_title 和 c_answer，以及存储主键 ID 的变量 question_id。
循环插入: 使用 WHILE 循环遍历 C 表中的数据，执行以下操作:
- 使用 SELECT ... INTO 语句从 C 表中获取当前行的数据，并赋值给临时变量。
- 使用 INSERT INTO 语句将当前行数据插入到 ask_questions 表中。
- 使用 SET question_id = LAST_INSERT_ID(); 获取刚插入记录的主键 ID。
- 使用 INSERT INTO 语句将当前行数据和 question_id 插入到 ask_answers 表中。
提交事务: 使用 COMMIT; 提交事务，将所有操作持久化到数据库。

示例代码:

START TRANSACTION;

DECLARE i INT DEFAULT 1;
DECLARE c_title VARCHAR(255);
DECLARE c_answer VARCHAR(255);
DECLARE question_id INT;

WHILE i <= 100 DO
    SELECT question_title, content INTO c_title, c_answer FROM C LIMIT i-1, 1;
    INSERT INTO ask_questions (user_id, category_id, title, description, price, hide, answers , status, created_at, updated_at)
    VALUES 
        (1, 7, c_title, c_title, 1, 1, 1,1, @timestamp := DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 24 + 24) HOUR), @timestamp),
    (1, 7, c_title, c_title, 1, 1, 1,1, @timestamp, @timestamp);
    SET question_id = LAST_INSERT_ID();

    INSERT INTO ask_answers (question_title, question_id, user_id, content, status, adopted_at, created_at, updated_at)
    VALUES (c_title, question_id, 1007, c_answer, 1, NULL, @timestamp := DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 24) HOUR), @timestamp);
    
    SET i = i + 1;
END WHILE;

COMMIT;

注意:

上述代码中的 user_id、category_id、price、hide、answers、status 等字段需要根据实际情况进行修改。
@timestamp 变量用于生成随机时间戳，可以根据实际需求进行调整。
FLOOR(RAND() * 24 + 24) 用于生成 24 到 48 之间的随机小时数，用于模拟问题和答案的创建时间。
为了提高效率，可以将循环内部的 INSERT 语句合并成一条语句，并使用 VALUES 子句一次性插入多条记录。

总结:

本文介绍了如何利用MySQL事务和循环语句，将数据从一个表批量插入到两个关联表中，并确保主键ID与外键ID的对应关系，提高数据插入效率和数据完整性。这种方法可以有效地处理大量数据的插入操作，并保证数据的逻辑关系正确。