如何对比MS SQL与MySQL关联数据库的查询结果差异

To find cases where the same email has a different number in your MySQL database compared to MS SQL, you can combine the OPENQUERY result with your local MS SQL table using a join. Here's a straightforward, practical approach:

Step 1: Fetch MySQL Data and Compare Directly in MS SQL #

Use a Common Table Expression (CTE) to pull the MySQL data into MS SQL's context, then join it with your local Contacts table to identify mismatches:

WITH MySQLContacts AS (
    -- Pull email and number from MySQL via your linked server
    SELECT 
        email, 
        number AS mysql_number
    FROM OPENQUERY([DB-01], 'select email, number from database.contacts')
)
-- Join with MS SQL Contacts and filter for mismatched numbers
SELECT 
    mc.email,
    mc.mysql_number AS mysql_side_number,
    ms.number AS mssql_side_number
FROM MySQLContacts mc
INNER JOIN [DB].[dbo].[Contacts] ms 
    ON mc.email = ms.email
WHERE 
    -- Catch direct number mismatches
    mc.mysql_number <> ms.number
    -- Optional: Include cases where one side has a NULL value and the other doesn't
    OR (mc.mysql_number IS NULL AND ms.number IS NOT NULL)
    OR (mc.mysql_number IS NOT NULL AND ms.number IS NULL);

Key Details: #

• CTE (MySQLContacts): This acts as a temporary result set holding your MySQL data, renaming number to mysql_number to avoid confusion with the MS SQL field.
• INNER JOIN: Ensures we only compare emails that exist in both databases (swap to LEFT JOIN if you want to include emails unique to MySQL, but your question focuses on matching emails with conflicting numbers).
• WHERE Clause: Explicitly checks for differing values, including edge cases where one number is NULL (since NULL <> anything doesn't return true by default in SQL).

Alternative: Using a Temporary Table (For Large Datasets) #

If you're working with a large volume of data, a temporary table might offer better performance than a CTE:

-- Create temp table to store MySQL data
SELECT 
    email, 
    number AS mysql_number
INTO #MySQLTemp
FROM OPENQUERY([DB-01], 'select email, number from database.contacts');

-- Compare with MS SQL table
SELECT 
    t.email,
    t.mysql_number AS mysql_side_number,
    c.number AS mssql_side_number
FROM #MySQLTemp t
INNER JOIN [DB].[dbo].[Contacts] c 
    ON t.email = c.email
WHERE 
    t.mysql_number <> c.number
    OR (t.mysql_number IS NULL AND c.number IS NOT NULL)
    OR (t.mysql_number IS NOT NULL AND c.number IS NULL);

-- Clean up the temp table
DROP TABLE #MySQLTemp;

Handling Data Type Differences #

If number is stored as different data types in the two databases (e.g., VARCHAR in MySQL, INT in MS SQL), cast them to the same type to avoid comparison errors. For example, if MS SQL's number is an integer:

WHERE 
    mc.mysql_number <> CAST(ms.number AS VARCHAR(20))
    -- Keep the NULL checks as needed

This query will return exactly the MySQL-side records where the number doesn't match the corresponding entry in MS SQL, along with the MS SQL value for easy reference.

内容的提问来源于stack exchange，提问作者Marc El Bichon