Comment résoudre le problème du nombre de colonnes distinctes dans MySQL

La base de données de test reproduite est la suivante :

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;

Les données de test dans le tableau sont les suivantes : Nous devons maintenant compter le nombre de colonnes après déduplication de ces trois colonnes.

Analyse du problème

Mon ami m'a donné quatre instructions de requête pour localiser le problème

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';

#🎜 🎜# Les résultats de la requête sont les suivants :

# 🎜🎜 #

Attention ! ! ! À partir des données de test, nous pouvons rapidement deviner où se situe le problème, mais il s'avère qu'il y a plus de 30 000 éléments de données dans le tableau et qu'il est impossible de visualiser les données à l'œil nu.

Il y a deux points contre-intuitifs dans les résultats de la requête ci-dessus :

La deuxième donnée est manquante après les statistiques de déduplication, mais la seconde Il manque une donnée. Les résultats des trois données montrent qu'elles n'ont pas les mêmes données.
Lorsque vous utilisez la même table pour effectuer une jointure externe gauche, la table pilote contient des données, mais la table pilotée est vide.
Regardons d'abord la deuxième question. Le document officiel a l'explication suivante :

Lors de l'utilisation. Sous-clause ON, elle contient les mêmes expressions conditionnelles utilisées dans la clause WHERE. Une situation courante consiste à utiliser la clause ON pour spécifier les conditions de jointure de la table et à utiliser la clause WHERE pour limiter les lignes incluses dans le jeu de résultats.
Si la table de droite n'a aucune ligne correspondante pour la condition dans la partie ON ou USING de LEFT JOIN, la table de droite utilise toutes les colonnes définies sur NULL.
Vous ne pouvez pas utiliser d'opérateurs de comparaison arithmétique (tels que =, < ou <>) pour comparer NULL.

• SELECT NULL = NULL;
  SELECT NULL IS NULL;

Donc le deuxième problème réside dans le résultat de NULL=NULL La valeur est toujours False, ce qui entraîne deux lignes de données initialement égales mais non égales.

Mais cela ne résout pas le premier problème : pourquoi une donnée a disparu après la déduplication. Cependant, on peut deviner que les données manquantes sont probablement liées à la valeur NULL.

On sépare les deux opérations de comptage et de distinct :

SELECT COUNT(*) as cnt FROM (SELECT  DISTINCT id, a, b FROM test_distinct) as tmp;

Hein ? Le résultat est correct, ce qui signifie que le plan de requête généré par count(distinct expr) peut être différent de ce que nous avions imaginé. Il ne s'agit pas de supprimer d'abord les doublons puis de compter. Utilisez expliquer pour analyser la requête. plan des deux déclarations, comme indiqué ci-dessous :

count(distinct expr)生成的查询计划可能和我们想象的不一样，并不是先去重再统计，使用explain分析一下两条语句的查询计划，如下所示：

从表中可以看到，mysql执行引擎直接将count(distinct expr)作为一个查询，查看官方文档：

解决办法

至此问题才终于弄清楚了。解决这个问题的办法有两种，第一种就是上述的先去重后统计，第二种可以利用IFNULL()

Comme vous pouvez le voir sur le tableau, le moteur d'exécution mysql utilise directement count(distinct expr) sous forme de requête, consultez le document officiel :

Solution

Le problème a finalement été clarifié. Il existe deux façons de résoudre ce problème. La première consiste à supprimer d'abord les doublons, puis à compter comme mentionné ci-dessus. La seconde consiste à utiliser la fonction IFNULL() :

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

. Un autre ajout, utilisez 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#🎜🎜##🎜🎜 ##🎜🎜##🎜🎜#Vous ne pouvez pas utiliser d'opérateurs de comparaison arithmétique (tels que =, ) pour comparer des valeurs nulles #🎜🎜##🎜🎜##🎜🎜##🎜🎜#count ; ( distinct expr) renvoie le nombre de lignes distinctes et non vides dans la colonne expr #🎜🎜# ;

• COUNT() a deux utilisations distinctes : elle peut être utilisée pour compter le nombre de valeurs dans une colonne, ou elle peut être utilisée pour compter le nombre de lignes. Lors du comptage des valeurs de colonne, la valeur de la colonne doit être non vide (NULL n'est pas compté). Lorsqu'une colonne ou une expression est spécifiée entre parenthèses de la fonction COUNT(), la fonction compte le nombre de résultats qui ont une valeur dans l'expression. Une autre fonction de COUNT() consiste à compter le nombre de lignes dans le jeu de résultats. Lorsque MySQL confirme que la valeur de l'expression entre parenthèses ne peut pas être vide, il compte en fait le nombre de lignes. Le plus simple est lorsque nous utilisons COUNT(). Dans ce cas, le caractère générique ne s'étend pas à toutes les colonnes comme nous l'avions deviné. En fait, il ignorera toutes les colonnes et comptera directement toutes les lignes - "MySQL haute performance" ;

  Dans InnoDB, SELECT COUNT(*) et SELECT COUNT(1) sont traités de la même manière et il n'y a aucune différence de performances.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!