Je participe à un projet et ai rencontré une mauvaise conception par d'autres, nous avons une table appelée tâche et chaque tâche a de nombreux utilisateurs, la table des tâches est comme ci-dessous:
aaa,bbb
et l'utilisateur est stocké comme aaa, bbb, ccc dans la colonne task_users , ce qui signifie que de nombreux identifiants d'utilisateurs sont placés dans une colonne, je sais que c'est une très mauvaise conception mais comme c'est un vieux projet , Je ne peux pas modifier la conception de la table.
Maintenant, j'ai un problème, si l'utilisateur est supprimé, comment puis-je le supprimer de la colonne task_users ?
l'ID utilisateur est généré par UUID et il est de longueur fixe avec 32 caractères, donc chaque ID utilisateur est unique, tel que 40cf5f01eb2f4d2c954412f27b3bf6eb , mais le problème est que l'ID utilisateur peut apparaître dans n'importe quelle position de la colonne task_users , donc je ne sais pas comment la supprimer
aaa,40cf5f01eb2f4d2c954412f27b3bf6eb,bbb -- in center 40cf5f01eb2f4d2c954412f27b3bf6eb,aaa,bbb -- in head aaa,bbb,40cf5f01eb2f4d2c954412f27b3bf6eb -- in end
lors de la suppression de l'ID utilisateur, le résultat mis à jour est comme
+----------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------+------+-----+---------+-------+ | id | varchar(40) | NO | PRI | NULL | | | name | varchar(100) | YES | | NULL | | | task_users | varchar(1000) | YES | | NULL | |
Je veux savoir si nous pouvons utiliser une mise à jour sql pour supprimer l'ID utilisateur spécifié tout en conservant la même forme de données t?
Remarque: je le fais dans une procédure stockée MySQL, une variable supplémentaire peut être utile, mais je veux toujours utiliser juste un sql pour le faire, la version MySQL est 5.0 strong>
Merci d'avance!
3 Réponses :
Essayez cette expression CASE où uid est l'argument de votre procédure stockée ...
-- only that user
UPDATE `task`
SET `task_users` = ''
WHERE `task_users` = uid;
-- at start
UPDATE `task`
SET `task_users` = REPLACE(`task_users`, CONCAT(uid, ','), '')
WHERE `task_users` LIKE CONCAT(uid, ',%');
-- at end
UPDATE `task`
SET `task_users` = REPLACE(`task_users`, CONCAT(',', uid), '')
WHERE `task_users` LIKE CONCAT('%,', uid);
-- in the middle
UPDATE `task`
SET `task_users` = REPLACE(`task_users`, CONCAT(',', uid, ','), ',')
WHERE `task_users` LIKE CONCAT('%,', uid, ',%');
Démo ~ http://sqlfiddle.com/#!9/1d2baa/1
Réponse d'origine "quatre requêtes" ci-dessous
UPDATE `task` SET `task_users` = CASE
-- at the start
WHEN `task_users` LIKE CONCAT(uid, ',%')
THEN REPLACE(`task_users`, CONCAT(uid, ','), '')
-- at the end
WHEN `task_users` LIKE CONCAT('%,', uid)
THEN REPLACE(`task_users`, CONCAT(',', uid), '')
-- in the middle
WHEN `task_users` LIKE CONCAT('%,', uid, ',%')
THEN REPLACE(`task_users`, CONCAT(',', uid, ','), ',')
-- only that user
ELSE ''
END
WHERE `task_users` LIKE CONCAT('%', uid, '%');
Merci pour votre réponse, votre solution a besoin de quatre sql séparés, dans ma question, je me demande si nous pouvons utiliser un seul sql pour le faire
@lucumt, il est possible d'utiliser plusieurs requêtes SQL dans un personnalisé procédure .
@lucumt J'ai mis à jour ma réponse avec une solution potentielle à requête unique " (non encore testée)
Ok, testé maintenant. Fonctionne bien sur MySQL 5.6
Vous pouvez utiliser l'expression suivante pour remplacer l'identificateur d'utilisateur spécifié par une chaîne vide:
UPDATE `task`
SET task_users = REGEXP_REPLACE(task_users, CONCAT('(,', @userID, '|', @userID, ',?)'), '');
WHERE task_users RLIKE CONCAT('(^|,)', @userID,'(,|$)')
Ou ajouter la clause WHERE pour filtrer les enregistrements à mettre à jour:
XXX
Remarque , le La fonction REGEXP_REPLACE () a été ajoutée dans MySQL 8.0.
Désolé, la version de ma base de données est 5.0
Votre version de LIKE a un problème potentiel, si un @userName se trouve être également une sous-chaîne d'un autre nom d'utilisateur. Utilisez plutôt REGEXP avec des limites de mots. Mais suggérer un remplacement de regex est généralement une bonne idée ici.
@TimBiegeleisen OP dit que ce sont toutes des chaînes uniques de 32 caractères, donc le risque d'un faux positif est nul
@Phil Merci d'avoir clarifié, bien qu'en général pour quelqu'un d'autre qui lira cette question à l'avenir, cela pourrait être une préoccupation pour lui.
@TimBiegeleisen bien sûr, cela a beaucoup de sens et je suis sûr que beaucoup d'autres implémentations CSV terribles ne profitent pas du luxe de valeurs de longueur fixes uniques. Ma réponse souffre du même problème que celui-ci
Merci, @TimBiegeleisen. Vous avez raison! J'ai mis à jour la réponse.
Je pense que nous pouvons le faire avec une seule requête:
UPDATE yourTable
SET task_users = SUBSTRING(
REPLACE(CONCAT(',', task_users, ','), CONCAT(',', uid, ','), ','),
2,
LENGTH(task_users) - LENGTH(uid) - 1)
WHERE task_users REGEXP CONCAT('[[:<:]]', uid, '[[:>:]]');
Voici un lien vers un démo (à utiliser uniquement à des fins de test):
Cette réponse utilise une astuce, par laquelle nous ajoutons des virgules au début et à la fin de la chaîne task_users . Ensuite, nous comparons un ID utilisateur donné en ajoutant également des virgules à son début et à sa fin. Si une correspondance est trouvée, nous la remplaçons par une seule virgule. Mais, cela laisse le remplacement toujours avec ses virgules de début et de fin, nous supprimons donc ceux avec une opération de sous-chaîne.
SQL Olympics de côté, j'espère que vous pouvez voir par la complexité de ces réponses que travailler avec des données CSV une base de données SQL peut être un véritable casse-tête. Peut-être que vous pouvez même utiliser cette page pour prouver à vos collègues que la conception du tableau doit changer.
Oh, c'est intelligent. Similaire à ce que vous voyez en recherchant des attributs de classe HTML avec XPath, c'est-à-dire en enveloppant la chaîne entière dans le délimiteur.
@Phil Je n'ai rien pu faire fonctionner avec votre démo SQLFiddle (comportement typique), mais je l'ai fait fonctionner avec Rextester. Vérifiez le lien de démonstration si vous êtes intéressé.
SQLFiddle est floconneux la plupart du temps, mais voici votre réponse sous forme de démo ~ sqlfiddle.com/#! 9 / bd4bb / 1 (avec UPDATE au lieu de SELECT)
@TimBiegeleisen C'est la réponse que je veux, merci beaucoup!
Vous pouvez utiliser
REGEXP_REPLACE ()fonction pour remplacer l'identificateur d'utilisateur spécifié par une chaîne vide.@Alexander Peux-tu ajouter un exemple? J'ai envisagé d'utiliser regex, mais le point clé est de savoir comment conserver le même format, ce n'est pas simplement de le remplacer par un vide, car cela ajoutera un doublon
,Je suis désolé, la fonction est possible depuis MySQL 8.0
@Alexander ça n'a pas d'importance, je suis très douloureux avec ce mauvais design de table