MySQL如何合并两个数据相似的表？Union/Join用法咨询

Hey there, let's break down how to tackle this MySQL table merge scenario—it’s super common when dealing with siloed Excel-sourced data that you can’t modify at the source. Here’s a step-by-step approach tailored to your needs:

1. First, Define Your Matching Rule #

Since you mentioned name and address are consistent across matching rows, these will be your "unique identifier" pair. But you need to account for messy data (like extra spaces, uppercase/lowercase differences) to avoid missing matches. For example, use TRIM() and LOWER() to normalize these fields when comparing.

2. Pick a Merge Strategy #

You have two common options based on what "latest database" means for you:

• Priority-based overwrite: Keep the most recent data (e.g., if Table B was imported later, use its phone/email to replace Table A’s for matching rows)
• Preserve all variations: Add fields to track which table each value came from (useful if you need to audit changes later)

We’ll focus on the priority-based approach first—it’s the most common for building a "single source of truth" database.

3. Implement the Merge #

Option A: Use INSERT ... ON DUPLICATE KEY UPDATE (Simplest for Overwrites) #

First, you’ll need to add a unique index to your target table to trigger the update when matches are found:

-- Add a unique index on normalized name + address to avoid duplicates
ALTER TABLE target_table 
ADD UNIQUE INDEX idx_normalized_name_address (
    TRIM(LOWER(name)), 
    TRIM(LOWER(address))
);

Then, import your first table’s data, then use the second table to update matching rows with newer phone/email values:

-- Step 1: Import all data from your first source table
INSERT INTO target_table (name, address, phone, email)
SELECT name, address, phone, email FROM table1;

-- Step 2: Import data from the second table, overwriting phone/email for matches
INSERT INTO target_table (name, address, phone, email)
SELECT name, address, phone, email FROM table2
ON DUPLICATE KEY UPDATE
    -- Only update if the new value isn't empty/null (avoids overwriting valid data with blanks)
    phone = IF(VALUES(phone) IS NOT NULL AND VALUES(phone) != '', VALUES(phone), phone),
    email = IF(VALUES(email) IS NOT NULL AND VALUES(email) != '', VALUES(email), email);

Option B: Use JOIN + UNION (For Complex Logic) #

If you need more control (like comparing which value is newer, or preserving both), use a join to combine rows and union to add new records from either table:

-- Generate a merged dataset: use Table2 values first, fall back to Table1 if missing
SELECT
    COALESCE(t2.name, t1.name) AS name,
    COALESCE(t2.address, t1.address) AS address,
    COALESCE(t2.phone, t1.phone) AS phone,
    COALESCE(t2.email, t1.email) AS email
FROM table1 t1
LEFT JOIN table2 t2 
    ON TRIM(LOWER(t1.name)) = TRIM(LOWER(t2.name)) 
    AND TRIM(LOWER(t1.address)) = TRIM(LOWER(t2.address))

-- Add rows from Table2 that don't exist in Table1
UNION

SELECT name, address, phone, email
FROM table2 t2
WHERE NOT EXISTS (
    SELECT 1 FROM table1 t1
    WHERE TRIM(LOWER(t1.name)) = TRIM(LOWER(t2.name)) 
    AND TRIM(LOWER(t1.address)) = TRIM(LOWER(t2.address))
);

You can insert this result directly into your target table with INSERT INTO target_table (...) [above query].

4. Verify & Clean Up #

Always double-check your merged data to avoid mistakes:

-- Check how many rows matched between the two tables
SELECT COUNT(*) 
FROM table1 t1
JOIN table2 t2 
    ON TRIM(LOWER(t1.name)) = TRIM(LOWER(t2.name)) 
    AND TRIM(LOWER(t1.address)) = TRIM(LOWER(t2.address));

-- Check for duplicate rows in the target table
SELECT name, address, COUNT(*) 
FROM target_table 
GROUP BY name, address 
HAVING COUNT(*) > 1;

Pro Tips #

• Clean data first: If addresses have inconsistencies (e.g., "St" vs "Street"), use REPLACE() to normalize them before merging.
• Backup everything: Always take a backup of your source tables and target table before running merge operations—better safe than sorry!
• Track changes: If you need to audit which table contributed each value, add fields like phone_source or email_source to your target table and populate them during the merge.

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