MySQL中count(distinct多列)处理NULL值异常问题咨询

Ah, I see the issue here—it all boils down to how SQL handles NULL values in DISTINCT multi-column combinations. Let me break this down step by step:

The Core Problem: NULLs Are Treated as Equal in DISTINCT #

In SQL, even though NULL != NULL in direct comparisons, operations like DISTINCT and GROUP BY treat all NULL values as identical. That means if you have multiple rows where col2 is NULL, their (col1, col2) combinations will be grouped together if col1 is the same.

When you added col3 to the DISTINCT set, the count stayed at 4980 because those 20 rows with col2 IS NULL still don't form unique combinations with col1 and col3. There are two likely reasons for this:

1. The col3 values are also NULL or duplicate for those rows #

If those 20 rows have either:

• col3 as NULL (so their combination becomes (col1, NULL, NULL), which is treated as identical across all those rows), OR
• col3 has the same value for multiple rows with the same col1 and col2 = NULL (e.g., 5 rows with (123, NULL, 456)), then those duplicates will still be collapsed into a single entry in the DISTINCT count.

2. You still have duplicate (col1, col2, col3) combinations #

Even if col3 has non-NULL values, if multiple rows share the exact same col1, col2 (NULL), and col3 values, they'll still be counted as one in the DISTINCT result.

How to Diagnose the Exact Duplicates #

To figure out which combinations are still repeating, run this query to group the problematic rows and see their counts:

SELECT col1, col2, col3, COUNT(*) AS duplicate_count
FROM tab1
WHERE col2 IS NULL
GROUP BY col1, col2, col3
HAVING COUNT(*) > 1;

This will show you exactly which (col1, col2, col3) groups have duplicates, and how many rows are in each duplicate group. From there, you can decide if you need to add another column to your unique key, or clean up the duplicate/NULL values to achieve uniqueness.

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