mysql에서 고유한 여러 열 개수 문제를 해결하는 방법

재현된 테스트 데이터베이스는 다음과 같습니다.

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)를 쿼리로 직접 사용합니다. 공식 문서를 확인하세요:

Solution

The 문제가 마침내 밝혀졌습니다. 이 문제를 해결하는 방법에는 두 가지가 있습니다. 첫 번째는 중복 항목을 먼저 제거한 다음 개수를 계산하는 것입니다. 두 번째는 IFNULL() 함수를 사용하는 것입니다:

SELECT COUNT(DISTINCT id, a, IFNULL(b, '0')) as cnt FROM test_distinct;

• 또한, count() 사용법:

  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;

• 🎜🎜Knowledge point🎜🎜🎜🎜null 값을 비교하기 위해 산술 비교 연산자(예: =, )를 사용할 수 없습니다. 🎜🎜🎜🎜count(distinct expr)는 숫자를 반환합니다. expr 열의 고유하고 비어 있지 않은 행 🎜

• COUNT()에는 두 가지 용도가 있습니다. 열의 값 수를 계산하는 데 사용하거나 행 수를 계산하는 데 사용할 수 있습니다. 컬럼 값을 계산할 때 컬럼 값은 Null이 아니어야 합니다(NULL은 계산되지 않음). COUNT() 함수의 괄호 안에 열이나 표현식이 지정되면 해당 표현식에 값이 있는 결과의 개수가 계산됩니다. COUNT()의 또 다른 기능은 결과 집합의 행 수를 계산하는 것입니다. MySQL은 대괄호 안의 표현식 값이 비어 있을 수 없음을 확인하면 실제로 행 수를 계산합니다. 가장 간단한 것은 COUNT()를 사용하는 것입니다. 이 경우 와일드카드는 우리가 추측한 대로 모든 열로 확장되지 않습니다. 실제로 모든 열을 무시하고 모든 행을 직접 계산합니다. - "고성능 MySQL";

  InnoDB에서는 SELECT COUNT(*)와 SELECT COUNT(1)이 동일하게 처리되며 성능상의 차이는 없습니다.

위 내용은 mysql에서 고유한 여러 열 개수 문제를 해결하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!