我需要重组我的MYSQL InnoDB数据库。
目前，我有一个customer表，其中包含3个产品名称。
我需要将这些名称提取到新的product表中。 product表应包含customer表中当前保留的每个名称，并通过新的customer表链接到customer_product表。尽管产品名称可能不是唯一的，但是它们彼此之间没有任何关系，这意味着对于每个customer，都需要在product表中插入3个新条目，在customer_product中插入3个新条目。 >表格。
所以代替这个：
customer
| id | product_name_a | product_name_b | product_name_c |
我需要这个：
customer
| id |
customer_product
| customer_id | product_id | X3
product
| id | name | X3
我编写了下面的有效的MYSQL过程：
BEGIN
DECLARE nbr_of_customers BIGINT(20);
DECLARE customer_count BIGINT(20);
DECLARE product_id BIGINT(20);
DECLARE customer_id BIGINT(20);
DECLARE product_name_a VARCHAR(500);
DECLARE product_name_b VARCHAR(500);
DECLARE product_name_c VARCHAR(500);
SELECT COUNT(*) FROM customer INTO nbr_of_customers;
SET customer_count = 0;
SET product_id = 1;
WHILE customer_count < nbr_of_customers DO
SELECT
customer.id,
customer.product_name_a,
customer.product_name_b,
customer.product_name_c
INTO
customer_id,
product_name_a,
product_name_b,
product_name_c
FROM customer
LIMIT customer_count,1;
INSERT INTO product(id, name)
VALUES(product_id, product_name_a);
INSERT INTO customer_product(customer_id, product_id)
VALUES(customer_id, product_id);
SET product_id = product_id + 1;
INSERT INTO product(id, name)
VALUES(product_id, product_name_b);
INSERT INTO customer_product(customer_id, product_id)
VALUES(customer_id, product_id);
SET product_id = product_id + 1;
INSERT INTO product(id, name)
VALUES(product_id, product_name_c);
INSERT INTO customer_product(customer_id, product_id)
VALUES(customer_id, product_id);
SET product_id = product_id + 1;
SET customer_count = customer_count + 1;
END WHILE;
END;
这太慢了。
我已在本地运行此程序，估计我的约1.5万名客户需要约1小时才能完成。而且我的VPS服务器要慢得多，因此可能需要10个小时才能完成。
问题似乎是插入内容需要很长时间。因此，我想在过程中存储所有插入内容，并在循环完成并且我知道要插入的内容之后批量执行所有插入内容。
我有一种方法可以批量执行所有〜100k的插入操作以优化性能，还是有更好的方法呢？
最终编辑：
我标记了正确的解决方案是基于它在极大地加快流程速度方面的出色工作，这是问题的主要重点。最后，由于该解决方案在不转义所插入字符串的限制方面，我最终使用修改后的生产代码（Java）来执行迁移。
最佳答案
首先，使用游标处理单个查询的结果，而不是对每一行执行单独的查询。
然后将VALUES列表连接成使用PREPARE和EXECUTE执行的字符串。
我的代码会按100个客户的批次进行插入，因为我希望查询的大小受到限制。
BEGIN
DECLARE product_id BIGINT(20);
DECLARE customer_id BIGINT(20);
DECLARE product_name_a VARCHAR(500);
DECLARE product_name_b VARCHAR(500);
DECLARE product_name_c VARCHAR(500);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT c.id, c.product_name_a, c.product_name_b, c.product_name_c FROM customer AS c;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET product_id = 1;
OPEN cur;
SET @product_values = '';
SET @cp_values = '';
read_loop: LOOP
FETCH cur INTO customer_id, product_name_a, product_name_b, product_name_c;
IF done THEN
LEAVE read_loop;
END IF;
SET @product_values = CONCAT(@product_values, IF(@product_values != '', ',', ''), "(", product_id, ",'", product_name_a, "'), (", product_id + 1, ",'", product_name_b, "'), (", product_id + 2, ",'", product_name_c, "'), ");
SET @cp_values = CONCAT(@cp_values, IF(@cp_values != '', ',', ''), "(", customer_id, ",", product_id, "), (", customer_id, ",", product_id + 1, "), (", customer_id, ",", product_id + 2, "),");
SET product_id = product_id + 3;
IF product_id % 300 = 1 -- insert every 100 customers
THEN BEGIN
SET @insert_product = CONCAT("INSERT INTO product(id, name) VALUES ", @product_values);
PREPARE stmt1 FROM @insert_product;
EXECUTE stmt1;
SET @insert_cp = CONCAT("INSERT INTO customer_product(customer_id, product_id) VALUES ", @cp_values);
PREPARE stmt2 FROM @insert_cp;
EXECUTE stmt2;
SET @product_values = '';
SET @cp_values = '';
END IF;
END LOOP;
IF @product_values != '' -- Process any remaining rows
THEN BEGIN
SET @insert_product = CONCAT("INSERT INTO product(id, name) VALUES ", @product_values);
PREPARE stmt1 FROM @insert_product;
EXECUTE stmt1;
SET @insert_cp = CONCAT("INSERT INTO customer_product(customer_id, product_id) VALUES ", @cp_values);
PREPARE stmt2 FROM @insert_cp;
EXECUTE stmt2;
SET @product_values = '';
SET @cp_values = '';
END IF;
END;
请注意，使用此解决方案后，在插入产品名称之前将无法正确转义它们。因此，如果任何产品名称包含特殊字符（例如单引号
'），则此解决方案将不起作用。关于mysql - 批处理MYSQL插入以提高数据库结构迁移的性能，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50847760/