Comment supprimer un ID faisant partie d'une liste dans un champ string ?

**clavier12AZQSWX** · 19/05/2023, 11h01

bonjour,

j'ai un champs string qui se comportent comme une liste de IDS, par exemple

id,nom,liste
1 toto 1,2,52
2 julie 1,5,3
3 dupont 15,5,6
4 sarah 5
6 julien 5,4

si je veux retirer l'élément 5 dans les champs 'liste', c-a-d supprimer '5' de la chaine liste , comment faire en une étape ?

je dis bien en une étape, car je sais que je peux le faire en 4 étapes (des sql update avec replace). j'ai 4 étapes car je ne peux pas juste faire un replace du 5 sinon je vais bousiller les 52,15 et je dois gérer le positionnement de la virgule !
Mysql n'a-t-il pas une fonction intégrée pour gérer les champs de type liste ?

**tatayo** · 19/05/2023, 11h14

Bonjour,
tu peux utiliser nu CASE dans la "partie" affectation" de la colonne:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
update MaTable
set MaColonne = case 
when Macolonne = '5' then null
when MaColonne like '5,%' then right(MaColonne, length(MaColonne) - 2
when MaColonne like '%,5' then Left(MaColonne, length(MaColonne) - 2
else MaColonne = replace(MaColonne, ',5,','')

Tu peux ajouter nu filtre pour ne prendre que les lignes concernées, mais dans tous les cas aucun index ne sera éligible ici.

Tatayo.

**clavier12AZQSWX** · 19/05/2023, 11h29

oui , une requête avec 4 cases est moins couteux en serveur que 4 update..
ça reste une écriture sql "encombrante"
n'y-a-t-il pas dans mysql (comme en php), des fonctions spécifiques aux "array" ?
si c'était le cas j'aurai pu utiliser "WHERE 5 IN macolonne" pour déjà mieux cibler

dommage que ya pas pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE matable set macolonne=enlever(5) where  5 IN macolonne "

**tatayo** · 19/05/2023, 11h41

Mysql propose la fonction REGEXP_REPLACE() qui doit faire le job.

Il ne te reste plus qu'à trouver l'expression régulière qui va bien.

Tatayo.

**clavier12AZQSWX** · 19/05/2023, 11h43

je suis pas très à l'aise avec le RegX

, je viens de voir qu'il y a la fonction TRIM, je vais peut-être arriver à réduire les cas...
https://www.w3resource.com/mysql/str...m-function.php

**al1_24** · 19/05/2023, 12h27

Cela démontre une fois de plus qu'avec un modèle de données mal construit on rencontre rapidement des problèmes qui ne peuvent être résolus de manière simple.
On ne gère pas une liste dans une colonne !

On crée une table fille pour y placer la liste.

Au lieu de la table Table1(#Id_T1, Attributs_T1, ListeDeValeurs), il faudrait Table1(#Id_T1, Attributs_T1) et Table2(*Id_T1, Valeur), avec une seule valeur de la liste dans chaque ligne.
Et là, plus de contorsion pour trouver une valeur dans la liste, la supprimer, la remplacer...

**Séb.** · 19/05/2023, 19h23

si je veux retirer l'élément 5 dans les champs 'liste', c-a-d supprimer '5' de la chaine liste , comment faire en une étape ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
UPDATE ta_table
SET ta_liste = TRIM(BOTH ',' FROM REPLACE(',' || ta_liste || ',', ',5,', ','));

1. On ajoute le délimiteur avant/après la valeur à supprimer => Pour être sûr de bien supprimer la valeur souhaitée uniquement
2. On ajoute le délimiteur en début et fin de liste => Pour gérer les recherches en début/fin de liste
3. On remplace la valeur à supprimer encadrée du délimiteur par le délimiteur seul => Pour que la liste reste correctement construite
4. On supprime les délimiteurs encadrants ajoutés pour l'exercice => On remet à neuf

C'est moche mais ça marche.

Passe plutôt par une 2nde table ou du JSON.

**clavier12AZQSWX** · 22/05/2023, 09h48

Envoyé par al1_24

Cela démontre une fois de plus qu'avec un modèle de données mal construit on rencontre rapidement des problèmes qui ne peuvent être résolus de manière simple.
On ne gère pas une liste dans une colonne !

On crée une table fille pour y placer la liste.

Au lieu de la table Table1(#Id_T1, Attributs_T1, ListeDeValeurs), il faudrait Table1(#Id_T1, Attributs_T1) et Table2(*Id_T1, Valeur), avec une seule valeur de la liste dans chaque ligne.
Et là, plus de contorsion pour trouver une valeur dans la liste, la supprimer, la remplacer...

je vous rejoins sur ce point à 100%

Quand j'ai appris le SQL ya des décénies, on nous apprenez l'existence du type SET de mysql ... Or dans aucun cours, on nous a appris à choisir/refuser un type SET et préférer/éviter une table fille....

**escartefigue** · 31/05/2023, 08h20

Dans l'apprentissage de la modélisation des bases de données, on aborde notamment les formes normales.
Or, la première d'entre-elles stipule justement que chaque intersection ligne/colonne ne doit contenir qu'une et une seule valeur !
(on trouve souvent le terme "valeur atomique" à ce sujet, mais c'est un abus de langage)

La remarque de AL1__24 est donc incontournable.

**Artemus24** · 31/05/2023, 14h38

Salut à tous.

Envoyé par clavier12AZQSWX

j'ai un champs string qui se comportent comme une liste de IDS, par exemple

On ne dit pas champs, mais colonne. Apprenez à utiliser le vocabulaire adéquate.

Je n'aime pas trop les sigles surtout quand leur signification peuvent être multiples.
Qu'est-ce que vous entendez par IDS ? (peut être Integrated Data Store comme le nom du SGBD sous BULL).

Envoyé par clavier12AZQSWX

si je veux retirer l'élément 5 dans les champs 'liste', c-a-d supprimer '5' de la chaine liste , comment faire en une étape ?

L'astuce de Seb. fonctionne parfaitement, je l'ai testé, sauf qu'elle n'a pas la bonne syntaxe sous MySql :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE `test` SET liste =trim(both ',' from replace(concat(',',`liste`,','),',5,',','));

Envoyé par clavier12AZQSWX

Mysql n'a-t-il pas une fonction intégrée pour gérer les champs de type liste ?

La colonne "liste" n'a pas lieu d'être car vous faites un mauvais usage des SET.
Un SET est une colonne qui va admettre une liste de valeurs, c'est-à-dire à choix multiple.
Un ENUM fonctionne presque pareil que le SET sauf qu'il admet qu'une seule valeur possible.
Voir la documentation MySql :
--> Type Set.
--> [https://dev.mysql.com/doc/refman/8.0/en/enum.html]TypeEnum[/url].

Il n'existe aucune fonction pour faire des substitutions car le but de ce stockage est de donner la liste des valeurs admissible à l'insertion.
D'autre part dans le type set comme dans le type enum, ce qui est stocké est la position de la valeur dans la liste.
Par exemple, si mon set est composé de ('a', 'b', 'c') et que je stocke 'c', la valeur stocké est 3.

Si vous avez besoin de connaitre si l'une de ces valeurs a été renseignées, vous devez utiliser la fonction "Find_In_Set()".

La bonne façon de gérer votre colonne "liste" puisqu'il ne s'agit pas d'une colonne à choix multiple comme le SET, est de suivre les recommandation de 'AL1_24.

Si les valeurs changent fréquemment et que vous avez besoin de les retrouver, vous ne devez pas les stocker mais les recalculer à l'aide d'une View.

Cela vous aurait évité d'avoir à trouver une astuce pour résoudre votre problème.

Cordialement.
Artemus24.
@+

**Séb.** · 31/05/2023, 19h51

L'astuce de Seb. fonctionne parfaitement, je l'ai testé, sauf qu'elle n'a pas la bonne syntaxe sous MySql :

Si si, à condition que MySQL soit paramétré avec PIPES_AS_CONCAT
Perso je suis en mode SET @@sql_mode = 'ANSI,TRADITIONAL'; ce qui est inclus l'option PIPES_AS_CONCAT

**Artemus24** · 31/05/2023, 20h12

Salut Seb.

Je suis dans le mode strict "innodb-strict-mode = on" et je n'ai pas modifié la variable "sql_mode" :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
>

Cordialement.
Artemus24.
@+

**Séb.** · 06/06/2023, 00h33

Avec @@sql_mode = 'ANSI,TRADITIONAL' :

REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION

**clavier12AZQSWX** · 06/06/2023, 09h39

plus haut, j'ai posé la question de l'existence ou non d'une fonction intégrée pour gérer les champs/colonnes contenant des valeurs de type liste de données (a,b,c,d) car dans ma précédent expérience avec Postgres, ça existait avec les fonctions sur les données tableau/array. (cf ARRAY_REMOVE)
Depuis le temps, j'avais espéré que mysql avait un peu évolué...

**Séb.** · 06/06/2023, 12h10

Depuis le temps, j'avais espéré que mysql avait un peu évolué...

Plus haut :

Mysql n'a-t-il pas une fonction intégrée pour gérer les champs de type liste ?

Or tu présentes une colonne de type TEXT avec des valeurs séparées par une , ; pour une colonne contenant des données structurées il y a JSON et ses tableaux/objets
=> https://dev.mysql.com/doc/refman/8.0/en/json.html

**Artemus24** · 06/06/2023, 13h24

Salut à tous.

@ clavier12AZQSWX : ta colonne n'est pas une liste de champs comme tu le dis.
Tu détournes de sa fonction première le type SET ou ENUM pour en faire un usage différent.
MySql fournit tout ce dont tu as besoin pour gérer ce type (SET ou ENUM) de colonne.

Comme il a été dit plus haut, ta modélisation n'est pas correcte, d'où tes problèmes.
La bonne façon de faire a été donnée par Al1_24.

Cordialement.
Artemus24.
@+

Comment supprimer un ID faisant partie d'une liste dans un champ string ?

Requêtes MySQL

Discussions similaires

Partager

Partager