MySQL批量插入多分类关联：为无分类代码添加分类记录

Hey there! Let's work through this problem step by step to get your 15,000 unlinked code records each paired with 20 unique code_category entries, using that single reference record (ID 123) as a template.

First, Grab the Reference Discount Value #

First, let's pull the discount from your reference record—we'll reuse this for all new entries (if you need different discounts per category, we can adjust this later):

SELECT discount FROM code_category WHERE id = 123;

Next, Create Your List of 20 Unique Category IDs #

You need 20 distinct category_id values. Here are two common ways to get them:

Option 1: Use Fixed, Known Category IDs

If you already know the 20 category IDs you want to use (like 1 through 20), generate a temporary list with UNION ALL:

SELECT 1 AS category_id UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
-- Keep adding until you reach 20
SELECT 20;

Option 2: Pull from an Existing Category Table

If you have a dedicated category table, just grab the first 20 entries (adjust the logic if you need specific categories):

SELECT id AS category_id FROM category LIMIT 20;

The Full Batch Insert Query #

Now we'll combine the unlinked codes with your 20 category IDs using a CROSS JOIN (this creates a Cartesian product—each code pairs with every category). Here's the complete query, using Option 1 as an example:

INSERT INTO code_category (code_id, category_id, discount)
SELECT 
    c.id AS code_id,
    cat.category_id,
    (SELECT discount FROM code_category WHERE id = 123) AS discount
FROM 
    code c
LEFT JOIN 
    code_category cc ON c.id = cc.code_id
CROSS JOIN 
    (
        SELECT 1 AS category_id UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5 UNION ALL
        SELECT 6 UNION ALL
        SELECT 7 UNION ALL
        SELECT 8 UNION ALL
        SELECT 9 UNION ALL
        SELECT 10 UNION ALL
        SELECT 11 UNION ALL
        SELECT 12 UNION ALL
        SELECT 13 UNION ALL
        SELECT 14 UNION ALL
        SELECT 15 UNION ALL
        SELECT 16 UNION ALL
        SELECT 17 UNION ALL
        SELECT 18 UNION ALL
        SELECT 19 UNION ALL
        SELECT 20
    ) cat
WHERE 
    cc.code_id IS NULL; -- Only target codes that have no existing category links

Key Notes & Optimizations #

• Why CROSS JOIN? It’s exactly what we need here—it takes every unlinked code and pairs it with each of the 20 categories, giving us 300,000 total records (15k ×20) in one go.
• Speed Tips: With this many records, consider disabling indexes on code_category temporarily (rebuild them after insertion) to speed things up. Also, double-check your database’s max_allowed_packet setting to avoid size issues.
• Test First: Run the SELECT part alone (without the INSERT) with a LIMIT 10 on the code query to make sure the output looks right before committing the full insert.
• Fixing Your Original Query: Your initial attempt had issues with how UNION interacts with VALUES, and didn’t handle the one-to-many pairing. This CROSS JOIN approach fixes both problems cleanly.

内容的提问来源于stack exchange，提问作者vinz