请求生成将Books表作者/插画师迁移至关联表的SQL转换查询

Let's break this down into actionable SQL steps to convert your denormalized Books table into the normalized one-to-many structure using the People and books_people tables.

Step 1: Populate the People Table with Unique Authors & Illustrators #

First, we need to get all distinct authors and illustrators from the Books table into the People table, making sure we don't create duplicate entries. This assumes your People table has a unique constraint on the name column (if not, add one first with ALTER TABLE People ADD UNIQUE KEY (name);).

-- Insert all unique authors into People
INSERT IGNORE INTO People (name)
SELECT DISTINCT author 
FROM Books 
WHERE author IS NOT NULL AND author != '';

-- Insert all unique illustrators into People
INSERT IGNORE INTO People (name)
SELECT DISTINCT illustrator 
FROM Books 
WHERE illustrator IS NOT NULL AND illustrator != '';

The INSERT IGNORE will skip any duplicates that already exist in the People table, which prevents primary key or unique constraint errors.

Step 2: Link Books to People via the books_people Junction Table #

Next, we'll create the associations between each book and its author/illustrator in the junction table. Replace people_id with whatever the primary key column name is in your People table (e.g., id if that's what you use).

-- Add author associations
INSERT INTO books_people (book_id, author_id, occupation)
SELECT 
    b.book_id,
    p.people_id,
    'author' AS occupation
FROM Books b
INNER JOIN People p ON b.author = p.name
WHERE b.author IS NOT NULL AND b.author != '';

-- Add illustrator associations
INSERT INTO books_people (book_id, author_id, occupation)
SELECT 
    b.book_id,
    p.people_id,
    'illustrator' AS occupation
FROM Books b
INNER JOIN People p ON b.illustrator = p.name
WHERE b.illustrator IS NOT NULL AND b.illustrator != '';

Pro tip: Run just the SELECT part of these queries first to verify the data matches what you expect before executing the INSERT.

Step 3: Clean Up (Optional) #

Once you've verified all associations are correct and you no longer need the original fields in the Books table, you can drop them:

ALTER TABLE Books 
DROP COLUMN author, 
DROP COLUMN illustrator;

Important Notes #

• Backup First: Always back up your database before running bulk data modifications like this.
• Handle Edge Cases: The WHERE clauses filter out NULL/empty strings to avoid creating invalid entries in the junction table. Adjust these if your data uses different placeholder values.
• Unique Constraint: Ensure the name column in People has a unique constraint to prevent duplicate person records.

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