Puis-je joindre des tables sur des valeurs séparées par des points-virgules dans MySQL sans outils externes ?

Puis-je résoudre ce problème avec Pure MySQL ? (Rejoindre ; Valeurs séparées dans une colonne)

Problème :

Vous avez des données stockées dans deux tables, avec une colonne dans la première table répertoriant plusieurs valeurs séparées par des points-virgules. Vous devez effectuer une jointure interne basée sur ces valeurs séparées, mais vous n'avez pas de table de liaison et ne pouvez utiliser aucun langage de programmation externe.

Discussion :

Le défi réside dans la conversion de la liste séparée par des points-virgules en lignes distinctes, permettant ainsi la jointure avec la deuxième table. Ceci peut être réalisé en utilisant une technique appelée « dérivation » ou « normalisation » des données.

Solution :

1. Créez un tableau de séries entières :

Pour normaliser les données, vous avez d'abord besoin d'un tableau contenant une plage de nombres. Dans ce cas, vous pouvez créer une table de séries entières avec des ID allant de 1 au nombre maximum d'éléments que vous attendez dans la liste séparée par des points-virgules.

2. Utilisez JOIN et les sous-requêtes :

Une fois que vous avez la table des séries entières, effectuez la requête suivante :

<code class="sql">SELECT user_resource.user, 
       resource.data

FROM user_resource 
     JOIN integerseries AS isequence 
       ON isequence.id <= COUNT_IN_SET(user_resource.resources, ';') /* normalize */

     JOIN resource 
       ON resource.id = VALUE_IN_SET(user_resource.resources, ';', isequence.id)      
ORDER BY
       user_resource.user,  resource.data</code>

Explication :

• Cette requête joint la table user_resource à la table integerseries pour générer un ensemble de lignes pour chaque utilisateur et chaque élément dans la colonne de ressources correspondante.
• La fonction COUNT_IN_SET compte le nombre d'éléments dans la colonne de ressources, et la fonction VALUE_IN_SET extrait un élément spécifique en fonction de l'isequence.id.
• La jointure finale avec la table de ressources fait correspondre l'élément extrait avec l'ID pour récupérer les données correspondantes.

Fonctions supplémentaires (facultatif) :

La requête utilise deux fonctions personnalisées, COUNT_IN_SET et VALUE_IN_SET, qui peuvent être définies comme suit :

<code class="sql">-- Function to count the number of delimited items in a string
DELIMITER $$
DROP FUNCTION IF EXISTS `COUNT_IN_SET`$$
CREATE FUNCTION `COUNT_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1)
                               ) RETURNS INTEGER
BEGIN
      RETURN CHAR_LENGTH(haystack) - CHAR_LENGTH( REPLACE(haystack, delim, '')) + 1;
END$$
DELIMITER ;

-- Function to get the value at a specific index in a delimited string
DELIMITER $$
DROP FUNCTION IF EXISTS `VALUE_IN_SET`$$
CREATE FUNCTION `VALUE_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1), 
                               which INTEGER
                               ) RETURNS VARCHAR(255) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
      RETURN  SUBSTRING_INDEX(SUBSTRING_INDEX(haystack, delim, which),
                     delim,
                     -1);
END$$
DELIMITER ;</code>

Ces fonctions fournissent une manière générique pour manipuler des chaînes délimitées dans les requêtes SQL.

Exemples de tables et de données :

<code class="sql">-- Integerseries table
CREATE TABLE `integerseries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);
-- Resource table
CREATE TABLE `resource` (
  `id` int(11) NOT NULL,
  `data` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Data for resource table
INSERT INTO `resource` (`id`, `data`) VALUES
(1, 'abcde'),
(2, 'qwerty'),
(3, 'azerty');

-- User_resource table
CREATE TABLE `user_resource` (
  `user` varchar(50) NOT NULL,
  `resources` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`user`)
);

-- Data for user_resource table
INSERT INTO `user_resource` (`user`, `resources`) VALUES
('sampleuser', '1;2;3'),
('stacky', '2'),
('testuser', '1;3');</code>

Sortie :

Exécution du une requête sur les exemples de données produira le résultat suivant :

+----------+-------+
| user     | data   |
+----------+-------+
| sampleuser | abcde  |
| sampleuser | qwerty |
| sampleuser | azerty |
| stacky    | qwerty |
| testuser  | abcde  |
| testuser  | azerty |
+----------+-------+

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!