Maison >base de données >tutoriel mysql >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 :
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!