Eviter les doublons : casse, diacritiques et ponctuation

[cfried]

Bonjour à tous,

Ne sachant pas si la section est appropriée, si un modérateur voit ce message et qu'il pense aller dans une autre rubrique, ne pas hésiter à le déplacer

Pour en arriver au problème :

J'ai créé une base de donnée relationnelle d'une certaine envergure et pour insérer les données on va se servir de fichiers Excel lus en PHP (PHPExcel).

Jusque là tout va bien. Je voudrais éviter d'insérer des doublons dans mes tables toutes propres (principalement dans les champs de texte !). Première solution, définir un index unique sur les champs en question. OK ! Mais si je ne me trompes, cette solution ne prends pas en compte les problèmes de diacritiques, de casse et surtout de ponctuation (un espace en + ou en -, tout ce qui est ; , : = + - _ et j'en passe).

Je voudrais donc que les chaines de signification identiques ne soient pas dupliquées en plusieurs entrées dans la base.

Exemple :

"Conformité grain, teinte masse, brillance"
NE DOIT PAS ETRE DUPLIQUE EN
"Conformité grain , teinte masse,brillance"

Pareil pour les accents etc.

Merci d'avance pour les pistes que vous pourrez me donner, ça me sera très utile !

[ericd69]

salut,

mysql n'est pas capable de ça...

le plus simple est de faire un prétraitement php (ou autre langage) qui va faire les traitements pour éviter ça... y a toutes les regexp de remplacement qui faut dedans pour générer une expression normalisée aux vues de ta recherche...

une fois que c'est fait, là tu testes la présence de la chaine dans la table, et en fonction tu insères ou pas... ça peut être fait dans une procédure stockée pour ne transmettre la chaine qu'une fois et éviter les aller retour...

comme ça tu optimises au mieux l'utilisation de php (ou ce qui te sert de langage serveur) et mysql...

[cfried]

Envoyé par ericd69

salut,

mysql n'est pas capable de ça...

le plus simple est de faire un prétraitement php (ou autre langage) qui va faire les traitements pour éviter ça... y a toutes les regexp de remplacement qui faut dedans pour générer une expression normalisée aux vues de ta recherche...

une fois que c'est fait, là tu testes la présence de la chaine dans la table, et en fonction tu insères ou pas... ça peut être fait dans une procédure stockée pour ne transmettre la chaine qu'une fois et éviter les aller retour...

comme ça tu optimises au mieux l'utilisation de php (ou ce qui te sert de langage serveur) et mysql...

Bonjour,

En cherchant bien hier je me suis rendu à l'évidence effectivement, pas possible de faire ceci directement avec le SGDB.

Pour répondre à mon problème j'ai finalement créé une fonction PHP qui me donne un pourcentage d'équivalence entre deux chaines (en utilisant la fonction de levenshtein implémentée dans PHP).

Grâce à ça et en fonction de la valeur de sortie je peux en déduire un comportement à adopter :

- Si le degré de similarité est élevé, on considère que ce sont les mêmes entrées et on ne duplique pas ;
- Si le degré de similarité est compris entre deux valeurs intermédiaires on demande l'intervention de l'utilisateur (admin) qui répondra par OUI ou par NON à la question de savoir si les chaînes sont identiques ;
- Si le degré de similarité est faible, on insère la nouvelle entrée !

J'ai bien noté votre suggestion concernant les procédures stockées mais je ne vois pas en quoi ceci pourrait être utile du coup ? (n'en ayant jamais programmé et utilisant déjà des requêtes préparées avec PDO).

Par contre, peut être pouvez vous me donner votre avis sur l'optimisation de mon code. Je m'explique : avant de comparer les chaines avec un algo relativement lourd comme Levenshtein et le traitement qui va autour dans ma fonction, je ne vérifie que les chaines qui commencent au moins par le même mot grâce à une requête préparée "... WHERE CATEGORIE_LIB LIKE '".$firstWord."%'".

Est-ce une bonne solution ?

En vous remerciant par avance de l'attention que vous portez à mon développement

[SQLpro]

L'algorithme de levenshtein est exponentiel et va donc avoir des conséquences catastrophiques sur les performances.
Effectivement MySQL est incapable de traiter correctement les collations... Ce n'est pas la seule limite et si vous voulez faire cela mieux vaut vous orienter vers un SGBDR qui sache le faire nativement !
PostGreSQL hélas n'en est pas non plus capable, bien que cela progresse...
A ma connaissance seul SQL Server gére correctement les collations en intégrant les casses, et tous les caractères diacritiques, y compris les ligatures (e dans l'o par exemple), avec plus de 2000 collations différentes.

A +

[cfried]

Envoyé par SQLpro

L'algorithme de levenshtein est exponentiel et va donc avoir des conséquences catastrophiques sur les performances.
Effectivement MySQL est incapable de traiter correctement les collations... Ce n'est pas la seule limite et si vous voulez faire cela mieux vaut vous orienter vers un SGBDR qui sache le faire nativement !
PostGreSQL hélas n'en est pas non plus capable, bien que cela progresse...
A ma connaissance seul SQL Server gére correctement les collations en intégrant les casses, et tous les caractères diacritiques, y compris les ligatures (e dans l'o par exemple), avec plus de 2000 collations différentes.

A +

En y réfléchissant bien, les performances ne sont pas forcément importantes car il s'agit juste de peupler la base de données avant qu'elle soit mise en production (migration d'une application à une autre).

Sinon j'avais pensé à un algo basique en PHP :
- supprimer toutes les diacritiques et la ponctuation
- faire la somme des codes ascii des caractères de la chaîne
- comparer la somme et dans un certain pourcentage d'erreur minime, la considérer comme identique

J'ai bien pensé à certaines limitations de ce genre de code comme :
CHIEN
et
NICHE
qui seraient dans ce cas considérés comme identiques mais sachant que les chaines comparées sont plus longues que ça, la probabilité de ce genre de cas diminue fortement ...

En ce qui concerne le choix de SGBDR, sachant que l'application existante "tournait" sous Access avec des tables non indexées de + de 20000 lignes on peut se permettre de passer à MySQL

Merci en tous cas de me lire et de m'apporter votre expertise.

[ericd69]

oui c'est sur...

je vois que tu tiens le bon bout

[cfried]

Envoyé par ericd69

oui c'est sur...

je vois que tu tiens le bon bout

Merci et je vous tiendrai au courant de l'avancée des travaux