재현된 테스트 데이터베이스는 다음과 같습니다.
CREATE TABLE `test_distinct` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `b` varchar(50) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
테이블의 테스트 데이터는 다음과 같습니다. 이제 이 세 개의 열을 중복 제거한 후 열 수를 계산해야 합니다.
친구가 문제를 찾기 위해 4개의 쿼리문을 주었습니다
SELECT COUNT(*) AS cnt FROM test_distinct; SELECT COUNT(DISTINCT id, a, b) as cnt FROM test_distinct; SELECT id, a, b, COUNT(*) AS cnt FROM test_distinct GROUP BY id, a, b HAVING cnt > 1; SELECT l.id AS l_id, l.a AS l_a, l.b AS l_b, r.id AS r_id, r.a AS r_a, r.b AS r_b FROM test_distinct l LEFT JOIN test_distinct r ON l.id = r.id AND l.a = r.a AND l.b = r.b WHERE r.id is NULL or r.id = 'null';
쿼리 결과는 다음과 같습니다.
주의! ! ! 테스트 데이터를 보면 어디에 문제가 있는지 금방 짐작할 수 있지만, 테이블 안에는 30,000개가 넘는 데이터가 있어 육안으로는 그 데이터를 보는 것이 불가능하다는 사실이 밝혀진다.
위 쿼리 결과에는 두 가지 반직관적인 점이 있습니다.
중복 제거 통계 후 두 번째 데이터가 누락되었지만 세 번째 데이터 결과를 보면 동일한 데이터가 없는 것으로 나타났습니다.
동일 테이블을 사용하여 왼쪽 외부 연결을 할 때 구동 테이블에는 데이터가 있지만 피동 테이블은 비어 있습니다.
먼저 두 번째 질문을 살펴보겠습니다. 공식 문서에는 다음과 같은 설명이 있습니다.
ON 절을 사용할 때 포함된 조건식은 WHERE 절에서 사용된 것과 동일합니다. 일반적인 상황은 ON 절을 사용하여 테이블의 조인 조건을 지정하고 WHERE 절을 사용하여 결과 집합에 포함되는 행을 제한하는 것입니다.
LEFT JOIN의 ON 또는 USING 부분의 조건에 대해 오른쪽 테이블에 일치하는 행이 없으면 오른쪽 테이블은 NULL로 설정된 모든 열을 사용합니다.
산술 비교 연산자(예: =, )를 사용하여 NULL을 비교할 수 없습니다.
SELECT NULL = NULL; SELECT NULL IS NULL;
두 번째 문제는 NULL=NULL의 결과가 항상 False이므로 원래 동일한 데이터의 두 행이 동일하지 않게 된다는 것입니다.
그러나 이것이 첫 번째 문제, 즉 중복 제거 후 데이터 조각이 사라지는 이유는 해결되지 않습니다. 그러나 누락된 데이터는 아마도 NULL 값과 관련이 있을 것으로 추측할 수 있습니다.
카운트와 구별의 두 가지 연산을 분리합니다.
SELECT COUNT(*) as cnt FROM (SELECT DISTINCT id, a, b FROM test_distinct) as tmp;
응? 결과는 정확합니다. 이는 count(distinct expr)
에 의해 생성된 쿼리 계획이 우리가 상상한 것과 다를 수 있음을 의미합니다. 중복 항목을 먼저 제거한 다음 쿼리 계획을 분석하기 위해 설명을 사용하지 않습니다. count(distinct expr)
生成的查询计划可能和我们想象的不一样,并不是先去重再统计,使用explain分析一下两条语句的查询计划,如下所示:
从表中可以看到,mysql执行引擎直接将count(distinct expr)
作为一个查询,查看官方文档:
至此问题才终于弄清楚了。解决这个问题的办法有两种,第一种就是上述的先去重后统计,第二种可以利用IFNULL()
표에서 볼 수 있듯이 mysql 실행 엔진은 count(distinct expr)
를 쿼리로 직접 사용합니다. 공식 문서를 확인하세요:
IFNULL()
함수를 사용하는 것입니다: SELECT COUNT(DISTINCT id, a, IFNULL(b, '0')) as cnt FROM test_distinct;
SELECT id, a, b, COUNT(*) FROM test_distinct GROUP BY id, a, b; SELECT id, a, b, COUNT(b) FROM test_distinct GROUP BY id, a, b;
COUNT()에는 두 가지 용도가 있습니다. 열의 값 수를 계산하는 데 사용하거나 행 수를 계산하는 데 사용할 수 있습니다. 컬럼 값을 계산할 때 컬럼 값은 Null이 아니어야 합니다(NULL은 계산되지 않음). COUNT() 함수의 괄호 안에 열이나 표현식이 지정되면 해당 표현식에 값이 있는 결과의 개수가 계산됩니다. COUNT()의 또 다른 기능은 결과 집합의 행 수를 계산하는 것입니다. MySQL은 대괄호 안의 표현식 값이 비어 있을 수 없음을 확인하면 실제로 행 수를 계산합니다. 가장 간단한 것은 COUNT()를 사용하는 것입니다. 이 경우 와일드카드는 우리가 추측한 대로 모든 열로 확장되지 않습니다. 실제로 모든 열을 무시하고 모든 행을 직접 계산합니다. - "고성능 MySQL";
InnoDB에서는 SELECT COUNT(*)와 SELECT COUNT(1)이 동일하게 처리되며 성능상의 차이는 없습니다.위 내용은 mysql에서 고유한 여러 열 개수 문제를 해결하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!